Search

SQL Server Service Control Manager Shutdown does not CHECKPOINT by xor88

Resolved
as By Design Help for as By Design

5
0
Sign in
 to vote
Type: Bug
ID: 776427
Opened: 1/13/2013 6:02:00 AM
Access Restriction: Public
0
Workaround(s)
2
User(s) can reproduce this bug
According to http://msdn.microsoft.com/en-us/library/ms188767.aspx a shutdown via the Service Control Manager does perform a checkpoint. This is very important behavior that ensures a quick service start and fast database recovery on startup.

Unfortunately, with SQL Server 2012 (11.0.3321.0) it is not happening. Database recovery on service startup can take very long and the SQL Server log clearly indicates recovery activity going on.

A service stop via Service Control Manager should generate a CHECKPOINT in every database.
Details (expand)

Product Language

English

Version

SQL Server 2012 - Enterprise Edition

Category

SQL Engine

Operating System

Windows Server 2008 R2 Standard (SP1)

Operating System Language

English

Steps to Reproduce

Generate lots of DML load, then issue a service stop via Service Control Manager. Then, start the SQL Server Service.

Actual Results

Long recovery time and long recovery activity being logged.

Expected Results

As all databases should have been CHECKPOINT'ed almost no recovery activity should take place.

Platform

 

Virtualization

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by xor88 on 4/10/2013 at 4:49 AM
A possible solution would be to make SHUTDOWN kill all connections except the current one, rollback all open transactions, switch the current session into the master database and then obtain X-locks on all databases which need to be shutdown. That would quiesce the system and allow for a reliable shutdown. It should work similarly to TAKE OFFLINE which also shuts down the database cleanly.
Posted by xor88 on 4/4/2013 at 6:23 AM
Thanks for looking into this!

So you're saying that all databases that are still in use will be rudely shut down, always causing a recovery step at service startup? This design is problematic because most interesting databases are *always* in use. This means that startup time is *always* increased by 1min (which is the default recovery time).

If the user configured a higher value for the target recovery time, startup would be delayed by that amount as well.

Would you consider taking a change request for this?
Posted by Microsoft on 4/3/2013 at 4:26 PM
Dear Customer,

Thanks for reporting the issue. I am a dev looking into this issue.
I think you are using the DB when you are calling the Service Control Manager Shutdown.
If you disconnect from the BD or change your connection to Master DB, then the checkpoint should be performed.
The reason for you skipping the checkpoint because your connection and the Service Control Manager are two threads treated as two different connections, so we need the DB lock for each of them and the one comes later (in this case is the Service Control checkpoint) should not perform on the DB without the lock.
There was a bug that did not lock the DB on the checkpoint thread and it may lead to inconsistency in recovery log in some cases. This bug was fixed in SQL2012. So you may see checkpoints before SQL2008R2 because of the bug.

Thanks,
Bryan
Sign in to post a workaround.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement