SH

Sascha Hess

xenosystems.de - IT-Consulting & Data Management

www.xenosystems.de

STRATEGISCHE WISSENS-ROADMAP 2026

SQL Server

Monitoring & Logging

Probleme erkennen bevor Nutzer sie melden — SQL Server vollständig überwachen

1

10 Monitoring-Fallen

Die häufigsten Lücken in der Datenbanküberwachung — mit Sofort-Fixes

2

Monitoring-Toolkit

8 DMV-Skripte für sofortige Situationsanalyse in unter 5 Minuten

3

Alert-System aufbauen

SQL Agent, Database Mail, Extended Events — fertige Alert-Rezepte

4

Monitoring-Dashboard

Power BI Echtzeit-Dashboard für alle kritischen Datenbankmetriken

5

30-Tage-Monitoring-Plan

Von null zu einer vollständigen, proaktiven Datenbanküberwachung

WAS SIE IN DIESEM BUCH ERWARTEN DÜRFEN

■  10 Monitoring-Fallen — Die häufigsten Lücken mit konkreten Sofort-Fixes und Skripten.

■  Monitoring-Toolkit — 8 DMV-Skripte für vollständige Situationsanalyse in unter 5 Minuten.

■  Alert-System — Fertige Rezepte für SQL Agent Alerts, Database Mail und Extended Events.

■  Monitoring-Dashboard — Power BI Vorlage für alle kritischen Datenbankmetriken auf einen Blick.

■  30-Tage-Monitoring-Plan — Von null zu einer vollständigen, proaktiven Datenbanküberwachung.

DAS MONITORING-ZIEL

Kein Alert-Sturm, kein Alarm-Fatigue. Das Ziel ist ein System, das bei echten Problemen sofort und präzise alarmiert — und bei normalem Betrieb schweigt. Zu viele Alerts sind genauso schädlich wie zu wenige: nach dem dritten Fehlalarm wird kein Alert mehr ernst genommen.

-- Letzte Backup-Zeitpunkte je Datenbank prüfen (Sofort-Diagnose):

SELECT

    d.name                              AS Datenbank,

    d.recovery_model_desc               AS Recovery_Modell,

    MAX(CASE WHEN b.type = 'D'

             THEN b.backup_finish_date  END) AS Letztes_Full_Backup,

    MAX(CASE WHEN b.type = 'L'

             THEN b.backup_finish_date  END) AS Letztes_Log_Backup,

    DATEDIFF(HOUR,

        MAX(CASE WHEN b.type = 'D'

                 THEN b.backup_finish_date END),

        GETDATE())                      AS Stunden_Seit_Full,

    CASE

        WHEN MAX(CASE WHEN b.type='D' THEN b.backup_finish_date END)

             IS NULL THEN 'KEIN BACKUP!'

        WHEN DATEDIFF(HOUR,

             MAX(CASE WHEN b.type='D' THEN b.backup_finish_date END),

             GETDATE()) > 26

             THEN 'ÜBERFÄLLIG'

        ELSE 'OK'

    END                                 AS Backup_Status

FROM sys.databases d

LEFT JOIN msdb.dbo.backupset b

    ON d.name = b.database_name

WHERE d.database_id > 4   -- Keine System-DBs

  AND d.state_desc = 'ONLINE'

GROUP BY d.name, d.recovery_model_desc

ORDER BY Stunden_Seit_Full DESC NULLS FIRST;

-- Transaktionslog-Nutzung aller Datenbanken prüfen:

SELECT

    DB_NAME(database_id)             AS Datenbank,

    name                             AS Dateiname,

    type_desc,

    CAST(size * 8.0 / 1024 AS DECIMAL(10,1)) AS Groesse_MB,

    CAST(FILEPROPERTY(name,'SpaceUsed') * 8.0 / 1024

         AS DECIMAL(10,1))           AS Genutzt_MB,

    CAST(100.0 * FILEPROPERTY(name,'SpaceUsed')

         / NULLIF(size,0)

         AS DECIMAL(5,1))            AS Auslastung_Pct,

    physical_name

FROM sys.master_files

WHERE type_desc = 'LOG'

ORDER BY Auslastung_Pct DESC;

-- Alert: Log > 80 % genutzt → sofortige Benachrichtigung

-- Blocked Process Threshold setzen (in Sekunden):

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;

EXEC sp_configure 'blocked process threshold (s)', 15;  -- Alert nach 15 Sek.

RECONFIGURE;

-- Extended Events Session fängt dann das 'blocked_process_report'-Ereignis

-- Freier Speicherplatz je Laufwerk (über xp_fixeddrives):

CREATE TABLE #Laufwerke (Drive NVARCHAR(3), FreeSpace INT);

INSERT #Laufwerke EXEC xp_fixeddrives;

SELECT

    Drive,

    FreeSpace                             AS Frei_MB,

    CAST(FreeSpace / 1024.0 AS DECIMAL(10,1)) AS Frei_GB,

    CASE

        WHEN FreeSpace < 5120  THEN 'KRITISCH (< 5 GB)'

        WHEN FreeSpace < 20480 THEN 'WARNUNG (< 20 GB)'

        ELSE 'OK'

    END AS Status

FROM #Laufwerke

ORDER BY FreeSpace ASC;

DROP TABLE #Laufwerke;

-- Alle fehlgeschlagenen Jobs der letzten 24 Stunden:

SELECT

    j.name                               AS Job_Name,

    jh.run_date,

    jh.run_time,

    jh.run_duration,

    jh.message                           AS Fehlermeldung,

    CASE jh.run_status

        WHEN 0 THEN 'Fehler'

        WHEN 1 THEN 'Erfolgreich'

        WHEN 2 THEN 'Wiederholt'

        WHEN 3 THEN 'Abgebrochen'

        WHEN 4 THEN 'Wird ausgeführt'

    END                                  AS Status

FROM msdb.dbo.sysjobhistory  jh

JOIN msdb.dbo.sysjobs        j  ON jh.job_id = j.job_id

WHERE jh.run_status = 0      -- Nur Fehler

  AND jh.step_id    > 0      -- Keine Job-Gesamteinträge

  AND msdb.dbo.agent_datetime(jh.run_date, jh.run_time)

      > DATEADD(HOUR, -24, GETDATE())

ORDER BY jh.run_date DESC, jh.run_time DESC;

-- Autogrowth-Ereignisse aus dem Default Trace lesen:

SELECT

    te.name                              AS Ereignis,

    t.DatabaseName,

    t.FileName,

    t.Duration / 1000                    AS Dauer_ms,

    t.IntegerData * 8 / 1024             AS Wachstum_MB,

    t.StartTime

FROM sys.fn_trace_gettable(

    (SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)),

     LEN(path))) + 'log.trc'

     FROM sys.traces WHERE is_default = 1), DEFAULT) t

JOIN sys.trace_events te ON t.EventClass = te.trace_event_id

WHERE te.name LIKE '%Grow%'

  AND t.StartTime > DATEADD(DAY, -7, GETDATE())

ORDER BY t.StartTime DESC;

-- Always-On Replikationsstatus und Verzögerung:

SELECT

    ag.name                              AS AG_Name,

    ar.replica_server_name               AS Replica_Server,

    ars.role_desc                        AS Rolle,

    ars.synchronization_health_desc      AS Sync_Gesundheit,

    ars.connected_state_desc             AS Verbindung,

    adbrs.synchronization_state_desc     AS DB_Sync_Status,

    adbrs.secondary_lag_seconds          AS Verzoegerung_Sek,

    DB_NAME(adbrs.database_id)           AS Datenbank

FROM sys.availability_groups             ag

JOIN sys.availability_replicas           ar

    ON ag.group_id = ar.group_id

JOIN sys.dm_hadr_availability_replica_states ars

    ON ar.replica_id = ars.replica_id

LEFT JOIN sys.dm_hadr_database_replica_states adbrs

    ON ar.replica_id = adbrs.replica_id

ORDER BY adbrs.secondary_lag_seconds DESC;

-- Alert wenn secondary_lag_seconds > 300 (5 Minuten)

-- Page Life Expectancy (PLE) — der wichtigste Memory-Indikator:

SELECT

    object_name,

    counter_name,

    cntr_value                           AS PLE_Sekunden,

    CASE

        WHEN cntr_value < 300  THEN 'KRITISCH'

        WHEN cntr_value < 1000 THEN 'WARNUNG'

        ELSE 'OK'

    END AS Status

FROM sys.dm_os_performance_counters

WHERE object_name LIKE '%Buffer Manager%'

  AND counter_name = 'Page life expectancy';

-- Faustregel: PLE < 300 → Memory-Engpass

-- Für 16 GB+ RAM: Erwartungswert 1.000–4.000 Sek.

-- Alle kritischen Indikatoren in einer Übersicht (30-Sekunden-Check):

SELECT

    -- Uptime:

    DATEDIFF(HOUR, sqlserver_start_time, GETDATE()) AS Uptime_Stunden,

    -- Memory:

    physical_memory_in_use_kb / 1024            AS RAM_Genutzt_MB,

    page_fault_count,

    -- CPU:

    (SELECT TOP 1 cntr_value

     FROM sys.dm_os_performance_counters

     WHERE counter_name = 'SQL Server:CPU usage %'

        OR counter_name = 'CPU usage %') AS CPU_Pct,

    -- Connections:

    (SELECT COUNT(*) FROM sys.dm_exec_sessions

     WHERE is_user_process = 1)          AS Aktive_Sessions,

    -- Blocking:

    (SELECT COUNT(*) FROM sys.dm_exec_requests

     WHERE blocking_session_id > 0)      AS Blockierte_Sessions

FROM sys.dm_os_sys_info;

SELECT TOP 15

    wait_type,

    waiting_tasks_count,

    CAST(wait_time_ms / 1000.0 AS DECIMAL(12,1))   AS Wartezeit_Sek,

    CAST(max_wait_time_ms / 1000.0 AS DECIMAL(10,1)) AS Max_Wartezeit_Sek,

    CAST(100.0 * wait_time_ms

         / SUM(wait_time_ms) OVER ()

         AS DECIMAL(5,2))                           AS Anteil_Pct

FROM sys.dm_os_wait_stats

WHERE wait_type NOT IN (

    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_AUTO_EVENT',

    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',

    'LAZYWRITER_SLEEP','LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE',

    'REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','SERVER_IDLE_CHECK',

    'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY',

    'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP',

    'SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT',

    'SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_BUFFER_FLUSH',

    'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','WAITFOR','XE_DISPATCHER_WAIT',

    'XE_TIMER_EVENT','BROKER_EVENTHANDLER','CHECKPOINT_QUEUE',

    'DBMIRROR_EVENTS_QUEUE','SQLTRACE_WAIT_ENTRIES'

)

ORDER BY wait_time_ms DESC;

-- Alle aktuell blockierten Sessions mit Blocker-Information:

WITH BlockTree AS (

    SELECT

        r.session_id                        AS Geblockte_Session,

        r.blocking_session_id               AS Blocker_Session,

        r.wait_time / 1000                  AS Wartezeit_Sek,

        r.wait_type,

        r.status,

        r.logical_reads,

        DB_NAME(r.database_id)              AS Datenbank,

        SUBSTRING(qt.text, 1, 300)          AS Geblockte_Query,

        SUBSTRING(bt.text, 1, 300)          AS Blocker_Query

    FROM sys.dm_exec_requests r

    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt

    JOIN sys.dm_exec_sessions s

        ON r.blocking_session_id = s.session_id

    CROSS APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) bt

    WHERE r.blocking_session_id > 0

)

