# tl;dr

I think my verbosity has obscured the real question I'm asking here, so I apologise for that. My main issue is that the checkpoint command appeared to be working for at least 800 iterations of the loop. The log size remained static at around 1GB during that period. Then, sometime overnight the checkpoint evidently failed to free the space within the log file and it consumed the drive. No database backups where scheduled. I'm at a loss as to what caused this behaviour.

Is there anything I can do to ensure that the log space is freed on completion of each loop iteration under both FULL and SIMPLE recovery modes?

I am trying to alter the schema (and make some data changes) on an extremely large table without causing any disruption to applications and services that require this table and also to keep the transaction log at a manageable size.

So I have decided to process the table data within a loop, either taking a log backup or issuing a checkpoint statement depending on the recovery model at the end of each iteration. Each loop migrates some data into a new table (both old and new tables are UNION ALLd behind a view to ensure availability while the data is only partially migrated).

However the transaction log is blowing out on the development server under SIMPLE recovery mode, but in a very odd way. I monitored the log for the first ~800 iterations of the loop (out of a total of ~3000) before going home and the log remained at a size consistent with the number of records it was processing per iteration and so the method appeared to be working. However, on arrival the next morning, I noticed that the log had exploded and consumed the entire disk with only 400 iterations to go.

I do not understand why the process appeared to work initially, and then failed at some seemingly arbitrary point. I would have expected the log to grow steadily if my logic was wrong, but this did not happen.

I truncated the log so it started at ~500KB. It grew to around 1.2GB after the first loop and stayed at approximately that size for the first few hundred iterations. Based on that it seemed as though the method was sound.

My script is summarised with the following pseudoish code:

select distinct [fk] into #tmp from [huge_table];

while exists (select 1 from #tmp) begin
select top (1) @id = [fk] from #tmp;

-- Actual query is more complicated...
delete [huge_table]
output deleted.* into [new_table]
from [huge_table] a
where a.[fk] = @id;

-- Keep log down
if @recovery = N'SIMPLE'
checkpoint;
else begin
set @logpath = '\\path\to\backups\log_' + cast(@c as varchar) + '.trn';
backup log 'database_name' to disk = @logpath;
set @c = @c + 1;
end

delete top (1) #tmp;
end


My choices at this point appear to be either a) fix the problem somehow, or b) assume that the same degenerate behaviour will not occur under FULL recovery mode in Production. I'm naturally repulsed by option B, but not sure how to go about option A.

How can I ensure that the size of the transaction log behaves itself. Is there something I am missing?

My only idea currently is to append to the end of the loop something like:

if @logsize > 10GB (or some other arbitrary number)
dbcc shrinkfile (N'database_log_file', 0, truncateonly);


But, aside from being awful in and of itself, I can't think of any way to translate this approach to a database under FULL recovery mode, so it's really not an option for me at this point.

EDIT: Also, I checked the log_reuse_wait_desc column after the failure but the result for the database in question was NOTHING, so that provided no enlightenment. I was able to do a log truncation via dbcc shrinkfile after the fact, so the log was not in any state that prevented truncation.

-

## migrated from stackoverflow.comDec 13 '11 at 14:22

This question came from our site for professional and enthusiast programmers.

Not really a fix but you can set a max size to the transaction log in SQL management studio. No good can come from a hard disk filling up. –  BalamBalam Dec 12 '11 at 1:06
@BalamBalam: that is poor advice. –  Mitch Wheat Dec 12 '11 at 1:10
@MitchWheat And why is that bad advice? Letting the hard disk fill up and locking up the whole server (not just SQL) is better than stopping a run away transaction log before it fills up the entire hard disk? –  BalamBalam Dec 12 '11 at 1:19
@BalamBalam: Both cases are equally unacceptable for my situation. The script needs to not cause runaway log files. –  Quick Joe Smith Dec 12 '11 at 2:11
Look, this question is about how to prevent the runaway log scenario in the first place, not how to deal with it afterwards. Please stay on topic. –  Quick Joe Smith Dec 12 '11 at 5:53

Even with the SIMPLE recovery model, the trans log can still blow up if there's a long running transaction going on. I'm wondering if something on your Dev server was running while your script was doing its thing and cause the trans log to not be able to truncate since the MinLSN was from some long running transaction.

To answer the question though, unless you restrict access to the DB (like running your script using ALTER DATABASE SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE or something similar like SINGLE_USER), there's no way you can force the log file to free space as other transactions may be running and the trans log has to stay consistent for those transactions.

