SQLServerCentral / Discuss Content Posted by David Bird / Article Discussions / Article Discussions by Author / SQL Overview V - Monitoring Long Running Jobs / Latest Posts

RE: SQL Overview V - Monitoring Long Running Jobs

SQLR45 — Thu, 01 Mar 2012 06:37:29 GMT

ok fixed it by myself reading this: http://www.sqlservercentral.com/Forums/Topic445734-283-3.aspxDo 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\INSTANCEThank you,

RE: SQL Overview V - Monitoring Long Running Jobs

SQLR45 — Wed, 29 Feb 2012 12:28:46 GMT

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,

RE: SQL Overview V - Monitoring Long Running Jobs

David Bird — Thu, 29 Sep 2011 07:39:23 GMT

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.

RE: SQL Overview V - Monitoring Long Running Jobs

aswini.gangaraju — Wed, 28 Sep 2011 20:33:48 GMT

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

RE: SQL Overview V - Monitoring Long Running Jobs

adnan.korkmaz — Fri, 16 Sep 2011 00:43:38 GMT

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

RE: SQL Overview V - Monitoring Long Running Jobs

Stephen Dyckes — Fri, 29 Jul 2011 16:54:32 GMT

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

RE: SQL Overview V - Monitoring Long Running Jobs

David Bird — Thu, 25 Mar 2010 07:30:42 GMT

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

RE: SQL Overview V - Monitoring Long Running Jobs

arielmunafo2001 — Thu, 25 Mar 2010 06:31:08 GMT

Great ArticleThe limk to SQL Overview Part VI doesnt work....http://www.sqlservercentral.com/articles/Monitoring/69650/

RE: SQL Overview V - Monitoring Long Running Jobs

Trey Staker — Wed, 24 Mar 2010 11:25:51 GMT

[quote][b]cclark-1005023 (3/24/2010)[/b][hr]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.[/quote]@David 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.

RE: SQL Overview V - Monitoring Long Running Jobs

SQLRNNR — Wed, 24 Mar 2010 10:58:11 GMT

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

RE: SQL Overview V - Monitoring Long Running Jobs

SQLRNNR — Wed, 24 Mar 2010 10:57:41 GMT

[quote][b]cclark-1005023 (3/24/2010)[/b][hr]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.[/quote]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.

RE: SQL Overview V - Monitoring Long Running Jobs

cclark-1005023 — Wed, 24 Mar 2010 07:31:22 GMT

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.

SQL Overview V - Monitoring Long Running Jobs

David Bird — Tue, 23 Mar 2010 21:43:52 GMT

Comments posted to this topic are about the item [B]SQL Overview V - Monitoring Long Running Jobs[/B]