SELECT * FROM BlockTree ORDER BY Wartezeit_Sek DESC;

-- Datenbankdateien: Größe, Nutzung, freier Platz:

SELECT

    DB_NAME(vfs.database_id)             AS Datenbank,

    mf.name                              AS Logischer_Name,

    mf.type_desc,

    CAST(vfs.size_on_disk_bytes / 1048576.0 AS DECIMAL(10,1)) AS Groesse_MB,

    CAST(vfs.io_stall_read_ms

         / NULLIF(vfs.num_of_reads, 0) AS DECIMAL(10,1))      AS Avg_Read_ms,

    CAST(vfs.io_stall_write_ms

         / NULLIF(vfs.num_of_writes, 0) AS DECIMAL(10,1))     AS Avg_Write_ms,

    mf.physical_name

FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs

JOIN sys.master_files mf

    ON vfs.database_id = mf.database_id

   AND vfs.file_id     = mf.file_id

ORDER BY vfs.io_stall_read_ms + vfs.io_stall_write_ms DESC;

-- Alle Jobs mit letztem Ausführungsergebnis:

SELECT

    j.name                               AS Job_Name,

    j.enabled                            AS Aktiviert,

    CASE jh.run_status

        WHEN 1 THEN 'Erfolgreich'

        WHEN 0 THEN 'FEHLER'

        WHEN 3 THEN 'Abgebrochen'

        ELSE 'Unbekannt'

    END                                  AS Letzter_Status,

    msdb.dbo.agent_datetime(

        jh.run_date, jh.run_time)        AS Letzte_Ausführung,

    jh.run_duration                      AS Laufzeit_HHMMSS,

    jh.message                           AS Nachricht

FROM msdb.dbo.sysjobs j

LEFT JOIN (

    SELECT jh2.*,

           ROW_NUMBER() OVER (

               PARTITION BY jh2.job_id

               ORDER BY jh2.run_date DESC, jh2.run_time DESC

           ) AS RN

    FROM msdb.dbo.sysjobhistory jh2

    WHERE jh2.step_id = 0   -- Nur Job-Gesamtstatus

) jh ON j.job_id = jh.job_id AND jh.RN = 1

ORDER BY

    CASE WHEN jh.run_status = 0 THEN 0 ELSE 1 END,  -- Fehler zuerst

    j.name;

-- Alle wichtigen Memory-Counters:

SELECT

    object_name,

    counter_name,

    cntr_value,

    CASE counter_name

        WHEN 'Page life expectancy'

            THEN CASE WHEN cntr_value < 300 THEN 'KRITISCH'

                      WHEN cntr_value < 1000 THEN 'WARNUNG'

                      ELSE 'OK' END

        WHEN 'Memory Grants Pending'

            THEN CASE WHEN cntr_value > 0 THEN 'WARNUNG' ELSE 'OK' END

        WHEN 'Lazy writes/sec'

            THEN CASE WHEN cntr_value > 20 THEN 'WARNUNG' ELSE 'OK' END

        ELSE '—'

    END AS Status

FROM sys.dm_os_performance_counters

WHERE (object_name LIKE '%Buffer Manager%'

       AND counter_name IN (

           'Page life expectancy',

           'Buffer cache hit ratio',

           'Lazy writes/sec',

           'Page reads/sec',

           'Page writes/sec'

       ))

   OR (object_name LIKE '%Memory Manager%'

       AND counter_name IN (

           'Memory Grants Pending',

           'Total Server Memory (KB)',

           'Target Server Memory (KB)'

       ))

ORDER BY object_name, counter_name;

-- TempDB-Dateinutzung und PAGELATCH-Waits:

SELECT

    'TempDB Dateien' AS Bereich,

    name,

    CAST(size * 8.0 / 1024 AS DECIMAL(10,1))    AS Groesse_MB,

    CAST(FILEPROPERTY(name,'SpaceUsed') * 8.0 / 1024

         AS DECIMAL(10,1))                       AS Genutzt_MB,

    CAST(100.0 * FILEPROPERTY(name,'SpaceUsed')

         / NULLIF(size,0) AS DECIMAL(5,1))       AS Auslastung_Pct

FROM tempdb.sys.database_files

UNION ALL

SELECT

    'PAGELATCH Waits',

    wait_type,

    CAST(wait_time_ms / 1000.0 AS DECIMAL(10,1)),

    waiting_tasks_count,

    NULL

FROM sys.dm_os_wait_stats

WHERE wait_type LIKE 'PAGELATCH%'

  AND wait_time_ms > 0

ORDER BY Bereich, Auslastung_Pct DESC;

-- Alle aktiven Sessions mit Ressourcenverbrauch:

SELECT

    s.session_id,

    s.login_name,

    s.host_name,

    s.program_name,

    s.status,

    r.wait_type,

    r.wait_time / 1000                   AS Wait_Sek,

    r.total_elapsed_time / 1000          AS Laufzeit_Sek,

    r.cpu_time / 1000                    AS CPU_Sek,

    r.logical_reads,

    r.blocking_session_id,

    DB_NAME(r.database_id)               AS Datenbank,

    SUBSTRING(qt.text, 1, 200)           AS Query_Beginn

FROM sys.dm_exec_sessions s

LEFT JOIN sys.dm_exec_requests r

    ON s.session_id = r.session_id

OUTER APPLY sys.dm_exec_sql_text(

    COALESCE(r.sql_handle, s.most_recent_sql_handle)) qt

WHERE s.is_user_process = 1

  AND s.status <> 'sleeping'   -- Nur aktive Sessions

