MSSQLTips: Tip #43 – SQL Server Agent Jobs Monitoring Script

Problems with SQL Server Agent Jobs can lead to missing backups, missing notifications about a failed job and sometimes missing mission critical tasks that are run as scheduled jobs.

Some of the common SQL Server Agent Job problems are described in other MSSQLTips tips. In this tip we have provided a single script that will check the jobs for a number of issues including:

  • Disabled SQL Server Agent Jobs
  • Failed SQL Server Agent Jobs or Job Steps
  • SQL Server Agent Jobs without a Description
  • SQL Server Agent Jobs without Schedules
  • Disabled SQL Server Agent Job Schedules
  • SQL Server Agent Jobs without Categories
  • SQL Server Agent Jobs Owned by a DBA or Other Privileged User
  • SQL Server Agent Jobs with no Owner or Owner Login is Denied “CONNECT TO SQL”
  • No SQL Server Agent Notifications on Job Failure or Completion
  • SQL Server Agent Operators Issues
  • SQL Server Agent Missing Job History
  • Potential SQL Server Agent Job Step Issue or Step Won’t Execute

Please read the latest MSSQLTips post: “SQL Server Agent Jobs Monitoring Script“.

Check out all of my tips here:

MSSQL Tips: Tip #32 – Resolving SQL Server Master Data Services (MDS) Patching Errors

When you patch a server hosting the MDS Web Application with an SQL Server update and if you have not completed the upgrade sequence users may get this error in the MDS Web Application on client side.

Check out details in the following MSSQL Tip: “Resolving SQL Server Master Data Services (MDS) Patching Errors“.

Check out all of my tips here:

ORA-01013 error troubleshooting

A user reported an application error ORA-01013. The error message was: “User requested cancel of current operation”. The user retried it several times, but had the same result.

The application uses Oracle database and there were no changes to the database since the last time when user was able to run this process.

The user was able to run the process in the test environment without any issues as well. I ran the same query from DBA query tool and the query completed successfully.
So, my first thought was that there was something wrong with application server itself.
After 10 minutes of checking similar issues (ORA-01013 error) online and on Oracle support site I found no explanation of why would this happen.

I started analyzing the query’s execution for both – for DBA’s execution and for the application.

So, when the query was running from the application it was running just for 15 seconds and then it was just cancelled (with error ORA-01013). It was cancelled every time after exact 15 seconds. When I ran the query myself it was running for about 2 minutes. The interesting thing was that SQL IDs for these two executions were different.

My next step was to find the query in cash plan:

select address, hash_value from v$sqlarea
where sql_text like ‘SELECT COUNT(*)%HERE IS THE PROBLEM SQL…%’ and sql_id<>’6munh028ah2bm’;

Where “6munh028ah2bm” was SQL ID of my execution.

Then I removed the “bad” plan from the cash:

exec sys.dbms_shared_pool.purge(‘00000004E2C255D8,1992735705′,’c’);

We have not tried to test it right after this, so I am not sure if this is enough to fix the problem. The next steps were to gather statistics for the tables related to the query.

This fixed the problem.

I thought I need to share this as I was not able to find anything helpful on the web during my troubleshooting. Hopefully next time it will safe time to somebody.