I've inherited the backup jobs from the previous Database Administrator and they are not "normal" jobs as far as I can tell. The transaction logs are backed up hourly and the database itself is backed up completely every night after several tables? indexes are re-indexed.

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!

    Requires Free Membership to View


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:

sp_helpdb DatabaseName

From the results of the above stored procedure use the NAME for the transaction log file.


i.e. DBCC SHRINKFILE (Northwind_Log, 10)


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.
—Regan G.

Do you have comments on this Ask the Expert Q&A? Let us know.

This was first published in May 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: