Automatically Stopping and Restarting SQL Server

In the old days, it was
common practice to have to stop, and restart SQL Server frequently for one
reason or another. However, lately with SQL Server 2000 and Windows 2000 we can
run for months with never having to stop and restart the SQL Server services,
or rebooting our machines. Occasionally we do run into problems that do
require us to stop and restart SQL Server.

The kinds of problems that
require SQL Server services to be restarted normally only affect a small number
of applications, and usually don’t cause a work stoppage. Therefore, in our
shop we have a policy that restricts us from restarting SQL Server in the
middle of the day, unless there is a work stoppage of some kind. So, when
there is a problem that requires SQL server to be restarted, the DBA’s either
need to stay late to recycle SQL Server during off hours, or they connect
remotely from home in the middle of the night to recycle SQL Server. If you
are like me, I hate working late, or having to get up in the middle of the
night to stop and restart SQL Server, I kind of like my sleep. Good thing
there are alternatives that don’t require me to manually restart SQL Server
during off hours. This article will explore a number of different approaches to
automate the task of stopping and restarting SQL Server.

NET Command

The first method to start
and stop SQL Server services is to use the NET command. To stop a SQL Server service
using the NET command you would issue the following command:

net stop [servicename}

Then to start a SQL Server
service you would issue the following command:

net start [servicename]

Where [servicename] is the
name of the SQL server service you want to stop or restart.

Since my goal is to build an
automated method to restart SQL Server I need to put a process in place that
will run a series of NET commands to first stop the SQL Server services, and
then another series of commands that will start my services. To do this I will
need to put together a batch file that contains a series of NET commands. I
built a batch file named “c:DBAbatchRestartSQLServer.bat that contains the
following commands:


net stop sqlserveragent
net stop mssqlserver
net start mssqlserver
net start sqlserveragent

This series of commands
first stops the default instances of SQL Server Agent and SQL Server, and then
starts these two services. To execute this command I just need to enter “c:DBAbatchRestartSQLServer.bat”
at the command prompt. Running this bat file from the command prompt doesn’t
achieve my goal of automating the process of restarting SQL Server. However, later
on in this article I’ll explain how you can schedule this bat file to be automatically
executed.

SC Executable

One of the drawbacks of the
NET command is you cannot use it to start your SQL Server services on a remote
machine. An alternative to using the NET command to stop and restart your SQL
Server services is the SC executable. The SC executable can be used to start
the SQL Server services locally or on a remote machine. The SC executable is a
command line program that can be used to communicate with the NT Service
Controller and services. This exe has lots of functionality. Not only can you
start and stop your SQL Server services, but you can also use this exe to
create and manage your services. In the scope of this article, I will only
cover how you can use this exe to start and stop your SQL Server services. Here
is the syntax for calling the sc executable:

sc [ServerName] Command ServiceName [ServiceArguments]


ServerName

Specifies
the name of the server where the service will be started or stopped. Enter the
ServerName in UNC format ("\myserver"). ServerName is not needed if
you are stopping/starting a local service.
Command

The command is either “start” or “stop”.
ServiceName

The
service name to be started or stopped.
ServiceArguments

Specifies
service arguments to pass to the service being started. Note: this option is
not used when the command “stop” is issued.

Just like with the NET
command solution, I will need to create a small batch file that contain the
commands to stop SQL Server services and restart them. However, since the SC
executable just sends stop requests and doesn’t wait for the services to stop
or start before processing the next command, I need to do a little more in my
bat file. The additional logic I need is to put in some method of waiting
between each SC command I issue. This extra wait time will allow the service
to stop or start before proceeding to the next SC command. Below is an example
of a batch script I can use to remotely stop and restart the MSSQLSERVER and SQLServerAgent
services on a remote computer named “RemoteMachine1”:


sc.exe RemoteMachine1 stop sqlserveragent
@ECHO OFF
SET COUNT=1
:LOOP1
IF %COUNT% == 5000 GOTO END1
SET /A COUNT=COUNT+1
GOTO LOOP1
:END1
@ECHO ON
sc.exe RemoteMachine1 stop mssqlserver
@ECHO OFF
SET COUNT=1
:LOOP2
IF %COUNT% == 5000 GOTO END2
SET /A COUNT=COUNT+1
GOTO LOOP2
:END2
@ECHO ON
sc.exe RemoteMachine1 start mssqlserver
@ECHO OFF
SET COUNT=1
:LOOP3
IF %COUNT% == 5000 GOTO END3
SET /A COUNT=COUNT+1
GOTO LOOP3
:END3
@ECHO ON
sc.exe RemoteMachine1 start sqlserveragent

Note that I placed a small
loop between each SC command. This loop is just one way to implement a delay.
Depending on how fast your machine processes through the loop you might need to
adjust the number of times each loop is executed, to make sure the service is stopped
or started before the batch file issues the next SC command.

Scheduling the Process to
Stop and Restart SQL Server

Now to complete my requirement
to build an automatic process to stop and restart SQL Server, I need to schedule
my batch script, which stops and restarts SQL Server, to be executed at a
specific time. Since I am restarting SQL Server, I cannot use SQL Server Agent
to schedule the running on my bat file. Although you could use SQL Server
agent on a different machine than the one you are recycling SQL Server, if you
use the SC exe to restart your SQL Server Services.

Since I want to build my
recycle process to be scheduled on the machine that I am recycling SQL Server
on, I have a couple of different options.

The first way is to use the
AT command. The AT command can be used to schedule my bat file locally or on a
remote computer. Below is an example that will schedule my batch file to occur
every day at 11:30 PM on the local machine:

at 23:30 /every:M,T,W,Th,F,S,Su c:DBAbatchRestartSQLServer.bat

Now if I only want to stop
and restart SQL Server once a week on Sunday morning at 3:00 AM I would use
the following AT command:

at 03:30 /every:Su c:DBAbatchRestartSQLServer.bat

For additional AT command
syntax please refer to Windows Help.

Alternatively, I could use
the Windows Scheduled Tasks GUID to schedule jobs. To open the task scheduler
click on “Start”, then click “All Programs”, point to “Accessories”, point to “System
Tools”, and then click on “Scheduled Tasks”. Once the task scheduler is open, you
need to double click on the “Add Scheduled Task” item. This will bring up the “Scheduled
Task Wizard”. All you need to do then is walk through the wizard to select the
batch file to run, and the set up the desired schedule for executing that batch
file.

Conclusion

I’ve shown you two methods
that you could be used to build a process to automatically stop and restart SQL
Server. Normally one does not need to routinely restart SQL Server. However,
should you run into a problem that requires you to restart SQL Server
periodically then hopefully one of these methods will work for you.

»


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles