2

We have a database in SQL Server 2008 for which the Recovery model is set to Simple.

Periodically, we run a big update on a big table (15 million rows to update). To accomplish this, we run a Stored Procedure which takes 2 hours+ to run. When the Stored Procedure finishes to run, the log file grew up to 37GB which is kind of strange since the recovery model is Simple and that we don't even explicitly begin a transaction in the Stored Procedure (we make a full backup of the DB prior to update for safety)

Also, when we shrink the log file, it goes back to 1MB

Is it possible to just prevent the log file to grow up 37GB ?

Thank you

3 Answers 3

1

I had a similar problem. I needed to delete a couple million old records. The delete failed after about 30 minutes because the available space for the logfile was too small. We solved it by modifying the delete statement to delete only 500,000 records at a time. This solved the problem.

To apply that knowledge to your case. Can you run a few smaller updates instead of one big one? This can be done by explicitly adding 'begin transaction' and 'commit' statements in your stored procedure. In addition to statements at beginning and end of the stored procedure, you can issue a commit after a million lines and start a new transaction than. I wouldn't do a commit after every update since this will slow down performance greatly. Another option would be to limit the stored procedure to a certain amount and call it several times or create similar stored procedures that work on different parts of the data.

Since you are not using explicitly the transaction boundaries, you might have some luck setting the transaction isolation level to uncommitted reads. See here. I think it might speed up the processing, but I don't expect it to have a major impact on the transaction log. Be aware that you enable dirty reads by setting the isolation level to uncommitted reads.

4
  • That would be something worth to try, but it does not explain why the log file keeps its huge size after the Stored Procedure finishes, why don't it shrinks back automatically to 1MB.
    – MaxiWheat
    Jan 6, 2010 at 20:57
  • 1
    It does. The log has to store ALL CHANGES IN THE CURRENT TRANSACTIOn. If that is 37gb worth of changes, it does so.
    – TomTom
    Nov 14, 2010 at 17:52
  • actually an older version of SQL Server did not reduced the filesize automatically (at least not immediately). The file was huge but it was only used to, let's say 1%. To reduce the physical size on disk, you needed to run a task to reduce the filesize (don't remember what it was called). Usually, telling the task, to reorganize the file (moving pages to beginning of file) and everything was fine. Nov 15, 2010 at 13:33
  • @TomTom: Depending on the synchronization level of the DB, the log does NOT need to store the transaction after a rollback or commit. Therefore, it might have been possible to shring it right away. But I think for perfromance reasons, this does NOT happen until you manually or automatically (usually a scheduled job during the night or on Weekends) trigger a maintenance function to do this. Nov 19, 2010 at 15:43
0

I'd also suggest trying to break up the operations to minimize log growth. SQL Server won't automatically shrink the log unless you have auto_shrink enabled, which can cause a major performance problem.

0

AFAIK, autoshrink only runs every 30 minutes. How long after the stored procedure finishes are you looking at the log file? Also, how many database are on this server? Autoshrink works on a round robin basis and may take a while to get around to this particular database.

4
  • As we did not know the first time it occured that the logfile would get that big, and did not know also that shrinking from 37GB to 1MB was a matter of a few seconds, we waited until the next day before running the Shrink command, and the next day the logfile was still 37GB. Autoshrink is not turned on for that DB, which I think explains why it remained 37GB. My question turns out more like how can we avoind to get it up to 37GB, we though that simple recovery would prevent that.
    – MaxiWheat
    Jan 7, 2010 at 13:37
  • 1
    AFAIK, the log can not be shrunk while there's a long running transaction writing to the log. I don't think it's going to shrink while the stored procedure is running, only after the stored procedure completes and the log has been comitted to the database. You might want to modify the stored procedure to avoid this, as others have posted here.
    – joeqwerty
    Jan 7, 2010 at 14:25
  • The log can't be shrunk while the transaction is running, since it contains the necessary information for an rollback. After commit and rollback, you can shrink it since the information is not needed anymore. Jan 8, 2010 at 19:56
  • Which is what I was saying. Thanks for being more succinct.
    – joeqwerty
    Jan 8, 2010 at 20:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .