DBA Tasks - To automate or use my own eyes

Question

DBA Tasks - To automate or use my own eyes

Johnny B

SSCrazy

Points: 2965
More actions
December 17, 2015 at 4:25 pm

#314259

Hi,
I've been asked to check the jobs and sql logs for 6 production servers. Two of them are SQL2000 the rest 2008 R2. I don't mind doing this manually. There is something 'pure' about checking job history and the logs.
Who out there would automate this for just 6 servers (assume an average of 15 jobs per server). If you did automate it, what strategy and tactic would you use?
Hoping we enjoy the responses.
Johnny B
SQL 2012 Standard VPS Windows 2012 Server Standard
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

Johnny B (12/17/2015)
Hi,
I've been asked to check the jobs and sql logs for 6 production servers. Two of them are SQL2000 the rest 2008 R2. I don't mind doing this manually. There is something 'pure' about checking job history and the logs.
Who out there would automate this for just 6 servers (assume an average of 15 jobs per server). If you did automate it, what strategy and tactic would you use?
Hoping we enjoy the responses.
Johnny B

I automated it in the form of a "Morning Report" that I have the servers send me. And, yeah... I've only got 6 servers. The report tells me how many times a job tried to run, how many times it pass, failed, or were aborted, what the max duration was, and a bunch of other goodies. It keeps me from having to go through the tedium of reviewing "once per minute" jobs, etc.

I'll also state that none of my overnight jobs are critical so I don't even bother with them sending an alert.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

We use an SSRS "dashboard" for this, also an overnight SSRS report which lists any individual job step errors.

These are based on querying the agent logs in msdb.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ScottPletcher SSC Guru Points: 101118 More actions · Answer 3

I've got more servers than that, but I'd automate it for one server. I don't see time spent visually scanning a log file as particularly productive. Besides, what happens when I'm on vacation?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lowell SSC Guru Points: 323518 More actions · Answer 4

to a high degree, every snippet of code is written with automation in mind. i think that's probably a best practice where possible.

today might be six servers, but tomorrow, what if you add one more? ten more? who knows; having something that auto expands to the data/servers demand makes my life so much easier all the way around

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

I would definitely automate it for six servers. I would do it for 3 servers because I don't believe that manual reviews are effective. They take longer than necessary and we can miss things. Like Lowell said, I do most things with an eye towards automation.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

There are three things that I absolute don't believe in automating... a cocktail before and a cigarette after. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 7

Jeff Moden (12/21/2015)
There are three things that I absolute don't believe in automating... a cocktail before and a cigarette after. 😀

I don't get it. Whats the third thing? :hehe:

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

Oh, and getting back to the title question of this post of "DBA Tasks - To automate or use my own eyes", the only correct answer is both. Automation is good but it does need to be verified by a human every once in a while.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

HappyGeek SSCoach Points: 18729 More actions · Answer 9

ScottPletcher (12/21/2015)
I've got more servers than that, but I'd automate it for one server. I don't see time spent visually scanning a log file as particularly productive. Besides, what happens when I'm on vacation?

Agreed, automate for even one; I would go further and say if you had a report mailed to yourself on a daily basis make sure you include the DBA Team or at least the Helpdesk, at least then you can't be criticised for nobody knowing of a problem while you were on vacation!

...

RTaylor2208 SSChampion Points: 13298 More actions · Answer 10

Jeff Moden (12/17/2015)
Johnny B (12/17/2015)
Hi,
I've been asked to check the jobs and sql logs for 6 production servers. Two of them are SQL2000 the rest 2008 R2. I don't mind doing this manually. There is something 'pure' about checking job history and the logs.
Who out there would automate this for just 6 servers (assume an average of 15 jobs per server). If you did automate it, what strategy and tactic would you use?
Hoping we enjoy the responses.
Johnny B
I automated it in the form of a "Morning Report" that I have the servers send me. And, yeah... I've only got 6 servers. The report tells me how many times a job tried to run, how many times it pass, failed, or were aborted, what the max duration was, and a bunch of other goodies. It keeps me from having to go through the tedium of reviewing "once per minute" jobs, etc.
I'll also state that none of my overnight jobs are critical so I don't even bother with them sending an alert.

Exactly this, we have a similar setup, we have a much larger scale environment but for more than 1 server to monitor I'd automate it every time.

MCITP SQL 2005, MCSA SQL 2012

Wayne West SSC-Insane Points: 22586 More actions · Answer 11

I use a Perl script that reads the previous 24 hours of logs per server and filters out successful logins, so server restarts and failed logins and jobs stand out quite clearly. The result file also contains a DBCC summary that only shows errors, the full DBCC results are also stored so I can see the details in case something in the summary shows that I need to look at something in depth. This gives me a very compact summary that takes just a minute per server to scan, and a batch job ages the logs so I'll have a back list for when I'm on vacation or in case I miss an error. It's emailed to my work account that I can look at from my phone if I'm feeling like being a glutton for punishment.

I also have alerts on the jobs for things that require a more immediate response.

Perl is crazy fast, which is really weird for an interpreted language. I'd scan all of my servers at 23:59 seven days a week.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]