question

Bugmesh avatar image
Bugmesh asked

Create a graphical "Process map" of SQL Server Jobs

I inherited an old environment that is using SQL Server 2000 (for the most part - a couple are 2005) as the backend for many End-of-life applications that have no vendor support. There are over 270 scheduled SQL Jobs that run in this environment, some are normal DB Maintenance jobs (backups, Integrity Checks, Optimizations) but there are many others that run on the server under a multitude of schedules. Some of these jobs have dependencies on other jobs or completion of mainframe (yes I said mainframe, you know, JCL, MVS, etc) processes. I have been trying to get a handle on what is running and when and which ones are dependent on others. We have had a few "opportunities" with jobs running long causing others to fail or hang and they have to be "killed off" so they do not impact daily operations. I want to be able to eliminate simple resource contention, if that is indeed what the issue is. We have just had to rebuild the SAN because of a perfect storm of old hardware and software, increased utilization, and inexperienced Network Engineers. we might still be having some I/O issues on the subsystem, but since this is now "my baby" I want to be able (at a glance) to spot potential contention between scheduled tasks and other daily/monthly or quarterly scheduled processes. I have used a couple monitoring programs in the past, but what I would like to do is create a graphical display showing all the jobs, when they are scheduled, what their min/max/avg duration is, job description, steps, and any relationship/dependency to other jobs. Idera has a monitor(free) that uses a calendar as the vehicle to display all the jobs, but this gets pretty busy when you have that many jobs and there is no real/clean way to export the list to excel/visio or any other presentation software. If someone has had this need before and has already conquered the issues, I would certainly appreciate you enlightening me on how to make it happen. Are there Process flow programs that can use a query result to build and display this information in a readable, logical format? I hope I have articulated my needs here, but if further clarification is needed, please feel free to send me any followup questions. I appreciate your your assistance
sql-server-2000jobsprocessjob-history
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If any of the answers are helpful, please indicate this by clicking on the thumbs up next to them. If any one answer solves your question, indicate that by clicking on the check mark next to that answer. You can vote on as many helpful answers as you want. You can only select one correct answer.
0 Likes 0 ·
Tim avatar image
Tim answered
If you can find such a query or report to detail this out I will pay you for the results. in the mean time I have helped implement an enterprise scheduler called Control M by BMC Software. Very similar to AutoSys in that it handles the schedule and can build the dependancy relationships. If your job is waiting on a file or another process to complete then it shows that dependency. It isn't cheap but well worth the investment. Being able to graphically see the impact of one job failure on downstream events is priceless. Also it stops me getting phone calls because my ETL process didn't produce some report when I never got the files to import. Allows us the properly point fingers. :)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Never underestimate the career preserving power of a properly pointed finger!
2 Likes 2 ·
Tim, Ultimately I would love to have something like this, but unfortunately the budget is not allowing a lot of wiggle room. It has been a while since I have had to do any significant amount of coding (outside of TSQL scripting) but I may just have to break out the canned air blow away some of the cobwebs upstairs and see what I can accomplish. Thanks again for the input
0 Likes 0 ·
KenJ avatar image
KenJ answered
I'm not familiar with something that will generate a process map of SQL Agent jobs, but I have seen a tool that puts job executions into an outlook style calendar. Not to plug a particular tool, but this is the only one I've seen in action - I'm sure there are others: [ http://www.sqlsentry.net/event-manager/sql-server-enterprise.asp][1] When I meet a new server, I take a quick peek at the agent history just to see what's running, what time it runs, and how often it runs. I've posted the script here: [ http://kenj.blogspot.com/2009/03/sql-agent-job-summary.html][2] [1]: http://www.sqlsentry.net/event-manager/sql-server-enterprise.asp [2]: http://kenj.blogspot.com/2009/03/sql-agent-job-summary.html
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ken, I appreciate the input. I have looked at a few of the commercial products available from Redgate and Idera and they have their strength in monitoring jobs (which is good) but they seem a bit limited on reporting features. I also looked at your blog and found your script to be useful. I made a few modifications to it and then exported the result set it to a spreadsheet( I then embellished it a bit. I can send you a snippet of the final if you are interested) I am working/thinking on a way I can incorporate this into an automated process using reporting services.
0 Likes 0 ·
Bugmesh - that would be nice. You can send it using the mail icon at the top of the site (just put KenJ in the "To" line and the site will take care of the routing).
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Doesn't give you everything you want, but take a look at SQLJobVis ( http://www.sqlsoft.co.uk/sqljobvis.php) - it gives a graphical view of the job history data
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev, appreciate the input. I have seen this and it is a pretty good monitoring tool AND the price is right. It works good for the monitoring but again, ultimately I am looking for something more comprehensive
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.