What SQl Agent Jobs Should I set up?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » What SQl Agent Jobs Should I set up?

What SQl Agent Jobs Should I set up?

Rate Topic

Display Mode

Topic Options

Author

Message

gs1975

Posted Monday, October 01, 2012 3:11 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:04 AM
Points: 28, Visits: 573

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

Beasto

Posted Tuesday, October 02, 2012 3:55 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 06, 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

tim_harkin

Posted Tuesday, October 02, 2012 7:48 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:26 AM
Points: 138, Visits: 356

Seven SQL Agent Jobs you should be running

Post #1367043

rmechaber

Posted Tuesday, October 02, 2012 7:51 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:43 AM
Points: 656, Visits: 2,921

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

Nadrek

Posted Wednesday, October 03, 2012 2:34 PM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:28 PM
Points: 675, Visits: 2,031

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

gs1975

Posted Thursday, October 04, 2012 6:36 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:04 AM
Points: 28, Visits: 573

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

rmechaber

Posted Thursday, October 04, 2012 7:02 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:43 AM
Points: 656, Visits: 2,921

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

gs1975

Posted Thursday, October 04, 2012 7:14 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:04 AM
Points: 28, Visits: 573

Thanks Rich,

I will look at this article now.

Cheers,
George

Post #1368383

« Prev Topic | Next Topic »

Permissions