Q

More information on backups and re-indexing

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!

VIEW MEMBER FEEDBACK TO THIS ASK THE EXPERT Q&A.

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.

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


Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in May 2005

Dig deeper on SQL Server Backup and Recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close