Sql-server – SQL Agent not Running

sql serversql-server-agent

Please Help. I have been trying this for the past 3 days.

I need to send an alert mail when the SQL Server Agent is not running. That is, there can be instances where SQL Server is is running, but Agent is not running. I need to set up the same as startup procedure to check whether the agent is disabled or not when the service starts.I had already made the procedure and set up the same as start up procedure. I had also made a job based on the same, but had not scheduled.

Thanks in advance. Please help

Best Answer

Instead of having SQL Server monitor one of its subprocesses, why don't you have a script at the operating system level - WMI, PowerShell, what have you - periodically poll the services to make sure Agent is running? This seems a lot more foolproof than a startup procedure (which only runs when the SQL Server service starts, and potentially will finish before Agent starts, so it's not going to catch many scenarios - like when Agent suddenly fails or is forcibly shut down without touching the SQL Server service).

A job is absolutely not the way to do this. How can a job tell you that SQL Server Agent is not running? Catch-22. That's like your car driving itself to go get gas when it is out of gas.

Also, creating a watchdog for the Agent service seems way too reactive to me. Have you looked into why the service isn't starting or is getting shut down? The Windows event logs should have information about this.

Related Solutions

Sql-server – SQL Server Agent

You need access to SQL Server Agent in order to do what you need to do. This is done through msdb permissions: SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole.

You can create a job to adhere to your schedule, and then the job will execute whatever task it is that you need done.

As for the performance impact on the server, SQL Server Agent itself is negligable but whatever the job does will definitely be a consideration on when it is run, depending on how expensive the job is. To give a better estimate on server impact, please tell us exactly what you need the job to do.

Sql-server – Check if SQL Agent job is being executed manually

Using Kenneth's code, and a few other bits and pieces that I pulled together, I came up with the following code which I think should do the job. If anybody sees any potential problem with this I'd be interested in knowing!

-- Check if this server is the preferred replica for backups.
    If sys.fn_hadr_backup_is_preferred_replica( @DatabaseName ) <> 1
    BEGIN

        -- Check whether the script is running from SQL Agent.  If it is not then raise an error so that the user knows that the backup hasn't completed.
        -- (If the job is run from the agent then there should be an equivalent job running on the preferred replica, so no need to alert).
        declare @AgentAccount varchar(100)
        set @AgentAccount = (select cast(value_data as varchar(100)) as [AccountName]
                                FROM sys.dm_server_registry
                                WHERE registry_key LIKE '%SQLSERVERAGENT%' --For named instance change to SQLAgent$<instanceName>
                                AND value_name = 'ObjectName'
                            )


        if @AgentAccount = SYSTEM_USER
        begin

            declare @jobSource tinyint
            SELECT  @jobSource = ja.run_requested_source
                    --WHEN 1 THEN 'SOURCE_SCHEDULER'
                    --WHEN 2 THEN 'SOURCE_ALERTER'
                    --WHEN 3 THEN 'SOURCE_BOOT'
                    --WHEN 4 THEN 'SOURCE_USER'  -- This one is manual
                    --WHEN 6 THEN 'SOURCE_ON_IDLE_SCHEDULE'
                    --ELSE 'UNKNOWN' END
            FROM msdb.dbo.sysjobactivity ja
            WHERE ja.session_id = @@SPID
            AND start_execution_date is not null
            AND stop_execution_date is null;

            if @jobSource = 4   -- Job was run manually.
            begin
                RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
                return 1
            end
            else
            begin
                print 'No backup produced as this server is not the preferred replica for backups.'
                return 0
            end
        end
        else
        begin
            RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
            return 1
        end
    END

Best Answer

Related Solutions

Sql-server – SQL Server Agent

Sql-server – Check if SQL Agent job is being executed manually

Related Question