I have a database in SQL Server 2000 with 90gb of data. It is growing every day with a some millions records. In order to get superior performance I rebuild the indexes every night. The job takes about 1.5 hours, and sometimes causes to the log file to grow to 30-40 gigs. I do a backup of the log files every 20 minutes, and this causes an log truncation to happen automatically (according to the MS documention).
Problem:
The log files makes the disk goes full. I cannot insert new disks at this point.
Question:
When rebuilding the indexes takes 1.5 hours, and I do a log backup (which also does a truncation) every 20 minutes, will the log backup ever get to do something with the transaction log while its still working on a transaction (doing the rebuild of index)?
Observation:
It seems like the 20 minute log backup is of no help because I see the log files grows so big.
BR
Henrik
NorwayIn the future, you may want to point your SQL specific questions tohttp://www.sqljunkies.com/forums/.
Here is some code I run in one of my jobs to shrink it down:
DBCC SHRINKDATABASE (dbname, 10)This first shrinks the db and then the log file and sets the size to be 10 MB. Then, it backs up the log file and shrinks it again. When I'm done, I get about 8-10 MB off of the db and my log file will be only 1 MB.|||Thanks for you reply and interest.DBCC SHRINKFILE(dbname_Log, 10)
BACKUP LOG Website WITH TRUNCATE_ONLY
DBCC SHRINKFILE(dbname_Log, 10)
DBCC SHRINKDATABASE AND SHRINKFILE actually removes data, which is not the intent.
The backup command issued is what I'm talking about. What happens if you issue a CREATE INDEX blabla WITH DROP at 09.00 PM, and this takes 1.5 hours. Then, at 09.20 you issue a BACKUP LOG blabla. Will the last command help on reducing the log file, or will it not do any work before 1.5 hours has past?
DBCC Indexdefrag is neither an option for me.
-Henrik
No comments:
Post a Comment