Does SQL Server issue when we shut down windows/SQL Server ?

Question

Does SQL Server issue when we shut down windows/SQL Server ?

Joy Smith San

SSC-Insane

Points: 24877
More actions
August 12, 2011 at 1:52 am

#390176

hi
Does SQL Server issue CHECKPOINT when we shut down windows/SQL Server ?
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It'll try to shut down cleanly, depends what's running/outstanding. There's only so much time allowed during a restart.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

It'll try to shut down cleanly, depends what's running/outstanding.

Thanks Gila.

Can you explain a bit more pls.

When SQL will decide NOT to shut down cleanly.?

There's only so much time allowed during a restart.

This point too I din't get..

Thanks again.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Say there's been an update running for 5 hours and someone initiates a shutdown. Since that hasn't committed, SQL would have to roll it back. A rollback typically takes longer than the original operation.

The windows shutdown is not going to wait 5 or more hours for SQL to finish up that rollback. If the service doesn't stop by itself in the allowed time, windows kills it. In that case, SQL would not shut down cleanly (that 5 hour rollback still needs to happen) and so SQL would finish the rollback after restart.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 4

Note that the SQL files should still be consistent after a restart, but it might take some time for them to come online, for the reasons Gail outlined.