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.

In my application right after restore I perform many(>100,000,000) updates to the newly created database. Thus, LOG files grows significantly.

How can I stop it from growing?

NOTE: Setting recovery model to simple is NOT going to work.

Also , this question was originally posted here: http://stackoverflow.com/questions/4400057/how-to-stop-log-file-from-growing-in-sql-server-2008

share|improve this question

8 Answers 8

You'd need to:

  1. Switch to simple recovery mode or backup the transaction log more frequently
  2. Apply your updates in smaller batches with a looping mechanism.
share|improve this answer

Can you change the recovery model to simple, long enough to perform the shrink, and then set the recovery model back?

Something like:

alter database <mydb> set recovery simple
go
checkpoint
go
alter database <mydb> set recovery full
go
backup database pubs to disk = 'c:\mydb.bak' with init
go
dbcc shrinkfile (N'mydb_log' , 1)
go

I admit I borrowed that from http://sql-server-performance.com/Community/forums/p/28345/151682.aspx

That link also links to: http://madhuottapalam.blogspot.com/2008/05/faq-how-to-truncate-and-shrink.html

share|improve this answer

As I suggested on stackoverflow:

You'd need to:

  1. Switch to simple recovery mode or backup the transaction log more frequently
  2. Apply your updates in smaller batches with a looping mechanism.
share|improve this answer

You will need to perform log backups more often. Maybe every 5 minutes during those updates happening.

A differential backup at night should be able to help you not relying on the overwhelming amount of log backup taken before, and if you are not interested on having a point-in-time recovery capability.

share|improve this answer

One thing that came to mind is that you could utilize bulk update in order to insert/update those 100M records. See this info at MSDN for a discussion on minimal logging with bulk updates.

share|improve this answer

The only real way to get control of your logging is to perform regular sql backups. If you can't go to a simple recovery model, then your backup strategy should include performing transaction log backups in order to set a checkpoint on them. Once the checkpoints are set, sql server can start recovering the disk space.

share|improve this answer
    
We did switch to simple recovery model but that did not help at all. As I understand the log file grows because of many updates or large transactions that are active for a long time. That's when I would like to turn off logging. –  Chicago Dec 9 '10 at 15:46
    
I don't believe it is possible to turn logging completely off. You might be better served just buying a 1TB or larger drive to put just the logs on for this situation. Incidentally, having the logs on a separate drive will generally increase performance. –  Chris Lively Dec 9 '10 at 16:01
    
this is not an option unfortunately as this has the potential to grow more than 1TB:) –  Chicago Dec 9 '10 at 16:12

The simlpe answer is - you do not. SImple recovery model is as good as it gets. Bassically Log file is needed for the whole data write scenario (the database is NOT written when you commit an update).

share|improve this answer

You can't stop SQL from using the log file, that's just how SQL works. You can, however, still keep the file small by making sure that SQL re-uses the file frequently. Two ways to do this:

  1. Switch db to Simple mode, and execute smaller-sized batches.
  2. Keep the db in FULL mode, perform frequent transaction log backups, and execute smaller-sized batches.

Switching to Simple mode is the easiest. If you can't do this, then take very frequent (every 10-15 minutes?) transaction log backups.

In both of these cases you will need to make sure you are not doing all the updates in one huge batch (UPDATE GIANTTABLE ... with no WHERE clause). You might need to use UPDATE TOP, a WHILE, or a cursor to only update a limited number of rows (say 100,000 or so).

share|improve this answer

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.