3

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: https://stackoverflow.com/questions/4400057/how-to-stop-log-file-from-growing-in-sql-server-2008

0

8 Answers 8

2

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

3

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.
2

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.
1

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.

0

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.

0

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.

3
  • 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, 2010 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.
    – NotMe
    Dec 9, 2010 at 16:01
  • this is not an option unfortunately as this has the potential to grow more than 1TB:)
    – Chicago
    Dec 9, 2010 at 16:12
0

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).

0

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).

You must log in to answer this question.

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