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
| Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line |
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.