Sql-server – How to stop Log file from growing in SQL Server 2008

loggingsql serversql-server-2005sql-server-2008

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

Best 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

Related Topic