SQL Overview V - Monitoring Long Running Jobs

Question

SQL Overview V - Monitoring Long Running Jobs

David Bird

SSCarpal Tunnel

Points: 4669
More actions
March 23, 2010 at 9:43 pm

#219801

Comments posted to this topic are about the item SQL Overview V - Monitoring Long Running Jobs
David Bird

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

cclark-1005023 Valued Member Points: 51 More actions · Answer 1

I like the thoughts in this, but we use a more general solution where we track similar information for all production jobs. Instead of using a manually configured time period for each job we calculate the standard deviation based on the historical job durations and report only when the current run time falls outside of 3 standard deviations. This alerts us to both jobs that run too long or too short, etc.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

cclark-1005023 (3/24/2010)
I like the thoughts in this, but we use a more general solution where we track similar information for all production jobs. Instead of using a manually configured time period for each job we calculate the standard deviation based on the historical job durations and report only when the current run time falls outside of 3 standard deviations. This alerts us to both jobs that run too long or too short, etc.

Nice Idea. Since, statistically, 99% should fall within that range of deviations - it should be more accurate to find when a job is behaving abnormally and avoid false positives.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Nice article. Thanks for the other articles in the series as well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 4

cclark-1005023 (3/24/2010)
I like the thoughts in this, but we use a more general solution where we track similar information for all production jobs. Instead of using a manually configured time period for each job we calculate the standard deviation based on the historical job durations and report only when the current run time falls outside of 3 standard deviations. This alerts us to both jobs that run too long or too short, etc.

@david-2 Bird -- Nice article. Thanks. I haven't read the others in the series but definately will.

@cclark -- Great idea, I hadn't thought of that method.

This is why I love the discussions on articles. The discussions to me are like icing on the cake except sometimes the icing is vitimin packed.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

arielmunafo2001 SSC Rookie Points: 30 More actions · Answer 5

Great Article

The limk to SQL Overview Part VI doesnt work....

http://www.sqlservercentral.com/articles/Monitoring/69650/

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 6

SQL Monitoring VI is coming out next week. Steve Jones must be planning ahead.

David Bird

Stephen Dyckes SSC Veteran Points: 244 More actions · Answer 7

I think the idea of monitoring on the deviation from an average runtime is spot on. I have used this methodology extensively to always be one step ahead of the users and bosses, and to stop problems before they become BIG problems. I have used the automated email to call a user running the query and have some fun (sometimes I love my job:exclamationmark:). I have saved countless hours by eliminating problems as they occur or quickly finding the root cause of a problem that occurred in the middle of the night or weekend, which has more than paid for itself multiple times over with just my sanity alone!

Since I am using a supported tool, SQL Sentry's Event Manager, I also have a graphical display showing me the interaction of all the events fired off in SQL or via the Task Scheduler in a cool calendar format, allowing me to drill in and see what was affected and/or caused the problem.

Stephen

adnan.korkmaz SSC-Addicted Points: 421 More actions · Answer 8

This is an old topic, but nevertheless: There is a tiny typo in "last_exectued_step_date" column. Raises error if not corrected.

aswini.gangaraju SSC Enthusiast Points: 117 More actions · Answer 9

Hi David,

I am trying to work with this package on SQL version 2008R2. I was able to to execute this on SQL 2005 version successfully by having some modification. I would like to know from you if i need to make any modifications need to make while working on SQL 2008 version.

Thanks in advance!

Ashwin

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 10

I have upgraded the package to SQL 2008 and plan to publish it someday. We are skipping the SQL Server 2008 R2 in our shop because of Microsoft's requirement for a new license to use the R2 release.

I did this upgrade a while ago and did not get any errors. I have a seperate package (never published) for pre SQl Server 200 instances that gave me warning and required a change to the multiserver database connection.

Upgrade gave this warning:

Warning 0x4020f42c: : The Maximum insert commit size property of the OLE DB destination "OLE DB Destination" is set to 0. This property setting can cause the running package to stop responding. For more information, see the F1 Help topic for OLE DB Destination Editor (Connection Manager Page).

Not able to access SQL Server 7 through multiserver. Created new multiserver to use Microsoft OLE DB Provider for SQL Server.

David Bird

SQLR45 SSC Journeyman Points: 77 More actions · Answer 11

Hello,

I don't get how to make the MultiServer connection to work, all my server have instance.

If I use the default LocalHost it's not working at all, gave me timeout.

I tried to delete and recreate the connection, no luck.

If I use a known Server\Instance it work, but as u probably guess it give me only data from that server.

Any help will be appreciated,

Thank you,

SQLR45 SSC Journeyman Points: 77 More actions · Answer 12

ok fixed it by myself reading this: http://www.sqlservercentral.com/Forums/Topic445734-283-3.aspx

Do not forget that if you have an instance you need to manually change the default value of SRV_Conn to SERVER\INSTANCE using MVS.

Also you need to change the LocalHost of ADONET.SQL_Overview and QASRV.SQL_Overview for SERVER\INSTANCE

Thank you,