ORDER BY r.total_elapsed_time DESC NULLS LAST;

-- Database Mail Setup (einmalig, als sysadmin):

EXEC msdb.dbo.sysmail_add_account_sp

    @account_name        = 'DBA-Alerts',

    @description         = 'SQL Server Alert-Konto',

    @email_address       = 'sqlserver@firma.de',

    @display_name        = 'SQL Server DBA Alerts',

    @mailserver_name     = 'smtp.firma.de',

    @port                = 587,

    @enable_ssl          = 1,

    @username            = 'sqlserver@firma.de',

    @password            = 'MailPassword!';  -- In Produktion: Key Vault!

EXEC msdb.dbo.sysmail_add_profile_sp

    @profile_name = 'DBA-Alert-Profile',

    @description  = 'Hauptprofil für DBA-Benachrichtigungen';

EXEC msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = 'DBA-Alert-Profile',

    @account_name = 'DBA-Alerts',

    @sequence_number = 1;

-- Test:

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'DBA-Alert-Profile',

    @recipients   = 'dba@firma.de',

    @subject      = 'Database Mail Test',

    @body         = 'Database Mail funktioniert.';

-- Operator anlegen (Empfänger für alle Alerts):

EXEC msdb.dbo.sp_add_operator

    @name              = 'DBA-Team',

    @enabled           = 1,

    @email_address     = 'dba@firma.de; it-leiter@firma.de',

    @pager_days        = 127;   -- 127 = alle Tage

-- Standard-Alerts für SQL Server Schweregrade:

-- Schweregrad 19–25 = sehr kritische Fehler (Korruption, OOM, fatale Fehler)

EXEC msdb.dbo.sp_add_alert

    @name                = 'SQL Severity 19+',

    @severity            = 19,

    @enabled             = 1,

    @delay_between_responses = 60,

    @notification_message = 'Kritischer SQL Server Fehler — Schweregrad >= 19';

EXEC msdb.dbo.sp_add_notification

    @alert_name   = 'SQL Severity 19+',

    @operator_name = 'DBA-Team',

    @notification_method = 1;  -- 1 = E-Mail

-- Spezifische Fehler-Alert:

-- Fehler 9002: Transaktionslog voll

EXEC msdb.dbo.sp_add_alert

    @name              = 'Transaktionslog voll (9002)',

    @message_id        = 9002,

    @enabled           = 1,

    @delay_between_responses = 30,

    @notification_message = 'Transaktionslog einer Datenbank ist voll!';

EXEC msdb.dbo.sp_add_notification

    @alert_name    = 'Transaktionslog voll (9002)',

    @operator_name = 'DBA-Team',

    @notification_method = 1;

-- Weitere wichtige Fehler-IDs für Alerts:

-- 823: I/O-Fehler (mögliche Hardware-Korruption)

-- 824: logischer Konsistenzfehler

-- 825: Lesefehler nach Wiederholung

-- 1105: Dateigruppe voll

-- 3041: Backup fehlgeschlagen

-- SQL Agent Job: täglicher Gesundheits-Check mit Alert-E-Mail

-- Job-Schritt: "DBA_Daily_Health_Check"

DECLARE @Warnungen NVARCHAR(MAX) = '';

-- 1. Backup-Check:

IF EXISTS (

    SELECT 1 FROM sys.databases d

    LEFT JOIN (

        SELECT database_name, MAX(backup_finish_date) AS Letztes

        FROM msdb.dbo.backupset WHERE type = 'D' GROUP BY database_name

    ) b ON d.name = b.database_name

    WHERE d.database_id > 4

      AND d.state_desc = 'ONLINE'

      AND (b.Letztes IS NULL OR DATEDIFF(HOUR, b.Letztes, GETDATE()) > 26)

)

    SET @Warnungen += '⚠ BACKUP: Datenbanken ohne aktuelles Full Backup!' + CHAR(13);

-- 2. Festplattenplatz-Check:

DECLARE @FreeSpace TABLE (Drive NVARCHAR(3), FreeSpace INT);

INSERT @FreeSpace EXEC xp_fixeddrives;

IF EXISTS (SELECT 1 FROM @FreeSpace WHERE FreeSpace < 20480)

    SET @Warnungen += '⚠ SPEICHER: Laufwerk hat weniger als 20 GB frei!' + CHAR(13);

-- 3. Fehlgeschlagene Jobs (letzte 24h):

IF EXISTS (

    SELECT 1 FROM msdb.dbo.sysjobhistory jh

    JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id

    WHERE jh.run_status = 0 AND jh.step_id = 0

      AND msdb.dbo.agent_datetime(jh.run_date, jh.run_time)

          > DATEADD(HOUR, -24, GETDATE())

)

    SET @Warnungen += '⚠ JOBS: SQL Agent Jobs in den letzten 24h fehlgeschlagen!' + CHAR(13);

-- 4. Transaktionslog-Check:

DECLARE @LogData TABLE (

    DatabaseName NVARCHAR(128), LogSize DECIMAL(10,2),

    LogUsed DECIMAL(10,2), Status INT

);

INSERT @LogData EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS');

IF EXISTS (SELECT 1 FROM @LogData WHERE LogUsed > 80)

    SET @Warnungen += '⚠ TRANSAKTIONSLOG: Log-Nutzung > 80%!' + CHAR(13);

-- 5. PLE-Check:

IF EXISTS (

    SELECT 1 FROM sys.dm_os_performance_counters

    WHERE object_name LIKE '%Buffer Manager%'

      AND counter_name = 'Page life expectancy'

      AND cntr_value < 300

)

    SET @Warnungen += '⚠ MEMORY: Page Life Expectancy < 300 Sekunden!' + CHAR(13);

-- Alert senden wenn Warnungen vorhanden:

