Articles Posted in the " SQL Agent " Category

  • Reading All Available SQL Error logs

    Reading All Available SQL Error logs

    There are many blog posts available online about reading SQL Server Error Log or SQL Server Agent Error Log. What I needed was to search for a string (or string within a string) in all available error logs. Save the results in a table. That way I can find the frequency and pattern of the occurrence. I am also aware that PowerShell commands can read error logs and there are few posts available online for doing that. I did not find one with everything I needed. I wrote 2 versions of this, one as a stored procedure that you can …


  • 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 …