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
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.
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. :)
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
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
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.
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
16 People are following this question.
