Automating a Stop and Restart of MSSQLServer 1

[JohnBates]

hi experts,

This is SQL Server 2000 SP3a.

I've found that you can gain most of the benefit that a reboot gives, by just stopping and starting the SQL Server and SQLServer Agent. I've been doing this via Enterprise Manager and it helps performance because it trims the size of tempdb and breaks all the client connections.
(I do this rarely and only when the clients are aware that they will lose their connections, etc.)

I want to create a *SQL Server Job* to automate and schedule a stop and start of MSSQLServer and SQLServerAgent.

The net stop MSSQLServer only works from a command line, right?

I can use the SHUTDOWN command in a stored proc or Query Analyzer but what is the command to START MSSQLServer ?

Any ideas ?

Thanks, John

 

[mihaildamjan]

Create a small batch file with this two lines only.

net start mssqlserver
net stop mssqlserver



Create a job to run this batch.

This is how I do it


Mihail

 

[mrdenny]

That will leave the SQL Agent shutdown.

You'll want it to have 4 lines.

net stop SQLSERVERAGENT
net stop MSSQLSERVER
net start MSSQLSERVER
net start SQLSERVERAGENT

This shouldn't work if run as a job. Once the agent stops the batch file should be stopped as well. I'd recommend running it as a scheduled task instead.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[JohnBates]

This shouldn't work if run as a job."

good point mrdenny. If the Agent is stopped, it cant execute the remaining lines.

John