This month for T-SQL Tuesday Mike Donnelly (b/t) decided to pick a topic to fit in with Ed Leighton-Dick’s (b/t) #SQLNewBlogger challenge. His highly open ended topic is to learn something new and blog about it. A great topic for a bunch of new bloggers! (and long standing ones too) For a brief second I thought “well this one will actually be easier for more Jr DBAs” but then I realized that SQL Server is so huge a product with so many different pieces that even someone like Paul Randall (b/t) would have many many choices of new things to learn. In my case I had actually finished this post when I realized it would fit in well with this months topic.

I had an interesting situation the other day where I had a production server that was running slowly. No specific numbers, just several critical jobs that normally take minutes that had been running for hours. They weren’t blocked and the query plan was fine. Nothing much was going on within SQL except for a number of OLEDB waits.

We had been through this before with and so we have a pretty good idea of what’s wrong (I’m not going to go into it here). The decision was made to shut the server down and move on. We are upgrading to a new machine next month and that should fix the problem.

So rather than just shut down the server and have the SQL Instance crash I decided it would be best to shut it down gracefully. Unfortunately one of the symptoms of the problem is that it’s impossible to remote into the server. In fact it’s a VM and our VM team couldn’t get in using their tools. This means that I was going to have to shut the instance down remotely.

After a bit of research I found this SO question. How to remotely Start/Stop SQLServer services kicking off existing connections? There is a great answer in there with several options.

• Use psexec to run net stop remotely.
• Connect remotely using Configuration Manager or Management Studio and shut down using one of them.
• Use the T-SQL SHUTDOWN command (with or without the NOWAIT option).

Using psexec and net stop wasn’t really an option for me since I’m not really comfortable with either command and really didn’t want to mess anything up. Of all of the choices my preference would probably be to connect to your remote instance using Configuration Manager and then shut it down. However, in this particular case I was in a hurry and my go to method for just about anything is T-SQL, and of course the SHUTDOWN option is T-SQL. So I ran the command.

SHUTDOWN WITH NOWAIT

And it worked admirably. I was instantly disconnected and my instance was shut down. This is NOT something I recommend except in dire circumstances. It instantly disconnected all connections and begin shutting the instance down. All transactions are rolled back, all non-committed work is lost. And it’s FAST. I won’t even be typing this command into a query window unless I plan on executing it immediately. There is too big a risk of running it accidentally. If circumstances warrant it then you can be quite a bit safer by excluding he WITH NOWAIT option.

SHUTDOWN

This has the benefit of waiting until all existing transactions are committed and then running a checkpoint on all databases.

Fortunately only members of the sysadmin or serveradmin have the authority to run the SHUTDOWN command.

There you go, I’ve learned something new. And the logo for T-SQL Tuesday is blue. And I borrowed the post idea from Mike. Hmm. My sister-in-law is getting married in two weeks. Coincidence?

Filed under: Microsoft SQL Server