24

Are there any special steps necessary to prevent data corruption when restarting a server hosting an MS SQL Server instance?

For example, I recently encountered the recommendation of stopping the SQL service manually. My understanding is that this is handled by the Windows shutdown process.

I'm sure there are a zillion steps which individual people may recommend, like that I just mentioned, but I'd like to avoid repeating obsolete or superstitious practices. Are there any recommendations from Microsoft, or widespread industry standards?

This question relates to the short-term procedure of rebooting a machine. There's another question regarding the long-term procedure of ensuring that a machine is unused, before taking it down permanently.

| improve this question | |
  • I can't make sense out of asking for "widespread industry standards" and not wanting a "zillion steps which individual people may recommend". That's presumably their account of industry standards. Both of these seem like valid questions, but the authoritative source is going to be a link to the Microsoft article. – Evan Carroll Jan 16 '18 at 20:46
14

You don't have to be fancy/worried or scared when you are restarting sql server.

Just make sure that you dont have any long running transactions. Best is to restart sql server using console or shutdown command during a low/minimum activity period also called maintenance window to minimize impact on your business.

If you have any DR setup and you dont want to be down, then best is to failover and then restart the passive or secondary node.

Clean Shutdown SQL Server occurs in below scenarios :

  • Stop sql server using Services console.
  • Shutting down your server
  • running SHUTDOWN command in SSMS

In above all situations, sql server cleanly shutsdown all its databases and then terminates the service which involves commiting or rolling back all the transactions, writing all dirty pages to disk and then writing an entry into transaction log.

Improper shutdown of sql server :

  • shutdown with nowait
  • pulling power cable from your server (if you have access).
  • killing sqlserver.exe from task manager
  • Dirve failure on which sql server binaries, exe, system databases reside or windows system drive failure .. usually C:\ drive.
  • overheating of the server causing it to shutdown (should rarely happen !!)

SQL Server will always try to do a clean shutdown ...unless you do something improper as stated above.

Some really good reading links on what happens behind the scenes during recovery phase :

| improve this answer | |
  • Great detail, thank you. What do you mean by a "DR Setup"? – Jon of All Trades Aug 26 '13 at 23:12
  • @JonofAllTrades Disaster Recovery .. e.g. Log shipping, Database Mirroring or you can even think of High availability like Clustering – Kin Shah Aug 26 '13 at 23:31
  • 1
    Whilst I totally agree with this list of 'improper' shutdown scenarios, data corruption should not happen even then, thanks to Write-Ahead Logging. – John Alan Aug 27 '13 at 19:07
5

This is all detailed exhaustively on this page.

Being that your question specifically asks "are there any recommended by Microsoft" I'm inclined to think that this is counter-productive to have this discussion here. The article their details the process through

  • Using either
    • command line
    • Powershell,
    • SQL Server Management Studio (GUI)
  • For 2008, 2012, 2014, 2016.
  • For the either
    • Database Engine
    • or, Agent

Whether or not those steps are satisfactory would be my opinion, which you don't want. So the right answer will always be most up to date there.

Stopping the service, prior to power down

is it necessary or recommended to do so before shutting down a server that happens to be running SQL services.

No, it's not necessary. When the Windows Kernel sends the signal to shutdown to SQL Server, it will do so in a fashion that is safe and the system will wait for it to complete. Speaking generally, anything built with the ability to safely shutdown does not have to be shutdown manually, and it stands to reason all Microsoft applications follow their own API and procedures tying into the PRESHUTDOWN, or SHUTDOWN phases. From the docs on PRESHUTDOWN, which I assume they're using,

Notifies a service that the system will be shutting down. Services that need additional time to perform cleanup tasks beyond the tight time restriction at system shutdown can use this notification. The service control manager sends this notification to applications that have registered for it before sending a SERVICE_CONTROL_SHUTDOWN notification to applications that have registered for that notification.

A service that handles this notification blocks system shutdown until the service stops or the preshutdown time-out interval specified through SERVICE_PRESHUTDOWN_INFO expires. Because this affects the user experience, services should use this feature only if it is absolutely necessary to avoid data loss or significant recovery time at the next system start.

As it may necessary, I assume that's how SQL Server works.

| improve this answer | |
  • That's almost what I was asking. That document describes how to shut down the SQL Server engine, but does not answer whether it it necessary or recommended to do so before shutting down a server that happens to be running SQL services. – Jon of All Trades Jan 18 '18 at 23:03
  • @JonofAllTrades updated again. – Evan Carroll Jan 18 '18 at 23:16
3

Not exactly when it comes to shutting down and preventing DB corruption. MS SQL Server is a very mature product and the odds of causing a corruption issue by a simple 'shutdown' would be a edge scenario. You're much more likely to cause corruption by not running CHECK DB or having checksum validation set on your DB.

Perhaps having external tools directly touching the MDF/NDF/LDF files could cause issues, such as trying to 'move' the files in between shut downs or having some software try to lock the files during shut down. I've seen Windows Clustering screw up when a disk hosting DB files is full, but not specifically cause 'db corruption'.

If you want to help ensure a smooth shutdown or failover, you can run a checkpoint, make sure you are running DBCC CHECKDB often (at least enough times to be able to recover corrupt data from a backup), and check that any external dependencies are taken care of such as mirroring.

If any experts DO have other 'best practices' I'd love to hear them however, but scouring the blogs and online resources for the past several years, I haven't seen much in data corruption and a simple 'shutdown/restart'.

| improve this answer | |
-1

The way I do it: 1) Disable all Jobs. 2) Make sure no jobs are currently executing. 3) Run SP_Who3 frequently to check activity, also run sp_whoisactive for more info. 4) If there's no activity and the only thing you see is your current sp_who3 query 5) Take DBs offline 6) Right click at the top of the database and click Stop 7) Verify if services are in a stopped state in services.msc 8) Done

PS. Should you have a PAGEIOLATCH / IOCOMPLETION or any other activity in SP_Who3 do not do the above as it could put your databases in recovery mode.

| improve this answer | |
  • Very rarely is it feasible or worth it to stop all traffic to the database for most people. I'm also note sure what benefit there is to taking all the databases offline - that'll just add more time to the process. – LowlyDBA Jun 19 '19 at 13:31
  • This is not a good advise at all. why would you suggest to take dbs offline - if dbs are in HADR e.g. mirroring or AG, you wont be able to do that. – Kin Shah Jun 19 '19 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.