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.
Dig Deeper on SQL Server Backup and Recovery
Related Q&A from Greg Robidoux
Avoid restoring your SQL Server database to solve the SQL Server error message "server out of memory." The problem could be the memory settings.continue reading
Find out how to restore data from a corrupt database.continue reading
Back up tables from a SQL Server database to a file with these commands.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.