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: - 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.