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

What SQl Agent Jobs Should I set up? Expand / Collapse
Author
Message
Posted Monday, October 1, 2012 3:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:38 AM
Points: 30, Visits: 704
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

Post #1366409
Posted Tuesday, October 2, 2012 3:55 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 6, 2013 5:52 AM
Points: 66, Visits: 220
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
Post #1366928
Posted Tuesday, October 2, 2012 7:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 12:44 PM
Points: 250, Visits: 698
Seven SQL Agent Jobs you should be running
Post #1367043
Posted Tuesday, October 2, 2012 7:51 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 5:01 PM
Points: 717, Visits: 3,034
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
Post #1367046
Posted Wednesday, October 3, 2012 2:34 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:06 PM
Points: 887, Visits: 2,453
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.



Post #1368033
Posted Thursday, October 4, 2012 6:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:38 AM
Points: 30, Visits: 704
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
Post #1368349
Posted Thursday, October 4, 2012 7:02 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 5:01 PM
Points: 717, Visits: 3,034
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
Post #1368377
Posted Thursday, October 4, 2012 7:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:38 AM
Points: 30, Visits: 704
Thanks Rich,

I will look at this article now.

Cheers,
George
Post #1368383
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse