September 17, 2011 at 9:28 am
I have a weekly reindex job for all databases in maintence plan.
Sometimes it failed with the message transaction log is full.
We do have a transaction log back up before it 1 hour ago.
And now when I check it the transaction log is 99% free.
This because after the reindex, the hourly transaction log back up ran.
How can we avoid this problem of the reindex makes transaction log full?
Thanks
September 17, 2011 at 9:37 am
There are two ways of handling this situation
1. Backup transaction log in between
2. Are you taking a full backup after this task? Then you can change your recovery option to Bulk log or even simple during this operation and then revert that change.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
September 17, 2011 at 9:42 am
Thanks a lot.
So in 1. I can do the transaction log backup during the process of reindex?
2. I do have a full backup, but it is on next day.
So you ask if I have a full backup, does it mean I should do a full backup right after the revert of recovery ?
We do have transaction log back up after that, will that work?
September 17, 2011 at 10:15 am
sqlfriends (9/17/2011)
So you ask if I have a full backup, does it mean I should do a full backup right after the revert of recovery ?We do have transaction log back up after that, will that work?
A log backup is sufficient. Just familiarise yourself with the bulk-logged recovery model first and what it means for point-in-time restores. Also note that while your log won't grow, the log backup will be the same size or larger.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply