LKBDE11339: Wie sehe ich, ob der SQL Server Agent Tasks für meine Datenbank definiert hat


This article has not been checked!

LKB | Created: 02/04/2020 | Version: 0 | Language: DE | Rating: 0 | Outdated: False | Marked for deletion: False

Author: Wim Peeters - Keskon GmbH & Co. KG


Symptom

Du willst wissen, ob der SQL Server Agent automatische Tasks für installierte Datenbanken eingestellt hat

Cause

Einige Datenbankanwendungen haben "special installations" oder "setup and install" - automatische Tasks

Solution

Der SQL Server Agent ist für automatische Tasks in einer SQL Server Umgebung da. Er bietet die Möglichkeit, Aufträge zu bestimmten Zeiten abzuarbeiten oder der Status des SQL Server zu überprüfen.

Der Hauptgebrauch ist:
- automatisch geschedulte Jobs laufen lassen
- einmalige Hintergrund Jobs laufen lassen
- automatisch auf Fehler zu reagieren und Alarme auszulösen

Der SQL Server Agent benötigt für diese Tätigkeiten eine eigene Datenbank, die automatisch installierte "msdb". Um darin definierte Aufträge zu sehen, kann man SQL-Abfragen nutzen:

-- die msdb wird vom SQL Server Agent verwendet:
use msdb
select * from sysjobs
select * from sysjobschedules
select * from sysschedules  

Hier ist eine unvollständige Liste von Tabellen im SQL Server mit einer (englischen) Kurzbeschreibung:

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

About the Author

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 in different European countries and different European languages. He writes knowledge base articles to solve IT problems and publishes them on the Lubby Knowledge Platform where he is one of the most important contributors and the main developer.

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!