Restart Sql Server Agent
-
Does anyone know how to restart sql server 2005 agent using TSQL? I know how to restart it manually but I would like to create a job that will do this automatically once a week...
Any suggestions would be helpful.
Thanks in advance!
-
xp_cmdshell and the net stop command. Although that might give you a problem with starting it if run directly in an agent. I once upon a time had a server that had to be restarted when a certain error happened. What I did was create a batch file that had
Net Stop xxxx
Wait 1 minute
Net Start xxxx
Where xxxx is the name of the service. I would then make the SQL Agent execute the batch file.
-
I think thats probably my best option! Thanks for the help.
-
We've been using the following script code for SQL 2000 for a few years now:
-- Check to see if SQL Server Agent is running
-- Then start it if it is not
DECLARE @SQLAgentErr int, @SQLAgentMsg varchar(500)
DECLARE @SQLAgentStatus varchar(50)
CREATE TABLE #tmpSQLAgent ([Current Service State] varchar(50))
INSERT INTO #tmpSQLAgent
EXEC master..xp_servicecontrol 'querystate', 'sqlserveragent'
SET @SQLAgentStatus = (SELECT [Current Service State] FROM #tmpSQLAgent)
DROP TABLE #tmpSQLAgent
IF @SQLAgentStatus = 'Stopped.'
BEGIN
-- Starts the SQL Server Agent service (generates error if already running)
EXEC master.dbo.xp_servicecontrol 'START', 'SQLServerAgent'
SET @SQLAgentErr = @@Error
IF @SQLAgentErr <> 0
BEGIN
SET @SQLAgentMsg = 'Error occured while starting SQL Server Agent. Error code: ' + STR(@SQLAgentErr)
RAISERROR (@SQLAgentMsg, 18, 1) WITH LOG
RETURN
END
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply