SQL Server Performance Forum – Threads Archive

Transaction log size after DBREINDEX

I have a query and am a bit perplexed with few facts. In our shop we have a database of 40+ gigs & has weekly optimisation jobs (DBCC DBREINDEX), which shoots the size of Transaction Log of the database around 20 GB after the operation. And due to this big txn log, the regular txn log backup fails on lack of space. My query is that, a complete full backup of the database should reduce the txn log (hopefully i am correct) from my understanding, I re ran the full back up and tried txn log backup too, and find that the txn log backup job fails due to lack of space (still!). Can anybody clarify for me where am i going wrong in understanding this ? DB is in BULK_LOGGED recovery model. Thanks in advance RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

Full database backup has no effect on t-log at all; it won’t reduce its size. Bulk_logged recovery mode should ensure that the t-log doesn’t grow excessively large during reindexing. If this is an issue, however, I would recommend (immediately after reindexing) issuing BACKUP TRAN db WITH NO_LOG to clear the log, then full database backup, then you will be able to continue with t-log backups. However, I use BULK_LOGGED recovery mode during reindexing on my servers and it does not produce large t-logs. You may wish to check it’s not something else (in addition to reindexing) which is creating large logs, e.g. batch jobs, etc. Tom Pullen
DBA, Oxfam GB

BACKUP LOG & Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file. For information on shrinking the size of a physical log file, see Shrinking the Transaction Log. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>