Automatically Stopping and Restarting SQL Server
November 21, 2006
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 DBAs 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 dont 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.
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:\DBA\batch\RestartSQLServer.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:\DBA\batch\RestartSQLServer.bat at the command prompt. Running this bat file from the command prompt doesnt achieve my goal of automating the process of restarting SQL Server. However, later on in this article Ill explain how you can schedule this bat file to be automatically executed.
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]
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 doesnt 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:\DBA\batch\RestartSQLServer.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:\DBA\batch\RestartSQLServer.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.
Ive 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.