Articles Posted in the " SQL Agent " Category

  • Warning: Non-existent step referenced While Creating SQL Agent Job

    Warning: Non-existent step referenced While Creating SQL Agent Job

    Today there was a question submitted in dba.stackexchange.com with heading “Suppressing non-ANSI warnings in SQLCMD script”. Details here. In summary, this happens when you use a script to create a SQL Agent job and one of the job step has conditions (under advanced properties) to go to certain steps in case of success or failure. Exact text of the warning:

    Warning: Non-existent step referenced by @on_success_step_id.
    Warning: Non-existent step referenced by @on_fail_step_id.

    I searched for the keywords “Warning: Non-existent step referenced” and looked at the top 10 results. Most of them talked about properly setting advance property of each …


  • Schedule SQL Agent Job With Availability Group

    Schedule SQL Agent Job With Availability Group

    There was a tweet came last night from Lohit about “need guidance on how to enable a set of jobs on secondary AG as soon as failover happen from pri to sec AG”.  I have implemented a solution for most (I am using “most”  literally because I have not done this for all jobs) of the agent jobs so jobs will run always on primary node.  For example, I want to run DBCC CHECKDB, recycle error log on primary and all readable secondaries.  That way for the jobs that I want to run on primary node only  I do not …


  • SQL Agent Jobs With Notification Enabled

    SQL Agent Jobs With Notification Enabled

    DBA team that I worked with manage 170+ SQL Servers. We do not enable notification on any SQL Agent job. We centrally monitor all jobs and send notification based on the tier (Business critical). Last night on call DBA got paged for a job failed in server we are doing proof of concept. By looking at the configuration in the central sever it should not send pager notification. Then I realized it was set in the job itself. This server has over 200 SQL Agent jobs. First I need to find out which jobs has notification enabled, what kind of …


  • Manage SQL Agent Job History

    Recently in one of our server row count in MSDB.DBO.SysJobHistory table exceeded 100 Million and looking at job history was slow via SSMS. It was impossible via the GUI. Because we had about 50 jobs that were running frequently (called from a listener mostly once in every 2 to 3 minutes). I was curious if we can manage the job history for individual job or job category. I also wanted different retention for success vs failure of the job. Meaning for the jobs that run every minute few days of history for success run is enough whereas for failed ones …