Category Archives: Reports

SQLAgent Job related reports and out of box real time job monitoring tool !

Posted on by

Lets start New Year with a very simple yet useful topic.

I am a big fan of out of box reporting capabilities of SSMS. Some of the reports are really useful.

One of the reports which I really like is the Top Jobs report. This one can be pulled by traversing through –

Job Monitoring 1

The report comes in really neat with a graph and 3 major information related to SQL agent jobs.

Job Monitoring 2

The ones which I’m really interested all the time are 20 Most Frequently Failing Jobs and the 20 Slowest jobs.

This information is very important for me as I can trend the job information over a period of time(Even though its for a weeks time) for the most business critical servers.

Job Monitoring 3

The reports provide lot of useful information such as Avg Run Duration,Total Executions etc which will help you to observe how jobs are behaving under normal circumstances and can even use this as a baseline. Pretty neat stuff out there.

We also have the Job steps execution history report which will provide us with information regarding various steps within the job. Again a useful one if you are having lot of steps which are configured for the same job.

Now lets look at a scenario  – Your server is hitting 100% CPU and you believe that a heavy SQL job running at that point of time might be a reason for this spike.

How do you check which SQL job is running at that very point of time? Well, there are multiple ways to accomplish this. A TSQL code which joins couple of job related MSDB tables can quickly pull this information for you.

What if there is a light weight tool sitting right inside SSMS which can pull that information for you, lets look at that right away

Job Activity Monitor option will help you to monitor SQL jobs real-time.

Job Monitoring 4

If you need to know about jobs which are executing right now, then you will need to use the filter option and apply the required status.

Job Monitoring 5

Job Monitoring 6

Conclusion

There are multiple ways to monitor your jobs and its statistics, however the above mentioned methods are simple and efficient to get a quick view of SQLAgent jobs and related information.

SSMS Report Error – Index(zero based) must be greater than or equal to zero and less than the size of argument list !

Posted on by

SSMS reports are one of the best ways to pull some great deal of Server level or Database level information within SQLServer.

Today I was running the ‘Disk Usage’ report (This one is my favorite) for one of the database and immediately got this error!

Error – Index (zero based) must be greater than or equal to zero and less than the size of argument list!

Well that’s one really cool error message to get when you are seriously working on something important!

 

Immediately I checked to see if the same report is causing issues for other databases which were available on the same instance.

The report was working great for all other databases except one which gave the error.

All the 4 ‘Disk Usage’ reports where having issues for that particular database, and I decided to compare this database with one for which the report was running fine.

Eventually the culprit was found. The database was having compatibility level 80 (SQL 2000) and was residing on a SQL Server 2005 SP4 instance. SSMS which I used to run reports was of version 10.50.2500 (2008 R2 SP1).

There are multiple T-SQL statements which are ran to pull this report,and this can be tracked by running a SQL-Profiler(Again, with caution for a production environment)

Changing the compatibility to 90 fixed the issue and the reports were running as expected.

Note – Changing the compatibility needs to be reviewed properly with the application owners as it can bring unexpected results. Proper testing and approval needs to be taken.

Conclusion –

Yet another reason to find all the databases which are running with lower compatibility and push application owners to fix their code where ever its required and move forward with latest compatibility levels.

For SQL Server 2012 compatibility level 80 is not supported, hence you won’t face these type of compatibility related issues anymore.