IF LEN(@Warnungen) > 0

    EXEC msdb.dbo.sp_send_dbmail

        @profile_name = 'DBA-Alert-Profile',

        @recipients   = 'dba@firma.de',

        @subject      = '⚠ SQL Server Health Alert — ' +

                        @@SERVERNAME + ' — ' +

                        CONVERT(NVARCHAR, GETDATE(), 104),

        @body         = 'Folgende Warnungen wurden erkannt:' + CHAR(13) +

                        CHAR(13) + @Warnungen + CHAR(13) +

                        'Server: ' + @@SERVERNAME + CHAR(13) +

                        'Zeitpunkt: ' + CONVERT(NVARCHAR, GETDATE(), 120),

        @importance   = 'High';

-- Extended Events Session für Deadlocks und Blocking (> 15 Sek.):

CREATE EVENT SESSION [DBA_Monitoring] ON SERVER

ADD EVENT sqlserver.blocked_process_report (

    WHERE (duration >= 15000000)  -- 15 Sekunden in Mikrosekunden

),

ADD EVENT sqlserver.xml_deadlock_report,

ADD EVENT sqlserver.sql_statement_completed (

    WHERE (duration >= 5000000)   -- Statements > 5 Sek. loggen

    ACTION (sqlserver.sql_text, sqlserver.username,

            sqlserver.database_name, sqlserver.client_hostname)

)

ADD TARGET package0.ring_buffer (

    SET max_memory = 51200,       -- 50 MB Ring Buffer

        max_events_limit = 1000

),

ADD TARGET package0.event_file (

    SET filename = N'D:\XE_Logs\DBA_Monitoring.xel',

        max_file_size = 50,       -- Max 50 MB je Datei

        max_rollover_files = 10   -- Max 10 Dateien (500 MB gesamt)

)

WITH (

    MAX_DISPATCH_LATENCY = 30 SECONDS,

    STARTUP_STATE = ON             -- Session startet automatisch mit SQL Server

);

ALTER EVENT SESSION [DBA_Monitoring] ON SERVER STATE = START;

-- Tabelle für historische Performance-Daten:

CREATE TABLE dbo.Performance_History (

    SnapshotID        INT IDENTITY PRIMARY KEY,

    SnapshotZeit      DATETIME2        NOT NULL DEFAULT SYSDATETIME(),

    Servername        NVARCHAR(128)    NOT NULL DEFAULT @@SERVERNAME,

    -- Memory:

    PLE_Sekunden      INT              NULL,

    RAM_Genutzt_MB    INT              NULL,

    Memory_Grants_Pending INT          NULL,

    -- CPU (aus Ring Buffer):

    SQL_CPU_Utilization_Pct INT        NULL,

    -- Connections:

    Aktive_Sessions   INT              NULL,

    Blockierte_Sessions INT            NULL,

    -- TempDB:

    TempDB_Auslastung_Pct DECIMAL(5,1) NULL,

    -- Log:

    Max_Log_Auslastung_Pct DECIMAL(5,1) NULL,

    -- Top Wait Type:

    Top_Wait_Type     NVARCHAR(100)    NULL,

    Top_Wait_Sek      DECIMAL(12,1)    NULL

);

-- Snapshot-Prozedur (täglich von SQL Agent aufgerufen):

CREATE OR ALTER PROCEDURE dbo.sp_Performance_Snapshot AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @PLE INT, @RAM_MB INT, @Grants INT,

            @CPU_Pct INT, @Sessions INT, @Blocked INT,

            @TempDB_Pct DECIMAL(5,1), @MaxLog_Pct DECIMAL(5,1),

            @TopWait NVARCHAR(100), @TopWait_Sek DECIMAL(12,1);

    -- PLE:

    SELECT @PLE = cntr_value

    FROM sys.dm_os_performance_counters

    WHERE object_name LIKE '%Buffer Manager%'

      AND counter_name = 'Page life expectancy';

    -- RAM:

    SELECT @RAM_MB = physical_memory_in_use_kb / 1024

    FROM sys.dm_os_process_memory;

    -- Memory Grants:

    SELECT @Grants = cntr_value

    FROM sys.dm_os_performance_counters

    WHERE object_name LIKE '%Memory Manager%'

      AND counter_name = 'Memory Grants Pending';

    -- Sessions:

    SELECT @Sessions = COUNT(*),

           @Blocked  = SUM(CASE WHEN r.blocking_session_id > 0 THEN 1 ELSE 0 END)

    FROM sys.dm_exec_sessions s

    LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id

    WHERE s.is_user_process = 1;

    -- Top Wait Type (kumulativ seit Restart — für Trend-Analyse):

    SELECT TOP 1

        @TopWait = wait_type,

        @TopWait_Sek = wait_time_ms / 1000.0

    FROM sys.dm_os_wait_stats

    WHERE wait_type NOT IN (

        'SLEEP_TASK','LAZYWRITER_SLEEP','LOGMGR_QUEUE',

        'REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE',

        'SERVER_IDLE_CHECK','SLEEP_DBSTARTUP','WAITFOR',

        'XE_DISPATCHER_WAIT','XE_TIMER_EVENT'

    )

    ORDER BY wait_time_ms DESC;

    INSERT INTO dbo.Performance_History (

        PLE_Sekunden, RAM_Genutzt_MB, Memory_Grants_Pending,

        Aktive_Sessions, Blockierte_Sessions,

        Top_Wait_Type, Top_Wait_Sek

    )

    VALUES (

        @PLE, @RAM_MB, @Grants,

        @Sessions, @Blocked,

        @TopWait, @TopWait_Sek

    );

END;

-- Vollständiger Backup-Überwachungsbericht:

CREATE OR ALTER PROCEDURE dbo.sp_Backup_Monitor AS

