Home Dashboard Directory Help
Search

SQL Server Service Control Manager Shutdown does not CHECKPOINT by xor88


Status: 

Resolved
 as By Design Help for as By Design


7
0
Sign in
to vote
Type: Bug
ID: 776427
Opened: 1/13/2013 6:02:00 AM
Access Restriction: Public
0
Workaround(s)
view
4
User(s) can reproduce this bug

Description

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
Sign in to post a comment.
Posted by Microsoft on 1/7/2014 at 10:08 AM
Hi,
Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing this bug as we do not have plans to address this in the current version of SQL Server.

Thanks again for reporting the product issue and continued support in improving our product.


Sincerely,
Ajay Jagannathan
Senior Program Manager
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.