Take the 2-minute tour ×
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.

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

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

I'm sure there are a zillion steps which individual people may recommend, like that I just mentioned, but are there any recommended by Microsoft, or widespread industry standards?

share|improve this question
add comment

2 Answers

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 :

share|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 Aug 26 '13 at 23:31
    
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
add comment

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'.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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