BEGIN

    SET NOCOUNT ON;

    -- Datenbanken ohne aktuelles Backup:

    SELECT

        d.name                              AS Datenbank,

        d.recovery_model_desc               AS Recovery_Modell,

        MAX(CASE WHEN b.type='D'

                 THEN b.backup_finish_date END) AS Letztes_Full,

        MAX(CASE WHEN b.type='I'

                 THEN b.backup_finish_date END) AS Letztes_Diff,

        MAX(CASE WHEN b.type='L'

                 THEN b.backup_finish_date END) AS Letztes_Log,

        DATEDIFF(HOUR,

            MAX(CASE WHEN b.type='D' THEN b.backup_finish_date END),

            GETDATE())                      AS Stunden_Seit_Full,

        CASE

            WHEN MAX(CASE WHEN b.type='D' THEN b.backup_finish_date END)

                 IS NULL                                 THEN 'KEIN BACKUP JEMALS'

            WHEN DATEDIFF(HOUR,

                 MAX(CASE WHEN b.type='D' THEN b.backup_finish_date END),

                 GETDATE()) > 25                         THEN 'ÜBERFÄLLIG'

            WHEN d.recovery_model_desc = 'FULL'

                 AND DATEDIFF(HOUR,

                     MAX(CASE WHEN b.type='L' THEN b.backup_finish_date END),

                     GETDATE()) > 2                      THEN 'LOG BACKUP FEHLT'

            ELSE 'OK'

        END AS Status

    FROM sys.databases d

    LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name

    WHERE d.database_id > 4

      AND d.state_desc = 'ONLINE'

    GROUP BY d.name, d.recovery_model_desc

    HAVING

        MAX(CASE WHEN b.type='D' THEN b.backup_finish_date END) IS NULL

        OR DATEDIFF(HOUR,

           MAX(CASE WHEN b.type='D' THEN b.backup_finish_date END),

           GETDATE()) > 25

        OR (d.recovery_model_desc = 'FULL'

            AND DATEDIFF(HOUR,

                MAX(CASE WHEN b.type='L' THEN b.backup_finish_date END),

                GETDATE()) > 2)

    ORDER BY Stunden_Seit_Full DESC;

END;

-- Always-On Health State Snapshot (für History-Tabelle):

CREATE TABLE dbo.AlwaysOn_History (

    SnapshotZeit          DATETIME2     NOT NULL DEFAULT SYSDATETIME(),

    AG_Name               NVARCHAR(128) NOT NULL,

    Replica_Server        NVARCHAR(128) NOT NULL,

    Rolle                 NVARCHAR(20)  NOT NULL,

    Sync_Health           NVARCHAR(50)  NULL,

    Verbindung            NVARCHAR(50)  NULL,

    Max_Lag_Sekunden      INT           NULL

);

-- Täglicher Insert in History-Tabelle:

INSERT INTO dbo.AlwaysOn_History (AG_Name, Replica_Server, Rolle,

    Sync_Health, Verbindung, Max_Lag_Sekunden)

SELECT

    ag.name,

    ar.replica_server_name,

    ars.role_desc,

    ars.synchronization_health_desc,

    ars.connected_state_desc,

    MAX(adbrs.secondary_lag_seconds)

FROM sys.availability_groups ag

JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id

JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id

LEFT JOIN sys.dm_hadr_database_replica_states adbrs ON ar.replica_id = adbrs.replica_id

GROUP BY ag.name, ar.replica_server_name, ars.role_desc,

         ars.synchronization_health_desc, ars.connected_state_desc;

-- Alert wenn Replikation > 5 Minuten hinterher:

IF EXISTS (

    SELECT 1 FROM sys.dm_hadr_database_replica_states

    WHERE secondary_lag_seconds > 300

)

    EXEC msdb.dbo.sp_send_dbmail

        @profile_name = 'DBA-Alert-Profile',

        @recipients   = 'dba@firma.de',

        @subject      = '⚠ Always-On Replikationsverzögerung > 5 Min — ' + @@SERVERNAME,

        @body         = 'Bitte sys.dm_hadr_database_replica_states prüfen.',

        @importance   = 'High';

-- Server Audit: alle sicherheitsrelevanten Ereignisse loggen

CREATE SERVER AUDIT [DBA_Security_Audit]

TO FILE (

    FILEPATH      = N'D:\Audit_Logs\',

    MAXSIZE       = 100 MB,

    MAX_ROLLOVER_FILES = 30,

    RESERVE_DISK_SPACE = OFF

)

WITH (

    QUEUE_DELAY     = 1000,

    ON_FAILURE      = CONTINUE,

    AUDIT_GUID      = NEWID()

);

-- Audit-Spezifikation: welche Ereignisse erfassen?

CREATE SERVER AUDIT SPECIFICATION [DBA_Security_Audit_Spec]

FOR SERVER AUDIT [DBA_Security_Audit]

ADD (FAILED_LOGIN_GROUP),              -- Fehlgeschlagene Logins

ADD (SUCCESSFUL_LOGIN_GROUP),          -- Erfolgreiche Logins (optional)

ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), -- Rollen-Änderungen

ADD (AUDIT_CHANGE_GROUP),              -- Änderungen am Audit selbst

ADD (SERVER_PERMISSION_CHANGE_GROUP)   -- Berechtigungsänderungen

WITH (STATE = ON);

ALTER SERVER AUDIT [DBA_Security_Audit] WITH (STATE = ON);

-- Datenbank-Audit-Spezifikation (DDL-Änderungen in Produktions-DB):

USE DWH_Prod;

CREATE DATABASE AUDIT SPECIFICATION [DWH_DDL_Audit]

FOR SERVER AUDIT [DBA_Security_Audit]

