Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Everytime I restart my server, the database is always in recovery mode, and it takes about 20 minutes for it to behave as normal. This always and only happens when I restart the server, so I have a few questions...

I was told this could be caused by a large log file? Could that be correct? If not then what could be the other causes?

I need to lower the space of the log file to prevent recoveries. What is better: shrinking or truncating?

How can I shrink or truncate a log file / database to lower the size? What is the syntax?

This question came from our site for professional and enthusiast programmers.

Do you tend to have large transactions in flight when you shut down? What is the recovery interval set to?
–
Martin SmithSep 3 '12 at 14:42

no actions are performed 20 minutes before server restart, other than select statements, the interval is set at 0.
–
pick4ponySep 3 '12 at 14:55

How frequently do you re start? How frequently do you backup the database? I am wondering why you are re starting the server on a regular basis? To be complete, you can manually checkpoint a database (which clears the log) if need be.
–
Lynn LangitSep 3 '12 at 16:13

"To be complete, you can manually checkpoint a database (which clears the log) if need be." how can that be done? and when you say clear a log, do you mean, not to use or simply wiping the log?
–
pick4ponySep 3 '12 at 16:34

Not enough information. Recovery model? Are you using features like mirroring or replication? Size of the database and files involved? Does the database handle any large transactions?
–
Jon SeigelSep 4 '12 at 1:41

3 Answers
3

I have the same issue and I believe I have resolved it but I have not been able to fully test it to confirm.

I believe the issues is related to the number of VLFs you have in your log file and not its size. If you have a large logfile it is likely that it grew organically through auto growth events and that it was not an intentional planned growth. If that is the case the you might have thousands of VLFs inside log files.

I believe the issue is that with so many VLFs it takes SQL server a long time to assess their state and then bring the database out of recovery. If you shrink your log file to the smallest size you can, often the size of the first VLF that was created in the log file, then you can immediately intentionally grow it again and thereby have it create the right number of VLFs (something less than 16).

Once this is complete I believe you will be able to see that your database comes out of recovery much faster.

I have not had a chance to test fail over of our production instances after I resolved our own VLF issues so I would be very curious if you can confirm this is the root cause of the issue. Experimentally I have seen the time it takes to come out of restoring in our staging environment dramatically reduced due to this so hopefully that is it.

Long-running uncommitted transactions increase recovery time for all types of checkpoints.

It is generally not recommended to run any kind of DBCC shrinkfile on production databases. Also log truncation behavior HAS changed from earlier versions to 2008 (thanks @Edward) - per this blog:

Backup log with trucate_only is no longer supported in SQL 2008. If your database is in bulk-logged or full recovery model then schedule T-Log backup on regular interval and it will keep your t-log is shape.

Again, I will mention, how frequently do you back up the database? Typically, regular backups "manage" log size best.

Reducing the size of the online transaction log can fix the problem, i.e. speed up the database getting online, but you should think about the disaster recovery before you do that.
Note that if you're in Simple recovery model, you will not be able to restore to a point in time.
On the other hand, if you're in FULL recovery model, the best way to keep the size of the online transaction log is to create transaction log backup on regular bases (schedule it).

Truncating the transaction log doesn’t free physical hard disk space, it only allows the SQL Server to reuse that space for transactions that have occurred since the last CHEKPOINT (since the last transaction log backup).

If you shrink the database, you'll reduce the size of the files. To shrink the MyDB database by 15 percent: