It wasn't until a recent conversation with a colleague that I realized how

confusing the many options for starting and stopping SQL can be. In part 1 of

a few I'll show you all the various methods and comment on their value, in part 2

I'll talk more about what happens on start up and shutdown.

I suspect most new users use Enterprise Manager (EM), so let's start with it.

Here is what you'll see if you already have the server registered and SQL is not

running. Clicking start will get things going. Nothing wrong with using EM -

it's the equivalent of all other methods, the only complaint I have is that

until SQL is running you have to keep clicking refresh to see if the start was

successful or failed.

I imagine the second most popular way is via the SQL Service Manager. This

application typically runs in the tray and displays the status of the service.

The downside is that just glancing at it doesn't tell you which instance

if you have more than one, you have to check the tooltip or double click it. The

real downside to this application is the memory usage. The first image below

shows it running via the processes tab of Task Manager using a startling 5m of

memory. This is installed and running by default. Whether you care about the 5m

or not depends on how much memory you have I guess! You can turn it off by

deleting the entry for it from your startup folder.

Less common but useful is to use the Services Applet from Control Panel. It's

become my favorite because it shows a progress bar as the service starts and

stops, very handy when I need to stop/start SQL as quickly as possible. Note

that you'll only see the progress bar if stopping takes a while. On servers with

8g of memory it can take 5-10 minutes.

If you're a real power user you can use the command line to manage services

(this applies to all services, not just SQL - you just have to know the service

name). These two commands start and stop a default instance of SQL.

net start mssqlserver

net stop mssqlserver

Finally, you can also run SQL directly from a command line. Normally the only

time you'll do this is when you need to start in single user mode, but if you

want to, it will work fine this way. I'm showing the short path as it is on my

machine as well as the full long name path (both of which may differ on your

machine if you changed the default folder during install.

C:\PROGRA~1\MICROS~4\MSSQL\binn\sqlservr.exe

C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe

You can also shutdown SQL via Query Analyzer. It doesn't have a corresponding

startup command since SQL would need to be running first in order to let you

enter the command.

Rebooting or restarting the entire server will also shutdown SQL. Whether it

restarts automatically depends on how you've configured SQL (typically set to

auto start).

Are there any wrong ways of stopping SQL? Pulling the plug on the server (or

associated storage) is about the only one. Removing power without going through

a clean shutdown presents the chance that one or more databases will be suspect

when you restart everything. It's a last resort option, a good example is when

you've got a server pegged at 100% utilization and cannot establish a connection

to kill the bad spid. It can also happen by just plain accident, someone pulls

the wrong plug. In practice SQL almost always recovers cleanly. Remember, this

is a last resort option.

Starting and stopping SQL isn't exciting, at least until you need to do it

the first time for real and aren't sure of the impact. Hopefully now you can

proceed with a greater sense of safety, realizing that beneath the various

applications it's all doing the same work and the only thing that really makes

one better than another is your preference in tools.