Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 21, 2006

Automatically Stopping and Restarting SQL Server

By Gregory A. Larsen

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:\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 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:\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.

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date