Script to restart SQL Services?

[niallo32]

I have a SQL Server 2000 database running on a Windows 2000 Quad processor, Intel platform.

I run an ERP on this server and occasionaly, I get errors such as 'Threaded business functions processing. Please wait then try again'. The ERP runs dog slow, even though the Server processor would only be at 1 or 2 percent usage.

As soon as I restart the SQL Server database, the ERP runs at high speed again until the next time it happens.

There are no errors in the SQL Server log.

I would like to restart the SQL DB each night at a scheduled time. Does anyone know of a script that I could write for this purpose?

Thanks

 

[gmmastros]

Look up net start and net stop

Those are DOS commands to stop and start a service. You can put them in a .bat file and use windows scheduler to run them every night.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[niallo32]

Thanks for that, I can only see one Windows Service relating to SQL Server though - SQLServer Agent.

There are four services in SQL Server Ent Manager..

 

[gmmastros]

Click start -> run
type services.msc
click OK.

You will see a list of services for your computer. SQL Server has several items in this list.

SQLSERVERAGENT is one of them.
You'll also see MSSQLSERVER

If you have named instances, then the service will be named:

MSSQLSERVER$[!]InstanceName[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[niallo32]

OK, we have MSSQLServer & SQLSERVERAGENT - I dont see any others. Should there be any others?

Thanks

 

[gmmastros]

No. That's usually all unless you have named instances. Since you don't have named instances, then that's all you'll see.

If I recall correctly, you should stop the SQLSERVERAGENT service first, then stop the MSSQLSERVER service, then start MSSQLSERVER, then start SQLSERVERAGENT.

Net Stop SQLSERVERAGENT
Net Stop MSSQLSERVER
Net Start MSSQLSERVER
Net Start SQLSERVERAGENT



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[niallo32]

Will try that - thanks again

 

[mutley1]

I use SC as opposed to net start and net stop - might just be our network, but it seems to be a lot quicker over 50 servers (3 minutes as opposed to 20).

SC Syntax

To stop

sc \\DCAS30MBWGS01 stop MSSQLSERVER > d:\apps\SQLServices\SQLServices.txt

To start

sc \\DCAS30MBWGS01 start MSSQLSERVER >> d:\apps\SQLServices\SQLServices.txt

Notice the append (>>). What I generally do is have 2 batch files - 1 to stop everything and 1 to start everything and schedule them about 30 mins apart (just because we have a lot of servers so it gives them time to all stop services so there are no conflicts when restarting.

I have all the stops and stars held in a DTS package that writes to text files, imports them to a temp table, reports on them and does a ping on the servers as well to see if it is up and running. Just as a log file, i use the following for each server in the batch file:

REM *******************
echo *** \\SERVER1 > d:\apps\logs\BLAH.txt
echo MSSQLSERVER >> d:\apps\logs\BLAH.txt
sc \\SERVER1 stop MSSQLSERVER >> d:\apps\logs\BLAH.txt

As George has pointed out, the services would be:

MSSQLSERVER
SQLSERVERAGENT
MSSQL$INSTANCENAME
MSSQLSERVEROLAPSERVICE (if you are using it).

Rgds,

M.