ADD (SCHEMA_OBJECT_CHANGE_GROUP),     -- ALTER/DROP TABLE, VIEW, PROC

ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), -- Benutzer anlegen/löschen

ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP) -- Rollen-Mitgliedschaft ändern

WITH (STATE = ON);

-- Fehlgeschlagene Logins der letzten 24 Stunden aus Audit-Log:

SELECT

    event_time                          AS Zeitpunkt,

    server_principal_name               AS Login_Name,

    client_ip                           AS Client_IP,

    application_name                    AS Anwendung,

    additional_information.value(

        '(//Error/text)[1]', 'NVARCHAR(512)')  AS Fehlertext

FROM sys.fn_get_audit_file(

    'D:\Audit_Logs\*.sqlaudit', NULL, NULL)

WHERE action_id = 'LGIF'   -- Login Failed

  AND event_time > DATEADD(HOUR, -24, GETDATE())

ORDER BY event_time DESC;

-- Alert: > 10 fehlgeschlagene Logins vom gleichen Client in 1 Stunde:

IF EXISTS (

    SELECT client_ip

    FROM sys.fn_get_audit_file('D:\Audit_Logs\*.sqlaudit', NULL, NULL)

    WHERE action_id = 'LGIF'

      AND event_time > DATEADD(HOUR, -1, GETDATE())

    GROUP BY client_ip

    HAVING COUNT(*) > 10

)

    EXEC msdb.dbo.sp_send_dbmail

        @profile_name = 'DBA-Alert-Profile',

        @recipients   = 'dba@firma.de; security@firma.de',

        @subject      = '🔐 SICHERHEIT: Brute-Force-Verdacht auf ' + @@SERVERNAME,

        @body         = 'Mehr als 10 fehlgeschlagene Login-Versuche von einem Client in 1 Stunde.',

        @importance   = 'High';

-- Datenbankwachstum über die letzten 30 Tage aus History-Tabelle:

WITH GrowthAnalysis AS (

    SELECT

        DB_NAME(database_id)             AS Datenbank,

        type_desc,

        MIN(size * 8.0 / 1024)           AS Groesse_MB_Min,

        MAX(size * 8.0 / 1024)           AS Groesse_MB_Max,

        MAX(size * 8.0 / 1024)

            - MIN(size * 8.0 / 1024)     AS Wachstum_MB_30T,

        COUNT(DISTINCT CAST(

            -- Berechnung aus dm_io_virtual_file_stats-Snapshots

            SnapshotZeit AS DATE))        AS Messpunkte

    FROM dbo.DB_Size_History   -- Eigene History-Tabelle (siehe unten)

    WHERE SnapshotZeit > DATEADD(DAY, -30, GETDATE())

    GROUP BY database_id, type_desc

)

SELECT

    Datenbank,

    type_desc,

    CAST(Groesse_MB_Max AS DECIMAL(10,1))    AS Aktuell_MB,

    CAST(Wachstum_MB_30T AS DECIMAL(10,1))   AS Wachstum_30T_MB,

    CAST(Wachstum_MB_30T * 12 AS DECIMAL(10,1)) AS Hochrechnung_1Jahr_MB,

    -- Wann ist die Datei voll bei aktuellem Wachstum? (Vereinfacht)

    CAST(Groesse_MB_Max / NULLIF(Wachstum_MB_30T / 30.0, 0)

         AS INT)                             AS Tage_Bis_Verdopplung

FROM GrowthAnalysis

WHERE Wachstum_MB_30T > 100   -- Nur signifikantes Wachstum anzeigen

ORDER BY Wachstum_MB_30T DESC;

-- Wöchentlicher Bericht: Speicher, TempDB, Log-Nutzung, Top-Wartetypen

CREATE OR ALTER PROCEDURE dbo.sp_Weekly_Capacity_Report AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @Bericht NVARCHAR(MAX) = '';

    -- 1. Datenbankgrößen:

    SET @Bericht += '=== DATENBANKGRÖSSEN ===' + CHAR(13);

    SELECT @Bericht += d.name + ': ' +

        CAST(CAST(SUM(mf.size * 8.0 / 1024) AS DECIMAL(10,1)) AS NVARCHAR)

        + ' MB' + CHAR(13)

    FROM sys.databases d

    JOIN sys.master_files mf ON d.database_id = mf.database_id

    WHERE d.database_id > 4

    GROUP BY d.name

    ORDER BY SUM(mf.size) DESC;

    -- 2. Performance-Trend (PLE, Blocked Sessions aus History):

    SET @Bericht += CHAR(13) + '=== PERFORMANCE-TREND (7 Tage) ===' + CHAR(13);

    SELECT @Bericht +=

        'Avg PLE: ' + CAST(CAST(AVG(PLE_Sekunden) AS DECIMAL(8,0)) AS NVARCHAR) +

        ' | Max Blocked: ' + CAST(MAX(Blockierte_Sessions) AS NVARCHAR) +

        ' | Avg Sessions: ' + CAST(CAST(AVG(Aktive_Sessions) AS DECIMAL(5,0)) AS NVARCHAR)

        + CHAR(13)

    FROM dbo.Performance_History

    WHERE SnapshotZeit > DATEADD(DAY, -7, GETDATE());

    EXEC msdb.dbo.sp_send_dbmail

        @profile_name = 'DBA-Alert-Profile',

        @recipients   = 'dba@firma.de; it-leiter@firma.de',

        @subject      = 'SQL Server Wochenbericht — ' + @@SERVERNAME

                        + ' — KW ' + CAST(DATEPART(ISO_WEEK, GETDATE()) AS NVARCHAR),

        @body         = @Bericht;

END;

MONITORING-DASHBOARD ARCHITEKTUR:

══════════════════════════════════════════════════════════════

