What SQl Agent Jobs Should I set up?

Question

What SQl Agent Jobs Should I set up?

gs1975

Ten Centuries

Points: 1057
More actions
October 1, 2012 at 3:11 am

#268327

Hi,
What are the most common/best SQL Agent Jobs to set up?
The ones I generally set up are as follows:- (Daily or weekly depending on job & circumstances)
1.) Backing up databases
2.) DBCC CheckDB Job
3.) Disk Alerts
4.) Rebuilding/Reorganising Indexes Based on Fragementation Level
5.) Update Stats
6.) Recycle Error_Log
7.) Recycle Agent_Error_Log
8.) Checking File Space of Selected Databases
What other common jobs am I missing/should I be looking at?
Kind regards,
George

Viewing 8 posts - 1 through 7 (of 7 total)

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

Beasto Valued Member Points: 74 More actions · Answer 1

I normally create one for checking the error log as well (excluding information you don't need of course), has given me early warnings of corruption etc in the past and I wouldn't do without it now!! I also like to have some performance warnings from the built in metrics and alerts on error codes 17-25 + 823/824/825

Simon

http://www.simonrich.es

tim_harkin SSCrazy Points: 2399 More actions · Answer 2

tim_harkin

SSCrazy

Points: 2399

October 2, 2012 at 7:48 am

#1544380

Seven SQL Agent Jobs you should be running[/url]

Rich Mechaber SSChampion Points: 11130 More actions · Answer 3

Well that's pretty open-ended....

In no particular order, here's some of what I like to run:

> You list "backups", but that will likely be at least 3 different jobs/schedules for FULL, DIFFERENTIAL, and LOG backups

> I have a job that looks for failed Agent job steps and reports them to me, as a check on Agent jobs

> I have a series of daily checks in one job: Autogrow events in the last 24 hours, critical DB sizes, DBs in FULL recovery mode that aren't in a specific list of DBs that should be, etc.

> TempDB size tracking

> Purge old records from msdb for things like backup runs, etc.

> Find SQL backup files on disk that haven't gone off to tape via a network backup. This took a bit of work with CLR for me to set up, but I can now easily query the SQL backup drive for *.BAK files that have an archive bit set, which means they haven't been backed up to tape by our system backups.

Does this help?

Rich

Nadrek SSC-Insane Points: 20039 More actions · Answer 4

Check for enabled but untrusted constraints.

Log performance/error/etc information to a monitoring server

Log security information to a monitoring server

Log backup/agent job/error log information to a monitoring server

Optional: Restore a database backup as a test

- if you don't do this, at minimum backup with CHECKSUM and then RESTORE VERIFYONLY, but that's still not as good as a real restore.

Optional: check for disabled constraints.

gs1975 Ten Centuries Points: 1057 More actions · Answer 5

Thanks for all your replies. There's some really good jobs here that I had overlooked.

I am just going through all of my servers and just want a number of jobs I can script out and run against all servers.

All of your suggestions are most helpful.

Thanks,

George

Rich Mechaber SSChampion Points: 11130 More actions · Answer 6

You're welcome, and thanks for replying!

Nadrek's suggestion to look for untrusted FK constraints is a very good one. An untrusted FK constraint will be a performance killer, and they aren't obvious if you're not looking for them. If you're looking for some more information, this is a great explanation and write-up describing the problem and the fix.

Rich

gs1975 Ten Centuries Points: 1057 More actions · Answer 7

Thanks Rich,

I will look at this article now.

Cheers,

George