Trans Log Backup takes too long after db recovery model change

Question

Trans Log Backup takes too long after db recovery model change

sql_er

SSCarpal Tunnel

Points: 4135
More actions
January 5, 2009 at 12:19 pm

#94865

Hello,
We have SQL Server 2000 Enterprise Edition. From time to time we rebuild indexes on one of our databases, which follows a Full Recovery Model. Before rebuilding indexes, I modify the recovery plan of the database from FULL to BULK_LOGGED (to prevent transaction log growth). After changing it back, I always backup up the transaction log.
What I noticed is that at this time it always takes ~ 30 minutes to back up the transaction log, whereas usually it takes just seconds. Executing DBCC SQLPERF(LOGSPACE) I saw that the transaction log grew to about 300 MB (as compared to usual 50 MB), so I could expect the transaction log backup to take a few minutes, but why 30 minutes? Does anyone have an explanation for this?
Does anything happen internally when the recovery model is changed from full to bulk_logged and to full again, that could cause this increase in time for the transaction log backup?
Thank you!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

When you perform a bulk operation under bulk-logged recovery, the details of the changes are not logged, just a record of what pages were logged. When the log is then backed up, those changed pages must be included in the transaction log backup.

Essentially, bulk-logged trades off faster bulk operations and smaller logs against log backup time and log backup size.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 2

Does what you said mean that I am not breaking any chain when I do that - meaning: If I do Full Backup up, then a few trans backups, then change to BULK_LOGGED, do bulk operations (as well as some other operations), and do trans backup again, I will be able to do point in time recovery?

Thank you!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

sql_er (1/5/2009)
Does what you said mean that I am not breaking any chain when I do that

Conditional yes.

There are some limitations on point-in-time recovery when there are bulk operations within a log backup. Please read through the pertinent sections in Books Online, as I don't fully recall the details.

You're not breaking the log chain though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

From BOL:

Point-in-time recovery is not supported with the Bulk-Logged Recovery model. Bulk-Logged recovery only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes.

MJ

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 5

Yes, according to BOL (http://msdn.microsoft.com/en-us/library/aa173529(SQL.80).aspx), point in time recovery is not supported.

At the same time, the log chain is not broken, in a sense that it allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes.

Thank you!