In two previous articles (Part 1 and Part 2), I've looked at some of the details surrounding starting and stopping SQL. In a lot of ways it's simple, but there are times when it's not. Especially if you're doing something for the first time and feel like you're in over your head! Today we'll a look at command line parameters, checkpointing and recovery, and a few miscellaneous items.

SQL supports a range of command line parameters, some documented and some not. There are three that are always used to set the locations of master.mdf, master.ldf, and the errorlog. Strangely (to me anyway) these switches are saved in the registry rather than as part of the service configuration. Note the second image where I'm passing in the "-m" parameter which tells SQL to start in SQL user mode. Note that I did this as a test only, this is not a recommended configuration!

Figure 1

Figure 2

For those of you interested, the startup parameters configured in Enterprise Manager are stored at HKEY Local Machine, Software, Microsoft, MSSQLServer, MSSQLServer, Parameters. Portion of that shown below.

Figure 3

If nothing goes wrong you can go a long long time without ever needed to change the parameters.

During a standard shutdown SQL will issue a checkpoint in each database. Let's define a checkpoint as having written all dirty pages at that point to disk, which means guaranteed recovery to that point. Running the checkpoint takes some amount of time, based on the recovery interval you've set, how many dirty pages exist, how busy the disks are, etc. When you restart the service, if

you look in the log you'll sometimes see comments about transactions being rolled forward or rolled back. These are normal entries. The key point to remember is that you'll always wind up in a transactionally consistent state. In other words, don't worry!

Figure 4

What you're really looking for are a couple key entries. The first indicates that recovery is complete - sample entry from the log shown in the first image below. It's also a very good idea to check that SQL is listening on all ports correctly. The best way to know what these are is to look in the log from the last time SQL was started. Sample of ports in the second image.

Figure 5

Figure 6

You also want to check for any database that is suspect. Suspect basically means something is wrong. One way to cause that to happen is to move one of the data or log files which is what I did to create the error for the image below. The line in the error log is only mildly helpful when you see it, double click to get the full scoop.

Figure 7

Figure 8

You can also check by refreshing the databases node in Enterprise Manager, looking for any databases that are grayed out and have (Suspect) appended to the name. It's worth a few minutes to learn more about this before it happens. My friend Brian has written a great article about fixing suspect databases.

Figure 9

Now for the miscellaneous items!

One common question I get is 'how do you know when the service is stopped'? The cleanest way I've seen is to open Task Manager and wait for the sqlservr.exe entry to disappear. On systems with a lot of memory this can take several minutes.

Figure 10

Going back to the earlier articles, one point I'd like clarify is that SQL Agent also runs as a service and can also be managed via Enterprise Manager, the Services applet, net start/stop, and the SQL Service Manager. There is no 'shutdown' command in TSQL for the agent, but you can do it indirectly by using xp_cmdshell, shown below stopping the agent.

Figure 11

I also missed at least one option for starting & stopping services, the service control manager (SCM). Text below is direct from BOL. The biggest advantage this offers is that you can exercise greater control over the configuration than you can using net start/stop.

I thought three articles would be enough, but I can see where it will take at least one more. Next time we'll look at a couple tips that will save you some headaches in production.