SQL Server Shutdown Procedures

[Arisap]

I am having a debate with one of my DBA's. He swears that if a Windows 2000 or 2003 server is gracefully shutdown (START,Shutdown), SQL server does NOT get stopped properly and can cause the database files to become damaged. I had always thought, and confirmed this with another friend who is a DBA that when a Windows server shuts down gracefully, it stops all running services gracefully as well.

I know from experience, for example, that shutting down an Windows server running Exchange (5.5 and up) gracefully, also gracefully shuts down the exchange services.

The reason for this debate, is that the DBA insists that we create shutdown scripts (when the UPS detectes a power failure) that first stop all SQL services and then shut the server down.

Opinions are appreciated.

 

[Catadmin]

I have never had any problem with Windows 2k or Windows 2k3 shutting down SQL Server gracefully when it is shut down properly.

On the other hand, I have had problems (power failures/surges or other errors) that when the server "crashes", it doesn't shut down the other services gracefully.

I don't know if there's a flaw with Windows 2k3 server that I'm unaware of that keeps the services going, but if you shut down and reboot the server (of either flavor OS), check both the Application log and the SQL Server Log and you'll see where it shuts down the services then restarts them on startup. If the SQL Server does *not* have in the old log the shutdown sequence or does not have the start up sequence in its newest log, then I would worry something else is going on with your machine.

The scripts certainly won't hurt anything, but I don't see where they are necessary if you see the series of shutdowns and start ups in your AppLog and SQL Log.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[mrdenny]

I've been a professional DBA for about 9 or 10 years now and I've never sean a server that was shut down correctly corrupt the databases.

Your DBA is nuts.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLBill]

I have had a server (Windows 2000 Advance Server) get shut off accidentally. Hit the power switch and there it went. I wasn't very happy, but when I brought everything back up and running I didn't have any database corruption. Lucky? Maybe so. But I have coworkers who are sysadmins and they back me up when I am on leave (as I have sysadmin privilege also and back them up). They have 'forgotten' the so-called proper shutdown procedures quite often and just shutdown the server. Never had a corrupt database yet.

Is it better to follow the proper shutdown procedure and stop SQL Server first? Of course. Is it necessary? Not really.

-SQLBill

Posting advice: FAQ481-4875