Maintenance Plans and Transaction Logs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Maintenance Plans and Transaction Logs

I just (for the second time in two weeks) had to do some emergency transaction log dumps because the drives that house our transaction logs ran out of space. We have had the experience of going from over 30 GB of space available on that drive to zero in under 24 hours. Generally, as long as our regular log dumps are working, we don’t have a problem. However, it seems that our backups scheduled for Saturday morning failed and then on Sunday morning we ran out of space. I have been trying to figure out what would take up that much log space in such a short period of time. We really don’t have any significant production running over the weekend and what’s more, the problem is occuring accross several databases for unrelated applications. This brings me to my question. I have a database maintenance plan scheduled for Sunday morning to reorg and reindex all of my user databases. Does a database reorg write a large amount of data to the transaction log? Should a translog dump be done immediately following a reorg? Thanks for any help you can offer.

Yes, reindex write a large amount of data to the transaction lol.
What kind of backup do you have on week end? Full backup?. If yes may be you have to shrink transaction log after backup and before maintenance plan.
Luis Martin
Moderator One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
I suggest you to perform frequent log backups to keep the size of Tlog logical space. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.