Check TechNet (or BOL: Checkpoints and the Active Portion of the Log) for more information.

-
I don't think running in restricted access is an option, since the main requirement is that the system needs to be live during this maintenance job, and that includes querying and insertion of more data. –  Quick Joe Smith Dec 29 '11 at 10:43
One thing you can do for quick deletion of data is to switch a partition holding that data out of the table. Since you seem to be holding that data in an "audit" table, you wouldn't need to actually delete it. That being said, you need to have a partition scheme that would hold all of the values you wanted deleted at one shot and you're limited to 1000 partitions on a table. Take a look at msdn.microsoft.com/en-us/library/ms191160%28v=SQL.100%29.aspx for info on that. –  Brandon Dec 29 '11 at 21:03
Table partitioning probably isn't going to be a valid solution here as that requires that table partitioning already be in place. –  mrdenny Dec 30 '11 at 10:41

Is the table being used for anything else? Mirroring, Replication, etc? Those will cause the transaction log to blow up.

You don't want to shrink the log file within the loop. This will cause the transaction log to perform poorly and your DBA to want to throttle you. You need to monitor the process while it is running to see what's causing the log to grow. Looking at the system after the fact will be useless as the log isn't growing any more. Look at the log reuse column while the process is running and the log is actively growing. Anything else is pretty much a waste of time.

Your code looks fine as written.

-
It's occurring on a development server with no automated backups or any other task. For all intents and purposes, I have exclusive access to it. –  Quick Joe Smith Dec 31 '11 at 4:37
@mrdenny: Coworkers are encountering a similar problem with the log -- any particular Profiler settings you'd recommend to diagnose this? –  OMG Ponies Feb 25 '12 at 2:14
If you look in profiler there is an event that you can see which will tell you when the log expands. I don't have profiler in front of me so I can't look and see what it is at the moment. –  mrdenny Feb 27 '12 at 20:31

The obvious question is does this size blow up?

 delete [huge_table]
output deleted.* into [new_table]
from [huge_table] a
where a.[fk] = @id;


Try

 select top 1000 fk, count(*) from [huge_table] group by fk order by count(*) desc


Are you just moving everything in [huge_table] to [new_table]?

-
No. The foreign key reference I'm looping on has a pretty consistent amount of records across the board, and, thanks to debugging output from the script, I was able to confirm that the iteration on which the log blew up did not have an especially large amount of data. –  Quick Joe Smith Dec 12 '11 at 2:09
With regards to your second question: yes, with a little bit of processing in some case. The operation is essentially an ALTER TABLE on [huge_table], but even as much as changing a column to be nullable sends the transaction log upwards of 100GB. Hence the batching approach. –  Quick Joe Smith Dec 12 '11 at 2:09
Wow. Yes an alter table can use a lot of transaction log. If you come back and run the delete on the @id that ran away does it still run away again? If the is problem ID runs on a second try then look for environmental stuff like a backup kicking off. This is an all or nothing that is not something I would want in production but insert into the new table and don't delete but truncate when it is complete. –  BalamBalam Dec 12 '11 at 3:09
Hmm, I hadn't considered the impact of a full backup until now. –  Quick Joe Smith Dec 12 '11 at 3:22
I have been able to confirm that no full backup was run on this database overnight, so that cannot be the cause either. –  Quick Joe Smith Dec 12 '11 at 5:51

Have you considered looking at the minimal logging features available in SQL Server 2008? New update on minimal logging for SQL Server 2008. The author also has a few blog post on examples using this feature here, here, and here. it mentions use of a trace flag TF-610 that is available to help limit the log use.

-

The best way I have done this in the past to prevent the log file from growing out of control and eating up all the hard drive space during a large data insert is to set the recovery model to bulk logged. I have had to do this as a contract DBA working on servers there weren't configured well for SQL. Had I not done this the hard drive hosting the database would have ran out of space and the SQL Server would have gone off line.

ALTER DATABASE DBName SET RECOVERY BULKLOGGED


I then shrank the log file to recover as much space as possible, if you shrink the datafile(s) too, you would probably want to rebuild the indexes.

DBCC SHRINKFILE (DBName_Log, 1)

-
I have updated my question to be more clear about what I'm asking. It's not so much about minimising the data:logging ratio, but just ensuring that existing log space is reused. –  Quick Joe Smith Dec 13 '11 at 23:20