Sql-server – How to switch an active SQL Server database into Simple Recovery Mode

There are a few things that can cause the log to have to grow, even in the SIMPLE recovery model:

• A long-running transaction - the log cannot be cleared until the transaction commits or rolls back. You can use DBCC OPENTRAN to show you the oldest active transaction.
• Transactional replication - the log cannot be cleared until the log reader job has read the committed transactions

There was also a bug in SQL 2000 SP4 that prevented checkpoints from properly clearing the log - see my blog post for more details: Why won't my log clear in SIMPLE recovery mode? SQL 2000 bug or very large VLFs.

My guess is that you've got a long-running transaction.

You shouldn't need to keep shrinking the log - constantly shrinking and growing the log leads to a thing called VLF fragmentation, which can affect performance. Also, whenever the log grows it must be zero-initialized, which causes everything to wait while the initialization takes place. Let the log reach a steady-state size and leave

Checkout the long article I wrote for TechNet Magazine on understanding the log and how it behaves in the various recovery models: Understanding Logging and Recovery in SQL Server.

Hope this helps!

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.