dbcc indexdefrag fills log and stays | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

dbcc indexdefrag fills log and stays

I have a transaction log set at about 160GB on a 1.6 TB database. We have logshipping running every 15 mins. I ran the dbcc indexdefrag on some tables last night and the transaction log filled up to about 65GB. The log has yet to reduce in size. Logshipping is working as there are logs being created and transferred. I have researched on MS web site with no luck as to why the log hasn’t reduced. Have I missed something? The sql logs show nothing regarding this process. Any suggestions on where to start looking for a solution? Thanks. Rick Schantz
DBA Coopervision
BACKUP LOG uses the truncation method and it 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 DBCC SHRINKFILE in books online. DBCC INDEXDEFRAG involves the defragmentation of a very fragmented index can generate more log than even a fully logged index creation. The defragmentation, however, is performed as a series of short transactions and thus does not require a large log if log backups are taken frequently or if the recovery model setting is SIMPLE. 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.
quote:I ran the dbcc indexdefrag on some tables last night and the transaction log filled up to about 65GB. The log has yet to reduce in size.
I guess you are talking about physical log file size. As Satya mentioned tran log backup truncates logical log size which means it marks space occupied by backed-up completed transactions to be free so it can be reused. However this space is marked as a free space inside transaction log files and is not released back to OS. If you want to reduce log files size you need to shrink transaction log files. I would recommend not doing it. IMO you have perfect process. Tran logs are backed-up every 15 minutes preventing uncontrolled tran log growth. 65 GB logical log size is significantly bellow physical log size, so I think you don’t have to be concerned. If you see more critical growth you could set tran log backup interval even shorter during index defragmentation.

Here is what I found. I used the ‘dbcc opentran(db_name)’ on the database I had ran the dbcc command against. I found one spid that had been in use before the jobs ran. It had never ended and was still running."Crappy finance job". Anyway, once the job finished the log went right back down. Odd how that happens but something to keep in the tool kit for future use. Thanks for all you suggestions.
Obviously index defragmentation was not able to produce 65 GB log in 15 minutes. I should have considered long running transaction.
]]>