This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a list of important system tables to find information about the SQL Server Agent.
These system tables are very useful for a Database Administrator to find out different information about the SQL Job, Alerts and Operators.
The MSDB contains all SQL Server Agent information in the following tables.
Stores information about the alerts:
1 |
SELECT *FROM msdb.dbo.sysalerts |
Stores information about current SQL Server Agent job activity and status:
1 |
SELECT *FROM msdb.dbo.sysjobactivity |
Stores information about the execution of scheduled jobs by SQL Server Agent:
1 |
SELECT *FROM msdb.dbo.sysjobhistory |
Stores information for each scheduled job to be executed by SQL Server Agent:
1 |
SELECT *FROM msdb.dbo.sysjobs |
Stores schedule information for jobs to be executed by SQL Server Agent:
1 |
SELECT *FROM msdb.dbo.sysjobschedules |
Stores information about each step of the jobs:
1 |
SELECT *FROM msdb.dbo.sysjobsteps |
Stores information about job step logs:
1 |
SELECT *FROM msdb.dbo.sysjobstepslogs |
Stores information about SQL Server Agent operator:
1 |
SELECT *FROM msdb.dbo.sysoperators |
Stores information about SQL Server Agent job schedules:
1 |
SELECT *FROM msdb.dbo.sysschedules |