My SQL Server jobs for monitoring, backup, and maintenance

Scripts in the msdb folder are used to set up email notifications and alerts. The central part here is a trigger for insert on msdb.dbo.sysjobhistory table, it sends an email on error. But, you can change notification to whatever is better for you.

• 0.Set.Mail - Database Mail set up
• 1.Set.Alerts - alerts set up
• 2.trg_i_sysjobhistory_2008 - trigger for SQL Server 2008/2008R2
• 2.trg_i_sysjobhistory_2012 - trigger for SQL Server 2012 and above
• 3.Job.Status - view to get job current status

Jobs

• job_Maintenance_CheckDB - creates jobs to run DBCC checks. It also creates a log table and writes logs in the msdb.

1. The first job for physical only check. Schedule: first, second and third Saturday.
2. The second job for a full check. Schedule: fourth Saturday.

• job_Maintenance_SaveDatabaseSize - saves the size of all servers databases in msdb, except [tempdb] and [model]. Schedule: once per day.
• job_Monitoring_CheckAlwayOn - checks AlwaysOn synchronization state and redo queue size (threshold is 100MB). Schedule: every 5 min.
• job_Monitoring_CheckLogSize - if any t-log file is 90% full, it will kick backup job. If it is greater than 16GB, it raises an error. Schedule: every 15 min.
• job_Monitoring_CheckLongTran - checks for different types of open transactions which stay open for more than 5 min. Schedule: every 5 min.
• job_Monitoring_CheckMirroring - checks mirroring synchronization state and unsent log size (threshold is 100MB). Schedule: every 5 min.
• job_Monitoring_CheckNewMemDump - checks for a new memory dump file generated by the SQL Server Database Engine. Schedule: every 15 min.
• job_Monitoring_CheckPullSubscription - checks distribution agent status on a subscriber when pull subscription is used. Schedule: every 15 min
• job_Monitoring_CheckReplicationAgent - checks distribution agent and transaction log reader agent status on the distributor server. Schedule: every 15 min
• job_Monitoring_NotificationCheck - it raises a fake error to check notifications. Schedule: every Monday at 11:00.

For index and statistics maintenance, I prefer to use Ola Hallengren scripts. You can find them here: GitHub

I'll add my own backup jobs later here.