Skip to content

Instantly share code, notes, and snippets.

@kmoormann
Created September 26, 2012 14:46
Show Gist options
  • Save kmoormann/3788471 to your computer and use it in GitHub Desktop.
Save kmoormann/3788471 to your computer and use it in GitHub Desktop.
SQL Server Agent Starting and Stopping
--######## FOR STARTING WHEN STOPPED OR STOPPING ####################
--IF YOU NEED TO STOP THE SERVER FIRST TO TRUN THE LINE BELOW
--EXEC xp_servicecontrol N'STOP',N'SQLServerAGENT';
USE master;
GO
CREATE TABLE #SQLAgentStatus
(
Status varchar(50),
Timestamp datetime default (getdate())
);
GO
INSERT #SQLAgentStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'SQLServerAGENT';
GO
DECLARE @LatestStatus VARCHAR(50)
SET @LatestStatus = (SELECT Status FROM #SqlAgentStatus WHERE timestamp = (SELECT MAX(timestamp) FROM #SqlAgentStatus))
IF ('Stopped.' = @LatestStatus OR 'Stopping...' = @LatestStatus)
BEGIN
-- START SQL Server Agent
EXEC xp_servicecontrol N'START',N'SQLServerAGENT';
INSERT #SQLAgentStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'SQLServerAGENT';
END
GO
EXEC xp_servicecontrol N'QUERYSTATE',N'SQLServerAGENT'
GO
SELECT * FROM #SQLAgentStatus
GO
DROP TABLE #SQLAgentStatus
GO
--######## FOR STOPPING WHEN STARTED OR STARTING ####################
--IF YOU NEED TO START THE SERVER FIRST TO TRUN THE LINE BELOW
--EXEC xp_servicecontrol N'START',N'SQLServerAGENT';
USE master;
GO
CREATE TABLE #SQLAgentStatus
(
Status varchar(50),
Timestamp datetime default (getdate())
);
GO
INSERT #SQLAgentStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'SQLServerAGENT';
GO
DECLARE @LatestStatus VARCHAR(50)
SET @LatestStatus = (SELECT Status FROM #SqlAgentStatus WHERE timestamp = (SELECT MAX(timestamp) FROM #SqlAgentStatus))
IF ('Running.' = @LatestStatus OR 'Starting...' = @LatestStatus)
BEGIN
-- START SQL Server Agent
EXEC xp_servicecontrol N'STOP',N'SQLServerAGENT';
INSERT #SQLAgentStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'SQLServerAGENT';
END
GO
DROP TABLE #SQLAgentStatus
GO
@aristotle0x01
Copy link

great scripts. i used them together, first stop then start, since those two actions got executed almost in the same time. so the agent may still be stopping, the start would start it, thus causing "1056, 'An instance of the service is already running.'". so it would be nice to mention a possible wait between the two:
WAITFOR DELAY '00:00:05'
GO

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment