Einfache Prozedur um die Logfiles einer oder aller Datenbanken zu shrinken

Zweck

ShrinkAllLogs verkleinert die Transaktionslog‑Dateien aller (oder gefilterten) benutzerdefinierten Datenbanken auf eine bestimmte Zielgröße, die einen konfigurierbaren Prozentsatz an freiem Platz belässt. Optional kann vor dem Shrink ein Log‑Backup durchgeführt werden, um Platz freizugeben (nur im vollständigen Recovery‑Modell nötig).

Parameter

Ausgabe

Die Prozedur gibt eine Ergebnistabelle zurück:

Beispiele

-- Alle Datenbanken, 20% freien Platz lassen, Log-Backup nur bei FULL
EXEC dbo.ShrinkAllLogs @TargetPercentFree = 20, @BackupLogBeforeShrink = 1;

-- Nur Datenbank 'SalesDB', maximales Shrink (0% frei), kein Log-Backup
EXEC dbo.ShrinkAllLogs @dbname = 'SalesDB', @TargetPercentFree = 0, @BackupLogBeforeShrink = 0;

USE master;
GO

CREATE OR ALTER PROCEDURE dbo.ShrinkAllLogs
    @dbname SYSNAME = '%',                -- Filter für Datenbanknamen (LIKE)
    @TargetPercentFree TINYINT = 10,      -- Wieviel % freier Platz soll nach Shrink bleiben (0-100)
    @BackupLogBeforeShrink BIT = 1        -- Bei vollständigem Recovery-Modell Log sichern?
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sqlcmd NVARCHAR(MAX) = N'';
    DECLARE @cr CHAR(1) = CHAR(13);
    DECLARE @lf CHAR(1) = CHAR(10);

    -- Tabelle für Start- und Endgrößen
    DECLARE @workvar TABLE (
        dbname SYSNAME,
        dbid INT,
        SizeStartInMB NUMERIC(15,2),
        SizeEndInMB NUMERIC(15,2),
        Differenz AS (SizeStartInMB - SizeEndInMB)
    );

    -- 1. Startgrößen erfassen (aus sys.master_files)
    INSERT INTO @workvar (dbname, dbid, SizeStartInMB)
    SELECT
        d.name,
        d.database_id,
        CAST(SUM(f.size * 8.0 / 1024) AS NUMERIC(15,2)) AS LogSizeMB
    FROM sys.databases d
    INNER JOIN sys.master_files f ON d.database_id = f.database_id
    WHERE d.database_id > 4
      AND d.state = 0                     -- nur ONLINE
      AND d.name LIKE @dbname
      AND f.type = 1                      -- Log-Dateien
    GROUP BY d.name, d.database_id;

    -- Falls keine Datenbank gefunden wurde
    IF NOT EXISTS (SELECT 1 FROM @workvar)
    BEGIN
        PRINT 'Keine passende Online-Datenbank gefunden.';
        RETURN;
    END;

    -- 2. Dynamisches SQL zum Shrink erzeugen (sicher mit QUOTENAME)
    SELECT @sqlcmd = @sqlcmd +
        N'USE ' + QUOTENAME(d.name) + N'; ' +
        CASE WHEN @BackupLogBeforeShrink = 1 AND d.recovery_model_desc = 'FULL'
             THEN N'BACKUP LOG ' + QUOTENAME(d.name) + N' TO DISK = ''NUL''; '   -- Dummy-Backup (nur für Freigabe)
             ELSE N''
        END +
        N'CHECKPOINT; ' +
        N'DBCC SHRINKFILE (' + QUOTENAME(f.name) + N', ' + 
            CAST((CAST(f.size AS BIGINT) * 8 / 1024 * (100 - @TargetPercentFree) / 100) AS VARCHAR(20)) + N') WITH NO_INFOMSGS;' +
        @cr + @lf
    FROM sys.databases d
    INNER JOIN sys.master_files f ON d.database_id = f.database_id
    WHERE d.database_id > 4
      AND d.state = 0
      AND d.name LIKE @dbname
      AND f.type = 1;

    -- 3. Ausführung (mit Fehlerbehandlung)
    BEGIN TRY
        EXEC sp_executesql @sqlcmd;
    END TRY
    BEGIN CATCH
        PRINT 'Fehler beim Shrink: ' + ERROR_MESSAGE();
        -- Hier könnte man auch RAISERROR verwenden
    END CATCH

    -- 4. Endgrößen erfassen
    UPDATE w
    SET w.SizeEndInMB = CAST((
        SELECT SUM(f2.size * 8.0 / 1024)
        FROM sys.master_files f2
        WHERE f2.database_id = w.dbid AND f2.type = 1
    ) AS NUMERIC(15,2))
    FROM @workvar w;

    -- 5. Ergebnis ausgeben
    SELECT
        dbname,
        SizeStartInMB AS [LogSize_MB_Before],
        SizeEndInMB AS [LogSize_MB_After],
        Differenz AS [Shrinked_MB]
    FROM @workvar
    ORDER BY dbname;

    SET NOCOUNT ON;  -- bleibt an
END;
GO