Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["        <li><a href=\"http://area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"http://careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2014 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \r\n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2014.4.18.1558\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Server Fault works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </noscr"], "copyright__copyright__copyright": [", 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.\n\nshare|improve this answer\nadd comment\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}