Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 39 ยท Administration and Security

SQL Server Agent, Maintenance and Monitoring

Automate SQL Server with SQL Server Agent jobs, schedules and alerts. Learn index rebuild versus reorganize, statistics updates, CHECKDB, Database Mail and a monitoring checklist.

A healthy SQL Server doesn't look after itself. Backups must run at night, indexes fragment, statistics go stale, disks fill and someone should notice before the users do. The tool that schedules this work is SQL Server Agent, a service that runs jobs on a schedule and raises alerts. This lesson covers Agent, the maintenance tasks every server needs and a basic monitoring routine.

Agent and the maintenance commands can't run in the browser, so the T-SQL is read-only, with runnable stand-ins that practice the decisions (such as when to rebuild an index). It connects to backup and restore and statistics.

SQL Server Agent basics

Agent is part of SQL Server on Windows and Linux (and available as Elastic Jobs or Azure Automation in Azure SQL Database, where there is no Agent). It stores its definitions in the msdb database. Its building blocks:

Concept What it is
Job A named series of steps: T-SQL, PowerShell, an SSIS package, an OS command
Step One action, with "on success / on failure" routing
Schedule When: daily at 02:00, every 15 minutes, on Sundays, at Agent startup
Operator A person or group to notify by email
Alert A reaction to an error number, severity or performance counter

Create jobs in SSMS or in T-SQL with the msdb procedures:

SQL
USE msdb;
EXEC dbo.sp_add_job         @job_name = N'Nightly Full Backup';
EXEC dbo.sp_add_jobstep     @job_name = N'Nightly Full Backup',
                            @step_name = N'Backup Shop',
                            @subsystem = N'TSQL',
                            @command = N'BACKUP DATABASE Shop TO DISK = N''D:\Backups\Shop.bak'' WITH COMPRESSION, CHECKSUM, INIT;';
EXEC dbo.sp_add_schedule    @schedule_name = N'Daily 2am', @freq_type = 4, @freq_interval = 1, @active_start_time = 020000;
EXEC dbo.sp_attach_schedule @job_name = N'Nightly Full Backup', @schedule_name = N'Daily 2am';
EXEC dbo.sp_add_jobserver   @job_name = N'Nightly Full Backup';

Set an operator and a notification on failure; a job that fails silently is worse than none. Check history with msdb.dbo.sysjobhistory or the Job Activity Monitor.

For database-wide automation consider the community-standard Ola Hallengren maintenance solution (IndexOptimize, DatabaseBackup, DatabaseIntegrityCheck), which most DBAs install instead of writing their own scripts.

Index maintenance

Over time, inserts and updates cause page splits that leave pages half empty and out of order: fragmentation. Measure it:

SQL
SELECT OBJECT_NAME(ips.object_id) AS tbl, i.name AS idx,
       ips.avg_fragmentation_in_percent, ips.page_count
FROM   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN   sys.indexes AS i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE  ips.page_count > 1000
ORDER  BY ips.avg_fragmentation_in_percent DESC;

The classic guideline (and it is only a guideline):

Fragmentation Action
under 5-10% Nothing
10-30% ALTER INDEX ... REORGANIZE (online, light, resumable)
over 30% ALTER INDEX ... REBUILD (rebuilds the tree, refreshes statistics)

Ignore small indexes (under about 1,000 pages): fragmentation there doesn't matter. On SSDs, many experts rebuild far less, because random reads are cheap. Rebuilding with ONLINE = ON (Enterprise or Azure) keeps the table usable. Choose a sensible FILLFACTOR (for example 90) for indexes that suffer many splits.

The decision rule is a simple query. Try it on a list of indexes:

SQL Choose reorganize or rebuild

Press Run (or Ctrl + Enter) to see the result here.

Statistics and integrity

  • Statistics: keep auto-update on, and refresh important tables more often than the threshold allows: UPDATE STATISTICS dbo.Orders WITH FULLSCAN; or EXEC sp_updatestats;. Rebuilding an index updates its statistics for free; don't update the same statistics again right after.
  • Integrity: DBCC CHECKDB verifies pages and allocation structures. Run it weekly (more often for critical data) on every database, and on restored copies of backups, to prove the backups are good.
SQL
DBCC CHECKDB (N'Shop') WITH NO_INFOMSGS, ALL_ERRORMSGS;

If it reports corruption, restore from backup rather than using the repair options that can lose data.

Notifications: Database Mail and alerts

Configure Database Mail (sp_send_dbmail) so Agent and your own code can send email. Then create alerts for what matters:

SQL
EXEC msdb.dbo.sp_add_alert @name = N'Severity 19+ errors',
     @severity = 19, @notification_message = N'Fatal error',
     @job_id = NULL;

Typical alerts: errors with severity 19 to 25, error 823/824/825 (disk and corruption errors), a full log file, a failed backup job.

A monitoring checklist

Check these daily or let a tool do it:

  1. Backups: did each job succeed? Is the newest backup recent enough for your RPO?
  2. Disk space: data, log and tempdb volumes.
  3. Failed jobs and Agent error logs.
  4. Blocking and long-running queries (DMVs).
  5. Wait statistics trends and CPU/memory counters (Page Life Expectancy, batch requests/sec).
  6. Errors in the SQL Server error log: EXEC sp_readerrorlog;

Here is a small stand-in that flags overdue backups from a status table, the sort of query you would schedule as a monitoring job:

SQL Which databases breach their RPO?

Press Run (or Ctrl + Enter) to see the result here.

flowchart TD
  S["Agent schedule fires"] --> J["Job: step 1 backup"]
  J --> K{"Success?"}
  K -- "yes" --> N["Step 2: CHECKDB or index job"]
  K -- "no" --> E["Notify operator by Database Mail"]
  N --> H["History in msdb"]

Agent runs the work; operators and alerts make sure someone hears about failures.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Where does SQL Server Agent store job definitions and history?

    Agent keeps its jobs, schedules, operators and history in the msdb system database.
  2. 2. What is a sensible default for an index with 40% fragmentation and 50,000 pages?

    Above roughly 30% on a sizeable index, a rebuild is the usual guideline. Tiny indexes are not worth maintaining.
  3. 3. Which command checks a database for corruption?

    DBCC CHECKDB checks the logical and physical integrity of all objects. VERIFYONLY only checks that a backup is readable.
  4. 4. Why attach an operator notification to every maintenance job?

    Notifications turn a silent failure into something a human can fix the same day.

Summary

  • SQL Server Agent runs scheduled jobs (steps, schedules, operators, alerts) stored in msdb.
  • Maintain indexes by fragmentation and size, keep statistics fresh and run DBCC CHECKDB regularly.
  • Alert on failures and monitor backups, disk space, blocking and waits.
  • Use proven maintenance scripts instead of writing your own from scratch.

Next: Project: order management database