Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts

Thursday, March 29, 2012

Does SQL Server put a shared lock on all tables within a transaction?

Would table1, table2 and table3 in code below, be locked with a shared lock from start of transaction to the end of transaction Or they would only be locked for the duration of their update, or insert statements and not for the entire transaction? Default isolation level is in effect in SQL Server.

begin tran
update table1 set column1 = 100
if @.ERROR = 0
begin
declare @.stat int
set @.stat = (select stat from table2 where employeeid = 10)
insert into table3 (col1, col2) values (@.stat , 325)
if @.@.ERROR = 0
commit tran
else
rollback tran
end
else
roll back tran

The answer to your first question is no. The answer to your second question is no.|||

Here's the long answer:

To keep this long answer from becoming a book, I'm going to classify IS/S locks as "shared", IU/I locks as "Update" and IX/X locks as "Exclusive". The I-versions are basically the same, but has a few differences, and usually used prior to obtaining the non-I version to eliminate lock starvation.

I'm going to assume table1 only has 1 row in it for this.

Update table1 ... will initially request an update lock as it scans the table for the rows to update, once it's located the correct rows, it will acquire an exclusive lock on those pages and rows then release the update locks.

set @.stat=(SELECT .. will request shared locks on the table, retrieve it's results, then release it's shared locks.

insert into table3 will acquire an exlusive lock on either an existing page, or a new page if either none are available, or the available ones are already exclusively locked. (There is an additional exclusive lock placed on the particular row as well).

The exclusive locks will be held until the transaction is either commited or rolled back. This stored procedure will not block any other session from either reading table2 at any time, or inserting a new record into table3. It will however block any other session from updating it's only row until the transaction is complete.

Wednesday, March 21, 2012

Does placing the transaction log on dedicated RAID volume make sense with Simple Recovery

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
DavidLarry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the log after each
transaction. It truncates after each time it performs a checkpoint (read about checkpoint in Books
Online).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com...
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would NOT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the next
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>sql

Does placing the transaction log on dedicated RAID volume make sense with Simple Recovery

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
David
Larry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the log after each
transaction. It truncates after each time it performs a checkpoint (read about checkpoint in Books
Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com...
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would NOT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the next
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>

Does placing the transaction log on dedicated RAID volume make sense with Simple Recov

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
DavidLarry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the l
og after each
transaction. It truncates after each time it performs a checkpoint (read abo
ut checkpoint in Books
Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com.
.
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would N
OT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the ne
xt
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>

Sunday, March 11, 2012

Does Linked Server for MS Access support transaction?

Dear all,

We have connected a Access to a MS SQL Server 2005 as a Linked Server with the followoing settings:

1) Provider: Microsoft Jet 4.0 OLE DB Provider

2) Product Name: Access

3) Data source: X:\XXXXX.mdb

4) Provider string: ;pwd=YYYYYY;

5) Collation Compatible: False

6) Data Access: True

7) Rpc: False

8) Rpc Out: False

9) Use Remote Collation: True

10) Collation Name:

11) Connection Timeout: 0

12) Query Timeout: 0

We found that when cannot have any insert/update/delete statement for this linked server if transaction is began. Otherwise, we will have the following exception.

============

Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ZZZZZZ" does not support the required transaction interface.

===========

We wonder whether the problem is because Access does not support distributed transaction. If so, do you know any workaround will work for us?


Thanks and regards,
William

Hi William,

I have come across your post related to Transaction Interface. Now i had a similar problem while connectiong the the Paradox database using linked server. Can you please suggest me on this Please. Thanks in advance.

Rakesh

|||

You can use pass-through openquery().

e.g.

update openquery(your_access_linkedserver,'select * from tb')

set col1 = 123

...

|||

Access does not provide any transactional support. No Rollbacks or Commits. Period.

Other than eliminating Access, or resort to not making transaction calls in ANY transaction involving Access, I can think of no workaround.

Does Linked Server for MS Access support transaction?

Dear all,

We have connected a Access to a MS SQL Server 2005 as a Linked Server with the followoing settings:

1) Provider: Microsoft Jet 4.0 OLE DB Provider

2) Product Name: Access

3) Data source: X:\XXXXX.mdb

4) Provider string: ;pwd=YYYYYY;

5) Collation Compatible: False

6) Data Access: True

7) Rpc: False

8) Rpc Out: False

9) Use Remote Collation: True

10) Collation Name:

11) Connection Timeout: 0

12) Query Timeout: 0

We found that when cannot have any insert/update/delete statement for this linked server if transaction is began. Otherwise, we will have the following exception.

============

Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ZZZZZZ" does not support the required transaction interface.

===========

We wonder whether the problem is because Access does not support distributed transaction. If so, do you know any workaround will work for us?


Thanks and regards,
William

Hi William,

I have come across your post related to Transaction Interface. Now i had a similar problem while connectiong the the Paradox database using linked server. Can you please suggest me on this Please. Thanks in advance.

Rakesh

|||

You can use pass-through openquery().

e.g.

update openquery(your_access_linkedserver,'select * from tb')

set col1 = 123

...

|||

Access does not provide any transactional support. No Rollbacks or Commits. Period.

Other than eliminating Access, or resort to not making transaction calls in ANY transaction involving Access, I can think of no workaround.

Does Linked Server for MS Access support transaction?

Dear all,

We have connected a Access to a MS SQL Server 2005 as a Linked Server with the followoing settings:

1) Provider: Microsoft Jet 4.0 OLE DB Provider

2) Product Name: Access

3) Data source: X:\XXXXX.mdb

4) Provider string: ;pwd=YYYYYY;

5) Collation Compatible: False

6) Data Access: True

7) Rpc: False

8) Rpc Out: False

9) Use Remote Collation: True

10) Collation Name:

11) Connection Timeout: 0

12) Query Timeout: 0

We found that when cannot have any insert/update/delete statement for this linked server if transaction is began. Otherwise, we will have the following exception.

============

Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ZZZZZZ" does not support the required transaction interface.

===========

We wonder whether the problem is because Access does not support distributed transaction. If so, do you know any workaround will work for us?


Thanks and regards,
William

Hi William,

I have come across your post related to Transaction Interface. Now i had a similar problem while connectiong the the Paradox database using linked server. Can you please suggest me on this Please. Thanks in advance.

Rakesh

|||

You can use pass-through openquery().

e.g.

update openquery(your_access_linkedserver,'select * from tb')

set col1 = 123

...

|||

Access does not provide any transactional support. No Rollbacks or Commits. Period.

Other than eliminating Access, or resort to not making transaction calls in ANY transaction involving Access, I can think of no workaround.

Does index defrag get logged?

I've noticed a huge transaction log size after having run an
index defragmentation. Does a defrag get written to the transaction
log really? (Assuming the full recovery model.)I've noticed a huge transaction log size after having run an
index defragmentation. Does a defrag get written to the transaction
log really? (Assuming the full recovery model.)

See this ...Link (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx)

Friday, March 9, 2012

Does Commit Transaction delete Temporary table?

Hi SQL Guru's,
I create a global temp table in one of my procedures:
select * into ##temp_update
from ( select distinct * from .....
after this I issue a commit transaction, and when I try to select from
the table I created (from my .Net code or Query Analyzer) it is not
there!
select * from ##temp_update
Invalid object name '##temp_update'.
Any idea why?From Books Online:
"Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing them. Th
e
association between a task and a table is maintained only for the life of a
single Transact-SQL statement. This means that a global temporary table is
dropped at the completion of the last Transact-SQL statement that was
actively referencing the table when the creating session ended."
If the session that creates the global temporary table ends before a new
session that accesses the global temporary table is started, the table is
dropped.
ML|||Yikes!
Good to know that, but bad for my program :)
thanks much
ML wrote:
> From Books Online:
> "Global temporary tables are automatically dropped when the session that
> created the table ends and all other tasks have stopped referencing them.
The
> association between a task and a table is maintained only for the life of
a
> single Transact-SQL statement. This means that a global temporary table is
> dropped at the completion of the last Transact-SQL statement that was
> actively referencing the table when the creating session ended."
> If the session that creates the global temporary table ends before a new
> session that accesses the global temporary table is started, the table is
> dropped.
>
> ML

Wednesday, March 7, 2012

Does Commit Transaction delete Temporary table?

