LKBEN10678: Howto know if the SQL Server Agent has tasks for my database


Symptom

You need to know if the SQL Server Agent has automatic tasks for the installed databases

Cause

Some database applications have a special installations or setup and install automatic tasks

Solution

The SQL Server Agent is used for running automatic tasks in the SQL Server environment. It has the possibility to run jobs

and to check the status of the SQL Server.

The primary use is:
- run automatic sceduled jobs
- run silent one time jobs
- automatically react on errors and generate alerts

The SQL Server Agent needs for doing it's job a database. This database is the msdb database which is installed

automatically. To see if there are jobs defined in this database you can use the following sql query:

--the msdb is used by the sql server agent
use msdb
select * from sysjobs
select * from sysjobschedules
select * from sysschedules   

This is an uncomplete list of tables in sql server with a litte description:

sysalerts (Transact-SQL) contains the warnings
 
syscategories (Transact-SQL) contains the categories
 
sysdownloadlist (Transact-SQL) contains queue for downloads
 
sysjobactivity (Transact-SQL) contains information about job activity
 
sysjobhistory (Transact-SQL) contains information about the job history
 
sysjobs (Transact-SQL) contains the information about the planned jobs
 
sysjobschedules (Transact-SQL) contains timing information for the jobs
 
sysjobservers (Transact-SQL) contains a list of servers for a job (a job can run on multiple servers)
 
sysjobsteps (Transact-SQL) contains steps for executing the jobs
 
sysjobstepslogs (Transact-SQL) contains protocol information
 
sysnotifications (Transact-SQL) contains a record for every notification
 
sysoperators (Transact-SQL) contains a record for every operator
 
systargetservergroupmembers (Transact-SQL) contains a list of server and who is a member of it
 
systargetservergroups (Transact-SQL) contains a list of server systems
 
systargetservers (Transact-SQL) more information about the lists of servers
 
systaskids (Transact-SQL) for backward compatibility and upgraded jobs
 
sysproxies (Transact-SQL) contains proxy information
 
sysproxylogin (Transact-SQL) contains the proxy login
 
sysproxysubsystem (Transact-SQL) delivers the sub system for every proxy account
 
sysschedules (Transact-SQL) contains information about the scheduled tasks
 
syssessions (Transact-SQL) contains the start data for every sql agent session
 
syssubsystems (Transact-SQL) information about all proxy systems for the sql server agent

Disclaimer:

The information provided in this document is intended for your information only. Lubby makes no claims to the validity of this information. Use of this information is at own risk!

About the Author

Author: Wim Peeters - Keskon GmbH & Co. KG

Wim Peeters is electronics engineer with an additional master in IT and over 30 years of experience, including time spent in support, development, consulting, training and database administration. Wim has worked with SQL Server since version 6.5. He has developed in C/C++, Java and C# on Windows and Linux. He writes knowledge base articles to solve IT problems and publishes them on the Lubby Knowledge Platform.