How to: Determine SQL Server Agent Start Up Type

This post will teach you how to retrieve the SQL Server Agent start up type from windows registry.

DECLARE @auto_start		INT
DECLARE @key			NVARCHAR(200)

SELECT @key = N'SYSTEM\CurrentControlSet\Services\'

IF (SERVERPROPERTY('INSTANCENAME') IS NOT NULL)
	SELECT @key = @key + N'SQLAgent$' + CONVERT (sysname, SERVERPROPERTY('INSTANCENAME'))
ELSE
	SELECT @key = @key + N'SQLServerAgent'
	
-- Get SQL Server start up type from windows registry
EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                              @key,
                              N'Start',
                              @auto_start OUTPUT,
                              N'no_output'

-- Show the SQL Server Agent start up type                     
SELECT (CASE @auto_start
        WHEN 2 THEN 1	-- 2 means auto-start
        WHEN 3 THEN 2	-- 3 means manual
        ELSE 0		-- Safety net
      END) AS [SQLAgent_StartUp_Type]