log file growing to fill disk during maintenance

[Luvsql]

I am trying to rebuild the tables within a database as it is archived weekly (ie high amounts of data moved weekly). The rebuild runs for about 5 hours and fills up 72Gb of space on the drive where the log file resides. The log file was initially 1Gb. The database is 16Gb, but only using 9Gb of space. The database is set to full recovery, with hourly backup jobs of the log.

Why would the rebuild of a database only 16Gb of space grow to over 72Gb? Can it be rebuilt without being logged, so it won't grow?

 

[bikerboy718]

I would switch the recovery model from full to bulk-logged so that way the transactions are not stored so granulary in the log file.

With Great Power Comes Great Responsibility!!!

Michael

 

[Catadmin]

Or you could set the DB to SIMPLE mode during the rebuild and switch it back afterwards.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Luvsql]

I had hourly backups of the log, so after each hour, it's supposed to truncate the log, but it doesn't appear it did at all.

 

[bikerboy718]

Are you still using the full recovery model? I have noticed that even though in some places it is written that the log file is supose to clear after a backup of the transaction log file when the recovery model is set to full it does not happen. That is my experience anyway. However it works perfectly with Bulk-Logged.

With Great Power Comes Great Responsibility!!!

Michael

 

[Catadmin]

Just because the transaction log backups clear the log, doesn't mean it's actually freeing up the space in the transaction log file to the OS. You also need to check and see if you truly have "Truncate Log on Checkpoint" selected. If you don't, and it isn't part of your Translog maintenance plan, then this might be your issue.

Before you do any log corrective action, you want to use Profiler to locate what Queries/jobs are causing the growth problem so you can see if you can correct them. After that, though, schedule a regular ShrinkFile/ShrinkDatabase job (run as T-SQL commands instead of via the database maintenence plan) that truncates the space and releases it back to the operating system. Just remember, though, if you do the second step without the first, it's not really going to correct the problem and will cause your backup to take longer because it has to re-add all that space back to the transaction log.

Hope that helps.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mutley1]

Hi Luvsql,

First things first - when you say you are "rebuilding" the tables, do you mean you are rebuilding the indexes? This 'can' take up a huge amount of the transaction log whatever mode your recovery is set to (worse in full recovery though as Catadmin rightly states!). It's just if it is in simple then it will checkpoint (hence clear) more frequently.

If you mean rebuild as in clear it out and reinsert data then that can also use the log heavily (but bulk insert as suggested by bikerboy) would cut this out.

Need a bit more info as to what you mean by "rebuilding the tables" to nail the problem.

If it is dropping all the data in the table and reimporting it, then the best thing to do is

1. script out the indexes
2. drop all indexes
3. truncate the table (use the command 'truncate table mytable'). This doesn't log individual deletes to the log file as opposed to 'delete * from mytable' which does (and hence causes log growth)
4. Import the data
5. Reinstate indexes.

Basically, if you are insertng a lot of data with indexes still in place, then the log will take a bigger hammering as it is reorganising everything and keeping a track of it all. It really depends what you mean by "rebuilding tables"....indexes or data...or both!!

HTH,

M.

 

[Luvsql]

I am running the DBCC DBREINDEX.