Hi SQL Guru's,
I create a global temp table in one of my procedures:
select * into ##temp_update
from ( select distinct * from .....
after this I issue a commit transaction, and when I try to select from
the table I created (from my .Net code or Query Analyzer) it is not
there!
select * from ##temp_update
Invalid object name '##temp_update'.
Any idea why?From Books Online:
"Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing them. Th
e
association between a task and a table is maintained only for the life of a
single Transact-SQL statement. This means that a global temporary table is
dropped at the completion of the last Transact-SQL statement that was
actively referencing the table when the creating session ended."
If the session that creates the global temporary table ends before a new
session that accesses the global temporary table is started, the table is
dropped.
ML|||Yikes!
Good to know that, but bad for my program :)
thanks much
ML wrote:
> From Books Online:
> "Global temporary tables are automatically dropped when the session that
> created the table ends and all other tasks have stopped referencing them.
The
> association between a task and a table is maintained only for the life of
a
> single Transact-SQL statement. This means that a global temporary table is
> dropped at the completion of the last Transact-SQL statement that was
> actively referencing the table when the creating session ended."
> If the session that creates the global temporary table ends before a new
> session that accesses the global temporary table is started, the table is
> dropped.
>
> ML

Sunday, February 26, 2012

Does a transaction automatically rollback on error?

When I write code for a multiple statements transaction do I need to check'if @.@.ERROR > 0 ' after each SELECT, INSERT, DELETE or UPDATE statement so that the'rollback tran' statement can be given, or SQL server will automatically rollback the transaction and we don't need to check for @.ERROR > 0 ?

If you start a transaction (with BEGIN TRANSACTION) you must end it with a COMMIT or ROLLBACK. So, yes, you should check the @.@.ERROR value after each pertinent statement and explicitly ROLLBACK the transaction if there is an error. I am not sure if there were any changes in this area for SQL Server 2005.|||In SQL 2005, you could do:

BEGIN TRY
.....
COMMIT
END TRY
BEGIN CATCH
.....
ROLLBACK
END CATCH

The good thing is you could put multiple SQL statementsin the TRY block. But there are some limitations too. check out books on line.

Friday, February 24, 2012

Does a Full Backup include transaction logs

In MS SQL 2005 when you do a Full Backup does it also backup and
truncate the transaction logs or do I need to back the transaction
logs up separately?
Thanks.
Brian
Full backups do not mark any log segments as inactive, Therefore no segments
will be truncated following a full backup. Only log backup mark segments as
inactive. Full backups do not interrupt the log backup chain.
This is so if you have a bad full backup, you can go to an earlier full
backup and restore logs through the time of the bad backup and up to
current.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184253274.265128.108590@.w3g2000hsg.googlegro ups.com...
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
> Thanks.
> Brian
>
|||Brian D (bdaltilio@.yahoo.com) writes:
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
You need to backup the transaction log separately. The rationale is that
the last recent backup may have gone lost, or be broken. If the log
backups are OK (and you saved them), you can recover from an older
full backup + the translog backups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Erland,
We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?
Brian
|||Brian,
If you want to save a little bit of backup space, run the 6pm log backup
just before the full backup. If you run it after, you will have backed up
the 12pm-6pm log activities twice.
Do NOT truncate the log ever, if you want to be able to restore the full
backup and then apply the logs to the full backup. If you were to backup
the database, then truncate the 6pm logs, the 12am, 6am, 12pm log backups
are all useless, since there is an unbridgeable transaction log gap between
the full backup and the first log.
RLF
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184261915.544240.106090@.d55g2000hsg.googlegr oups.com...
> Erland,
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
> Brian
>
|||Brian D (bdaltilio@.yahoo.com) writes:
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
First ask yourself: if the database goes belly-up, how much can we afford
to lose? In the case, the database file goes bad, there is a possibility
to take a final backup of the log. But if the log file disappears, this
means that you could lose up almost six hours of work. Can you afford
that?
No, that is it not a leading question. There are businesses where even
the loss of five minutes of data is a disaster. And there are businesses
where a full backup once a night without log backup is perfectly sufficient.
I just want you to evaluate where you fit in. Taking log backups as rarely
as you do, appears a bit unusual, so it might be that your business is
content with restoring the backup from last night. In which case, dealing
with the log is just extra overhead for you. For the rest of the post I will
nevertheless assume that this six-hour window is right for you.
I can't see that it matter whether you back up the log before or after
the full backup, but you should back up the log sometime there. In a
way log backups and full backups are independent of each other.
Russell mentioned that you should never truncate the transaction log.
I like to point out another thing. Where do you write the transaction log
dumps? Do you append them to the same device? Do you ever use WITH INIT?
Here is something to be careful with so that you don't lose part of a
log chain.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I agree completely with Erland. In my experience, I have never been
rewarded or punished because I did or did not have a backup. I was always
judged on whether I had a working recovery plan. Start from the recovery
side and build a complete plan, including a backup plan, that meets the
business needs. Finally, test your recoery plan to see that it actually
works and that it can be done in the agreed-upon time.
If you don't test, you have a recovery hope, not a recovery plan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996C38071EF5Yazorman@.127.0.0.1...
> Brian D (bdaltilio@.yahoo.com) writes:
> First ask yourself: if the database goes belly-up, how much can we afford
> to lose? In the case, the database file goes bad, there is a possibility
> to take a final backup of the log. But if the log file disappears, this
> means that you could lose up almost six hours of work. Can you afford
> that?
> No, that is it not a leading question. There are businesses where even
> the loss of five minutes of data is a disaster. And there are businesses
> where a full backup once a night without log backup is perfectly
> sufficient.
> I just want you to evaluate where you fit in. Taking log backups as rarely
> as you do, appears a bit unusual, so it might be that your business is
> content with restoring the backup from last night. In which case, dealing
> with the log is just extra overhead for you. For the rest of the post I
> will
> nevertheless assume that this six-hour window is right for you.
> I can't see that it matter whether you back up the log before or after
> the full backup, but you should back up the log sometime there. In a
> way log backups and full backups are independent of each other.
> Russell mentioned that you should never truncate the transaction log.
> I like to point out another thing. Where do you write the transaction log
> dumps? Do you append them to the same device? Do you ever use WITH INIT?
> Here is something to be careful with so that you don't lose part of a
> log chain.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Does a Full Backup include transaction logs

In MS SQL 2005 when you do a Full Backup does it also backup and
truncate the transaction logs or do I need to back the transaction
logs up separately?

Thanks.
BrianBrian D (bdaltilio@.yahoo.com) writes:

Quote:

Originally Posted by

In MS SQL 2005 when you do a Full Backup does it also backup and
truncate the transaction logs or do I need to back the transaction
logs up separately?


You need to backup the transaction log separately. The rationale is that
the last recent backup may have gone lost, or be broken. If the log
backups are OK (and you saved them), you can recover from an older
full backup + the translog backups.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,

We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?

Brian|||Brian,

If you want to save a little bit of backup space, run the 6pm log backup
just before the full backup. If you run it after, you will have backed up
the 12pm-6pm log activities twice.

Do NOT truncate the log ever, if you want to be able to restore the full
backup and then apply the logs to the full backup. If you were to backup
the database, then truncate the 6pm logs, the 12am, 6am, 12pm log backups
are all useless, since there is an unbridgeable transaction log gap between
the full backup and the first log.

RLF

"Brian D" <bdaltilio@.yahoo.comwrote in message
news:1184261915.544240.106090@.d55g2000hsg.googlegr oups.com...

Quote:

Originally Posted by

Erland,
>
We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?
>
Brian
>

|||Brian D (bdaltilio@.yahoo.com) writes:

Quote:

Originally Posted by

We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?


First ask yourself: if the database goes belly-up, how much can we afford
to lose? In the case, the database file goes bad, there is a possibility
to take a final backup of the log. But if the log file disappears, this
means that you could lose up almost six hours of work. Can you afford
that?

No, that is it not a leading question. There are businesses where even
the loss of five minutes of data is a disaster. And there are businesses
where a full backup once a night without log backup is perfectly sufficient.
I just want you to evaluate where you fit in. Taking log backups as rarely
as you do, appears a bit unusual, so it might be that your business is
content with restoring the backup from last night. In which case, dealing
with the log is just extra overhead for you. For the rest of the post I will
nevertheless assume that this six-hour window is right for you.

I can't see that it matter whether you back up the log before or after
the full backup, but you should back up the log sometime there. In a
way log backups and full backups are independent of each other.

Russell mentioned that you should never truncate the transaction log.
I like to point out another thing. Where do you write the transaction log
dumps? Do you append them to the same device? Do you ever use WITH INIT?
Here is something to be careful with so that you don't lose part of a
log chain.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Does a Full Backup include transaction logs

In MS SQL 2005 when you do a Full Backup does it also backup and
truncate the transaction logs or do I need to back the transaction
logs up separately?
Thanks.
BrianFull backups do not mark any log segments as inactive, Therefore no segments
will be truncated following a full backup. Only log backup mark segments as
inactive. Full backups do not interrupt the log backup chain.
This is so if you have a bad full backup, you can go to an earlier full
backup and restore logs through the time of the bad backup and up to
current.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184253274.265128.108590@.w3g2000hsg.googlegroups.com...
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
> Thanks.
> Brian
>|||Brian D (bdaltilio@.yahoo.com) writes:
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
You need to backup the transaction log separately. The rationale is that
the last recent backup may have gone lost, or be broken. If the log
backups are OK (and you saved them), you can recover from an older
full backup + the translog backups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Erland,
We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?
Brian|||Brian,
If you want to save a little bit of backup space, run the 6pm log backup
just before the full backup. If you run it after, you will have backed up
the 12pm-6pm log activities twice.
Do NOT truncate the log ever, if you want to be able to restore the full
backup and then apply the logs to the full backup. If you were to backup
the database, then truncate the 6pm logs, the 12am, 6am, 12pm log backups
are all useless, since there is an unbridgeable transaction log gap between
the full backup and the first log.
RLF
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184261915.544240.106090@.d55g2000hsg.googlegroups.com...
> Erland,
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
> Brian
>|||Brian D (bdaltilio@.yahoo.com) writes:
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
First ask yourself: if the database goes belly-up, how much can we afford
to lose? In the case, the database file goes bad, there is a possibility
to take a final backup of the log. But if the log file disappears, this
means that you could lose up almost six hours of work. Can you afford
that?
No, that is it not a leading question. There are businesses where even
the loss of five minutes of data is a disaster. And there are businesses
where a full backup once a night without log backup is perfectly sufficient.
I just want you to evaluate where you fit in. Taking log backups as rarely
as you do, appears a bit unusual, so it might be that your business is
content with restoring the backup from last night. In which case, dealing
with the log is just extra overhead for you. For the rest of the post I will
nevertheless assume that this six-hour window is right for you.
I can't see that it matter whether you back up the log before or after
the full backup, but you should back up the log sometime there. In a
way log backups and full backups are independent of each other.
Russell mentioned that you should never truncate the transaction log.
I like to point out another thing. Where do you write the transaction log
dumps? Do you append them to the same device? Do you ever use WITH INIT?
Here is something to be careful with so that you don't lose part of a
log chain.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I agree completely with Erland. In my experience, I have never been
rewarded or punished because I did or did not have a backup. I was always
judged on whether I had a working recovery plan. Start from the recovery
side and build a complete plan, including a backup plan, that meets the
business needs. Finally, test your recoery plan to see that it actually
works and that it can be done in the agreed-upon time.
If you don't test, you have a recovery hope, not a recovery plan.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996C38071EF5Yazorman@.127.0.0.1...
> Brian D (bdaltilio@.yahoo.com) writes:
>> We do a full backup daily at 6pm. If we do a trans log backup 4 times
>> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
>> immediately before or after the full backup or can we just skip the
>> 6pm one all together? If I skip the 6pm trans log backup should I
>> truncate the log immediately after the full backup?
> First ask yourself: if the database goes belly-up, how much can we afford
> to lose? In the case, the database file goes bad, there is a possibility
> to take a final backup of the log. But if the log file disappears, this
> means that you could lose up almost six hours of work. Can you afford
> that?
> No, that is it not a leading question. There are businesses where even
> the loss of five minutes of data is a disaster. And there are businesses
> where a full backup once a night without log backup is perfectly
> sufficient.
> I just want you to evaluate where you fit in. Taking log backups as rarely
> as you do, appears a bit unusual, so it might be that your business is
> content with restoring the backup from last night. In which case, dealing
> with the log is just extra overhead for you. For the rest of the post I
> will
> nevertheless assume that this six-hour window is right for you.
> I can't see that it matter whether you back up the log before or after
> the full backup, but you should back up the log sometime there. In a
> way log backups and full backups are independent of each other.
> Russell mentioned that you should never truncate the transaction log.
> I like to point out another thing. Where do you write the transaction log
> dumps? Do you append them to the same device? Do you ever use WITH INIT?
> Here is something to be careful with so that you don't lose part of a
> log chain.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Does a Full Backup include transaction logs

