SQL SERVER – Fix – Agent Starting Error 15281 – SQL Server blocked access to procedure ‘dbo.sp_get_sqlagent_properties’ of component ‘Agent XPs’ because this component is turned off as part of the security configuration for this server

SQL Server Agent fails to start because of the error 15281 is a very common error.

When you start to restart SQL Agent sometimes it will give following error.

SQL Server blocked access to procedure ‘dbo.sp_get_sqlagent_properties’ of component ‘Agent XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, search for ‘Agent XPs’ in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)

To resolve this error, following script has to be executed on the server.

sp_configure 'show advanced options', 1;
sp_configure 'Agent XPs', 1;

When you run above script, it will give a very similar output as following on the screen.

Now, if you try to restart SQL Agent it will just work fine.

That’s it! Sometimes there is a simpler solution to complicated error.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Reason for SQL Server Agent Starting Before SQL Server Engine Service

Nakul, a dedicated member of the Gandhinagar SQL Server User Group, recently emailed me with a very interesting, but quick question. He asked me why the SQL Server Agent starts before SQL Server Engine does? He made the very valid point that as the SQL Server Engine is the core service, it should start first, and there is little point to running the SQL Server Agent without it.

Off the top of my head, I can offer the following quick reasons for this sequence:

  • The SQL Server Engine does not only run jobs for SQL Server Engine itself. It also runs jobs for other core services like the SQL Server Analysis Service, Integration Service, Reporting Service etc.;
  • The SQL Server Agent can run almost any kind of task that an Operating system can run. For example invoking any program or running shell scripts;
  • The SQL Server Agent also starts jobs which are scheduled to run the second the SQL Server Engine starts, and for this reason it is needed; and
  • Replication, mirroring and a few other tasks also depend on Agent Jobs.

These are the reasons that I have come up with so far. Can you think of any more? Let us have your views.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Find Next Running Time of Scheduled Job Using T-SQL

I often receive good question on blog, however, I do not always receive good answer for the questions. Imran Mohammed is one SQL Expert who spend his time helping SQL Community on this blog. I always read his answers as there is something to learn every time. Recently someone asked on blog about Finding next run time for Schedule Job using T-SQL. Imran came up with nice script. I have modified it a bit to adjust needs.

USE msdb
;WITH CTE AS (SELECT schedule_id, job_id, RIGHT('0'+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date
FROM sysjobschedules)
SELECT A.name Job_Name,
'Will be running today at '+
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) END),1,2)
':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time),3,2)
':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2) 'Scheduled At'
FROM sysjobs A ,CTE B
WHERE A.job_id = B.job_id
AND SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 5,2) +'/'+
SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 7,2) +'/'+
CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) END),1,2)
':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),3,2)
':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)) >

Reference : Pinal Dave (http://blog.SQLAuthority.com), Imran Mohammed

SQL SERVER – Retrieve Information of SQL Server Agent Jobs

sp_help_job returns information about jobs that are used by SQLServerAgent service to perform automated activities in SQL Server. When executed sp_help_job procedure with no parameters to return the information for all of the jobs currently defined in the msdb database.


Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

SQL SERVER – What is New in SQL Server Agent for Microsoft SQL Server 2005

I came across this interesting and detailed article ‘What’s New in SQL Server Agent for Microsoft SQL Server 2005‘ on Microsoft TechNet.

This article describes Security Improvements, New Roles in the msdb Database, Multiple Proxy Accounts, Performance Improvements, Performance Counters, New SQL Server Agent Subsystems, Shared Schedules, WMI Event Alerts, SQL Server Agent Sessions, Database Mail Support, Stored Procedure Changes in depth.

Reference : Pinal Dave (http://blog.SQLAuthority.com)