SQL SERVER (Quellen):

  dbo.Performance_History    → Tägl. Snapshots (PLE, Sessions, Waits)

  dbo.ETL_Log               → ETL-Job-Status

  dbo.AlwaysOn_History      → Replikationsstatus-Verlauf

  dbo.DB_Size_History       → Datenbankgrößen-Verlauf

  msdb.dbo.sysjobhistory    → SQL Agent Job-Historie

  msdb.dbo.backupset        → Backup-Historie

      ↓ Power BI DirectQuery oder täglicher Import (06:30 Uhr)

POWER BI DATASET:

  Alle History-Tabellen als DirectQuery-Quelle

  Refresh: alle 15 Minuten (für Echtzeit-Übersicht)

DASHBOARD-SEITEN:

  Seite 1: Server Health Overview (GF/IT-Leitung)

  Seite 2: Performance Detail (DBA)

  Seite 3: Job & Backup Status (DBA)

  Seite 4: Kapazitäts-Trend (IT-Leitung/Planung)

══════════════════════════════════════════════════════════════

SERVER HEALTH OVERVIEW (Hauptseite):

══════════════════════════════════════════════════════════════

HEADER: Servername | Letzte Aktualisierung | Uptime

KPI-CARDS (Ampel-System):

  ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐

  │   PLE    │ │ Blockier.│ │Job-Fehler│ │Backup OK │

  │ 2.841 s  │ │    0     │ │    2     │ │  14/15   │

  │ ● Grün  │ │ ● Grün  │ │ ● Rot   │ │ ● Gelb  │

  └──────────┘ └──────────┘ └──────────┘ └──────────┘

TREND-CHARTS (letzte 30 Tage):

  Links:  PLE-Verlauf (Liniendiagramm) — Ziel > 1.000

  Rechts: Aktive Sessions + Blockierungen (Kombi-Chart)

UNTERE ZONE:

  Tabelle: SQL Agent Jobs — letzte 24h (Grün/Rot-Ampel)

  Tabelle: Backups — Status je Datenbank (Ampel + Zeitstempel)

══════════════════════════════════════════════════════════════

-- Aktueller PLE-Status (Ampelfarbe):

PLE Ampelfarbe =

VAR _PLE = LASTNONBLANK(Performance_History[PLE_Sekunden], 1)

RETURN

    SWITCH(TRUE(),

        _PLE >= 1000, "#107C41",   -- Grün

        _PLE >= 300,  "#F4A226",   -- Gelb

        "#C00000"                   -- Rot

    )

-- Letzte Backup-Überwachung:

Datenbanken ohne aktuelles Backup =

CALCULATE(

    COUNTROWS(Backup_Status),

    Backup_Status[Status] IN { "ÜBERFÄLLIG", "KEIN BACKUP!" }

)

-- Jobs fehlgeschlagen (letzte 24h):

Jobs fehlgeschlagen 24h =

CALCULATE(

    COUNTROWS(Job_History),

    Job_History[run_status] = 0,

    Job_History[Zeitpunkt] > NOW() - 1

)

-- PLE 7-Tage-Trend (steigend oder fallend):

PLE Trend =

VAR _Aktuell = CALCULATE(AVERAGE(Performance_History[PLE_Sekunden]),

                  DATESINPERIOD(Performance_History[SnapshotZeit], TODAY(), -3, DAY))

VAR _Vorwoche = CALCULATE(AVERAGE(Performance_History[PLE_Sekunden]),

                  DATESINPERIOD(Performance_History[SnapshotZeit],

                                TODAY() - 7, -3, DAY))

RETURN

    IF(_Aktuell > _Vorwoche, "↑ Verbesserung",

       IF(_Aktuell < _Vorwoche * 0.9, "↓ Verschlechterung", "↔ Stabil"))

VOR DEM START

Klären Sie vorab: Ist Database Mail auf dem Server konfiguriert? Wenn nicht, ist das die erste Aufgabe in Woche 1 — ohne E-Mail-Versand sind automatische Alerts nicht möglich. Reservieren Sie außerdem Speicherplatz für Audit-Logs und Extended-Events-Dateien (Empfehlung: separates Laufwerk, mindestens 50 GB).

ERGEBNIS NACH 30 TAGEN

Ihr Ergebnis nach 30 Tagen: Automatische Alerts für alle kritischen SQL-Server-Ereignisse (Backups, Blockierungen, Festplatte, Fehler-IDs, Security), täglich historisierte Performance-Daten für Trend-Analyse, ein Power-BI-Dashboard mit Echtzeit-Servergesundheit und 30-Tage-Trends, ein SQL Server Audit für alle DDL-Änderungen und fehlgeschlagene Logins — und ein Playbook, das jeden im Team befähigt auf Alerts zu reagieren.

SQL Server Monitoring-Setup

Vollständige Einrichtung eines proaktiven Monitoring-Systems: Database Mail, SQL Agent Alerts, Extended Events, Performance History, Backup-Überwachung, Security Audit und Power BI Dashboard. Scope: 3–5 Tage.

SQL Server DB Health Check

Professioneller Audit Ihres SQL Servers — Managementreport mit Performance-Analyse, Monitoring-Bewertung, Sicherheits-Check und priorisiertem Maßnahmenplan. Scope: 3–5 Tage.

Query-Tuning Session

1-Tages-Session: DMV-Diagnose, Execution-Plan-Analyse, Rewrite-Empfehlungen und Index-Strategie für Ihre Top-Performance-Probleme.

SQL Server Performance-Projekt

Umfassendes Performance-Tuning über 4–8 Wochen: Monitoring, Tuning, Index-Strategie, Query Store und vollständige Dokumentation aller Maßnahmen.

Interim IT-Management

Übernahme der IT-Steuerung auf Zeit — Budgetplanung, Dienstleister-Management, strategische IT-Ausrichtung.