Make your own free website on Tripod.com

How to restart SQL Server service at regular intervals?
Last updated: May 7th '02 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New



 
NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line
See also:  
Database coding conventions SQL Server interview questions
Evaluation of federated database servers SQL Server FAQ on programming, administration, replication and tools
SQL Server security best practices SQL Server administration best practices
Related books:
Inside SQL Server 2000Microsoft SQL Server 2000 UnleashedSQL Server Backup and Recovery: Tools and Techniques

How to restart SQL Server service at regular intervals?

DBAs used to restart SQL Server once in a while, in those good old Windows NT 4.0 and SQL Server 6.5 days. With Windows 2000, the operating system is more stable and reliable, as is the case with SQL Server 7.0 or SQL Server 2000. So, there's no real need to restart the SQL Server service or reboot the server, unless the server is poorly configured or sized. I'm able to run my production servers for months, without rebooting them, there by increasing the server uptimes dramatically.

But every now and then, for whatever reason, people keep asking me, how to restart the SQL Server service periodically (on a scheduled basis)? There are several different ways, but here's a simple one.

Create a batch file called RestartSQL.BAT with the following commands in it, and save it on the C:\ drive:

@ECHO OFF
NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER
NET START MSSQLSERVER
NET START SQLSERVERAGENT

If your requirement is to restart SQL Server every Sunday mid-night, run the following command at the command prompt of the SQL Server computer:

AT 00:00 /EVERY:Su C:\RestartSQL.BAT

This will schedule the batch file RestartSQL.BAT to run every Sunday night at 12:00 AM.

While running the above command, make sure you are logged in as an administrator on the SQL Server computer. Also make sure, the "Task Scheduler" service of Windows 2000 is running and it's startup type is set to Automatic. The above command assumes that RestartSQL.BAT is in C:\. If not, provide the right path there.

If you'd like to schedule the server cycling at a different time, look at the documentation of AT command in Windows 2000 help.

As you can see, there are no stored procedures and jobs involved in this server restart process. It all happens outside SQL Server. At the time of restarting the server, any existing user connections will be disconnected and their incomplete work will be rolled back. So, it is important to pick a time that has minimal or no impact on your SQL Server users.

The above batch file assumes that you are running the default instance of SQL Server. With SQL Server 2000, you can have multiple instances of SQL Server on the same computer and you need to refer to the non-default instances using an instance name. In that case, make sure you change the service name in the NET START and NET STOP commands as needed. You can find out the service names from the Services Applet of Windows 2000 Administrative Tools or Windows NT Control Panel.