Check SQL Server Agent status on all SQL Servers

There are two easy checks to verify if the SQL Agent service is running:

SQL-Agent-Status-Code

SQL-Agent-Status-Results

#1) By using the build-in DM-view:

SELECT *
FROM master.sys.dm_server_services

(last_startup_time is always NULL so it seems, Microsoft is working on it? more info)

#2) By querying it (script by Colin Stasiuk):

IF EXISTS (
   SELECT 1
   FROM master.dbo.sysprocesses
   WHERE program_name = N'SQLAgent - Generic Refresher'
   )
      BEGIN
         SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
      END

ELSE
   BEGIN
      SELECT @@SERVERNAME AS 'InstanceName', 0 AS 'SQLServerAgentRunning'
   END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s