The tables are large ones, but I'm concerned about the size of my transaction log which is HUGE (19G!). I've been led to believe that these re-indexes are part of the cause of the size of the log.
Can you direct me anywhere that I might get more in-depth information on backups and re-indexing? Thanks!
Based on what you described as your backup model, this does make sense to be able to recover data if there is a failure. It is a good idea to combine both full and transaction log backups.
I'm not sure how large your database is or what types of jobs are running besides the index rebuilds mentioned above. Therefore, it is hard to determine how large your transaction log really needs to be. Here are some steps to reduce the size of your transaction log if it does not really need to be that large.
Run this command to see how much of your transaction log is being utilized: DBCC SQLPERF ( LOGSPACE )
Then, based on what is being used as well as how large you think your transaction log should be, run these commands to shrink the size of the transaction log:
From the results of the above stored procedure use the NAME for the transaction log file.
DBCC SHRINKFILE (LogFileName, SizeInMB) i.e. DBCC SHRINKFILE (Northwind_Log, 10)
MEMBER FEEDBACK TO THIS ASK THE EXPERT Q&A:
It seems to me that the cause of the large log files may well be the fact that DBCC DBREINDEX is a fully logged operation under SQL Server 2000 (which I assume to be the version of SQL being used).
If this is the case, then the shrinking of the log file to 10 MB may equate to a lot of log file growth when the DBCC DBREINDEX operation occurs. Assuming that the DBREINDEX is the cause of the excessive Log's, here are some thoughts:
It might be worth it to schedule a DBCC SHOWCONTIG before the regular DBCC DBREINDEX and determine how fragmented the user's indexes really are. Given that, the user could consider (a) using DBCC INDEXDEFRAG, (b) reviewing their DBCC DBREINDEX strategy to put in place a system that generates INDEXDEFRAG or DBREINDEX dynamically, based upon the SHOWCONTIG results.
By limiting the amount of REINDEXING being done the user should reduce the log's. Also, after reviewing the scheduled SHOWCONTIG the user can review and determine whether their DBREINDEX strategy is appropriate, and a change (decrease) in frequency may also give them some relief.
Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in May 2005