In MS SQL 2005 when you do a Full Backup does it also backup and
truncate the transaction logs or do I need to back the transaction
logs up separately?
Thanks.
BrianFull backups do not mark any log segments as inactive, Therefore no segments
will be truncated following a full backup. Only log backup mark segments as
inactive. Full backups do not interrupt the log backup chain.
This is so if you have a bad full backup, you can go to an earlier full
backup and restore logs through the time of the bad backup and up to
current.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184253274.265128.108590@.w3g2000hsg.googlegroups.com...
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
> Thanks.
> Brian
>|||Brian D (bdaltilio@.yahoo.com) writes:
> In MS SQL 2005 when you do a Full Backup does it also backup and
> truncate the transaction logs or do I need to back the transaction
> logs up separately?
You need to backup the transaction log separately. The rationale is that
the last recent backup may have gone lost, or be broken. If the log
backups are OK (and you saved them), you can recover from an older
full backup + the translog backups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?
Brian|||Brian,
If you want to save a little bit of backup space, run the 6pm log backup
just before the full backup. If you run it after, you will have backed up
the 12pm-6pm log activities twice.
Do NOT truncate the log ever, if you want to be able to restore the full
backup and then apply the logs to the full backup. If you were to backup
the database, then truncate the 6pm logs, the 12am, 6am, 12pm log backups
are all useless, since there is an unbridgeable transaction log gap between
the full backup and the first log.
RLF
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184261915.544240.106090@.d55g2000hsg.googlegroups.com...
> Erland,
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
> Brian
>|||Brian D (bdaltilio@.yahoo.com) writes:
> We do a full backup daily at 6pm. If we do a trans log backup 4 times
> a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
> immediately before or after the full backup or can we just skip the
> 6pm one all together? If I skip the 6pm trans log backup should I
> truncate the log immediately after the full backup?
First ask yourself: if the database goes belly-up, how much can we afford
to lose? In the case, the database file goes bad, there is a possibility
to take a final backup of the log. But if the log file disappears, this
means that you could lose up almost six hours of work. Can you afford
that?
No, that is it not a leading question. There are businesses where even
the loss of five minutes of data is a disaster. And there are businesses
where a full backup once a night without log backup is perfectly sufficient.
I just want you to evaluate where you fit in. Taking log backups as rarely
as you do, appears a bit unusual, so it might be that your business is
content with restoring the backup from last night. In which case, dealing
with the log is just extra overhead for you. For the rest of the post I will
nevertheless assume that this six-hour window is right for you.
I can't see that it matter whether you back up the log before or after
the full backup, but you should back up the log sometime there. In a
way log backups and full backups are independent of each other.
Russell mentioned that you should never truncate the transaction log.
I like to point out another thing. Where do you write the transaction log
dumps? Do you append them to the same device? Do you ever use WITH INIT?
Here is something to be careful with so that you don't lose part of a
log chain.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I agree completely with Erland. In my experience, I have never been
rewarded or punished because I did or did not have a backup. I was always
judged on whether I had a working recovery plan. Start from the recovery
side and build a complete plan, including a backup plan, that meets the
business needs. Finally, test your recoery plan to see that it actually
works and that it can be done in the agreed-upon time.
If you don't test, you have a recovery hope, not a recovery plan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996C38071EF5Yazorman@.127.0.0.1...
> Brian D (bdaltilio@.yahoo.com) writes:
> First ask yourself: if the database goes belly-up, how much can we afford
> to lose? In the case, the database file goes bad, there is a possibility
> to take a final backup of the log. But if the log file disappears, this
> means that you could lose up almost six hours of work. Can you afford
> that?
> No, that is it not a leading question. There are businesses where even
> the loss of five minutes of data is a disaster. And there are businesses
> where a full backup once a night without log backup is perfectly
> sufficient.
> I just want you to evaluate where you fit in. Taking log backups as rarely
> as you do, appears a bit unusual, so it might be that your business is
> content with restoring the backup from last night. In which case, dealing
> with the log is just extra overhead for you. For the rest of the post I
> will
> nevertheless assume that this six-hour window is right for you.
> I can't see that it matter whether you back up the log before or after
> the full backup, but you should back up the log sometime there. In a
> way log backups and full backups are independent of each other.
> Russell mentioned that you should never truncate the transaction log.
> I like to point out another thing. Where do you write the transaction log
> dumps? Do you append them to the same device? Do you ever use WITH INIT?
> Here is something to be careful with so that you don't lose part of a
> log chain.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx