Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Managing many SQL Servers? Expand / Collapse
Author
Message
Posted Friday, January 16, 2009 8:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:38 AM
Points: 22, Visits: 148
Rudy,
Great article. It talked more about your monitoring framework which I feel is the hardest part for a DBA to decide on and setup. I too perform automated monitoring using linked servers. I use sqlcmd instead of isql or osql, but still the same concept. I love this framework for monitoring b/c it's easy and it's allowed my team and I to monitor everything (jobs, space, error log, blocking, automated killing of blocking, replication failures and latency, etc) across ~70 installations around the globe from a central location.

I saw your solution for job monitoring where you join on sysjobservers to see if a job failed or not. Another join you could add is sysjobhistory. By joining on sysjobhistory you can also report/alert on the actual error message from the failed job. Here's a snippet of my logic to trap failed jobs. I run it every 5mins (for some servers it may be overkill) so hence my long date calculation/check at the end. The filtering isn't perfect, but seems to trap ~99% of the failed jobs for us. In SQL2005, some job failures do not have error codes, some will fail a job step but still mark the job successful. Other jobs will show a warning for a job step but still mark the job successful so I've learned to not trust job outcomes 100% of the time. That's why I also trap on certain key words as well. So far it's kept my team and I honest, although in the earlier days there were some painful learnings as we fine-tuned the logic =). I hope this is helpful:

select @@servername, jobs.[name], his.sql_message_id, his.sql_severity, his.[message], his.run_status, his.retries_attempted
,LastRunTime = convert (varchar(100), (ltrim(str(run_date))+' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':') ) )
from msdb..sysjobhistory his with(nolock), msdb..sysjobs jobs with(nolock), msdb..syscategories cat with(nolock)
where his.job_id = jobs.job_id
and jobs.category_id = cat.category_id
and his.run_status in (0,4)
and SUBSTRING(CONVERT(char(8), his.run_date),7,2) = DATEPART(dd, getdate())
and SUBSTRING(CONVERT(char(8), his.run_date),5,2) = DATEPART(mm, getdate())
and SUBSTRING(CONVERT(char(8), his.run_date),1,4) = DATEPART(yy, getdate())
--Filter certain job categories from alerting
and cat.[name] not in ('Test')
and (his.sql_message_id <> 0 or his.[message] like '%fail%' or his.[message] like '%Error%')
--the date conversion below is so we only trap jobs that failed in the last 5mins
and (convert (varchar(100), (ltrim(str(run_date))+' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':') ) )) >= (getdate() - 0.0034722222222222222222222222222222)

lc
Post #638175
Posted Friday, January 16, 2009 8:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:37 PM
Points: 312, Visits: 1,098
Hello Leon,

Thanks for the excellent tip/modification. I will test your code and then modify my reports with this additional information. Thanks!!

Does this code produce the same information on SQL 2005/2008?

Are you using SSRS to produce reports? I've made several web reports so that I can have them sent to my Blackberry. The web base reports are easy on the eyes and the develops get there own web reports regarding their test/development server.

Love hearing how other DBA are managing many SQL servers.

It's nice to know that your not alone in the sea of SQL servers/instance :)

Rudy



Post #638196
Posted Friday, January 16, 2009 9:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:38 AM
Points: 22, Visits: 148
Yes, the code works on SQL2005 and SQL2008 best I can tell. We're all SQL2005 x64 for the most part with a few SQL2008 installations sprinkled in but so far it's worked fine in both environments.

I don't use the monitoring so much for reporting in my group. We're a small DBA group so besides the usual break/fix and new server deployments we have limited bandwidth. We use the monitoring more for alerting us so we can respond quickly to issues.

lc
Post #638219
Posted Friday, January 16, 2009 10:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 12:49 PM
Points: 11, Visits: 120
Rudy,

When you say 'this ability' what are you referring to? I ask because your description of the original script says it works in earlier versions of sql.

Glen
Post #638299
Posted Friday, January 16, 2009 11:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:37 PM
Points: 312, Visits: 1,098
Glen,

This process/code works on all our SQL server. We are using versions 2000, 2005 and 2008 (we also has 7.0 and it work on that too). We also have a mix of 32 bit, 64 bit and servers will multiple instances. I'm not sure if this answers your question, if not can you re state it?

Thanks,

Rudy



Post #638342
Posted Friday, January 16, 2009 12:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 12:49 PM
Points: 11, Visits: 120
Rudy,

your message of Jan 15 stated "Just wanted to mention that this ability is available in SQL 2008 but does not work against older versions of SQL server".

I was unclear on which ability you were referencing.

Thanks,

Glen
Post #638436
Posted Friday, January 16, 2009 1:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:37 PM
Points: 312, Visits: 1,098
Oh, I see. Well SQL 2008 will not allow you to remotely execute code (via SSMS) to SQL 2000 and earlier. Why I'm not sure and I have not tested it but this comes from Microsoft.

This code does not care about version numbers as long as the code you are trying to execute is a valid for that version of SQL.

That's it.

Rudy



Post #638490
Posted Saturday, January 17, 2009 10:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 5, 2009 8:18 AM
Points: 1, Visits: 16
very nive script.
I am using a very advanced automated management tool named EZManage SQL Pro
and one of it's features is MultiScripting.

you can get it from
www.futureitsoft.com

itay
Post #638729
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse