Restarting Microsoft SQL Server 2000 via Scripts

I have noticed that SQL server sucks up most of our Servers RAM by the end of a business day. I have since implemented a simple batch file to run twice a day, once at 06:00 and the second time at 18:00 before our nightly backup.

The basis of this script is the NET command. The Net command has a plethora of sub commands such as Stop, Start, user, use, view, accounts, and so on. An overview of each command can be found here. But we will be using only two of these commands, NET STOP and NET START.

Since the SQL Server Agent (SQLSERVERAGENT) is dependent on SQL Server (MSSQLSERVER) we need to stop it before we do anything with SQL Server. The command to stop a service is NET STOP and in this case we want the Service Name and not the display name. To find the Service name you can right click on the Service and go to properties. On the General Tab the first field is the Service name. We want SQLSERVERAGENT so our command will be NET STOP SQLSERVERAGENT. After the server agent is stopped we can stop the server itself with NET STOP MSSQLSERVER. Then we need to do the reverse of this and run NET START MSSQLSERVER followed by NET START SQLSERVERAGENT.

Our batch file is going to look like this then:

NET STOP SQLSERVERAGENT

NET STOP MSSQLSERVER

NET START MSSQLSERVER

NET START SQLSERVERAGENT

Another command we could use from a remote workstation such as our Administrator’s workstation is the SC command. We will still need to know the service name to use the SC command but there is only one difference. That difference is now we need to specify the server/workstation that we want to control the services of. The server name will be in the form of \\SERVERNAME.

This time our script would look like

SC \\SERVERNAME STOP SQLSERVERAGENT

SC \\SERVERNAME STOP MSSQLSERVER

SC \\SERVERNAME START MSSQLSERVER

SC \\SERVERNAME START SQLSERVERAGENT

Again the SC command has a lot more it can do but that is all the time we have to cover it now. If you want to learn more about the SC command you can visit this web site.