Dynamisches „Shrink Logfile“ für SQL Datenbanken

Ich habe heute aufgrund einer Fehlersituation (Platte nahezu voll) und einem dazugehörigen Backup-Problem (die Kollegen sind noch mit der Lösung beschäftigt) nach einer schnellen und einfachen Möglichkeit gesucht, alle Datenbanken (deren Transaction-Logfiles) meiner MS SQL 2008 R2 – Cluster-Instanz zu shrinken.

Dazu fiel mir dann, dass Thomas mir vor kurzem erst was von solch einem Skript erzählt hatte…

Es war dieses Skript „Dynamic Shrink Log file on server“…

DECLARE @tbl TABLE
(
ID INT IDENTITY(1,1),
DBNAME NVARCHAR(1000),
[FileName] NVARCHAR(1000)
)
INSERT INTO @tbl
SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles ST INNER JOIN Sys.databases SB on ST.dbid = sb.database_id where Size > 100000 AND FileName LIKE '%ldf' and sb.state = 0

DECLARE @MinID INT , @MaxID INT,@DBName NVARCHAR(1000),@FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@SQL NVARCHAR(MAX)

SELECT @MinID = MIN(ID),@MaxID = MAX(ID) FROM @tbl

WHILE(@MinID <=@MaxID)
BEGIN
SELECT @DBName = DBNAME,@FileName=[FileName] FROM @tbl where ID = @MinID

SELECT @RecoveryModel = recovery_model_desc FROM sys.databases where name = @DBName

SELECT @SQL= N'USE ['+ @DBName+']'+CHAR(10)+CHAR(10)
+CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT'ELSE N''END+CHAR(10)+
+N'DBCC SHRINKFILE('+@FileName+',1)'+CHAR(10)
+CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END
+CHAR(10)
PRINT @SQL
EXEC SP_EXECUTESQL @SQL

SELECT @MinID = @MinID +1
END

Da ich mir bereits vorher darüber im Klaren war, dass dies nur eine einmalig Aktion sein würde, brauchte ich mir keine Gedanken über Wartungspläne oder Backup-Chains zu machen.

Auch möchte ich hier nochmal dringend darauf hinweisen, dass es NICHT sinnvoll ist dieses Skript automatisiert und regelmäßig laufen zu lassen. Sollte es zu Situationen kommen, in denen regelmäßig TransactionLogs voll laufen oder unkontrolliert wachsen, so sollte man sich dringend mit der Ursache auseinander setzen. Es gibt sinnvollere und besser geeignete Skripte/Massnahmen (z.B. BulkLogged) um solch einen Wachstum in den Griff zu bekommen. Die zu ergreifenden Massnahmen sind aber immer davon abhängig, welche SQL-Version wird eingesetzt, welche Applikation nutzt die Datenbank, welchen Zweck erfüllt die Datenbank, muss es der Recovery Mode „Full“ sein? Wie sieht die vertragliche Situation aus… etc

Beim erstmaligen Ausführen des obigen Skriptes funktionierte eigentlich alles wie gewünscht, allerdings kann das Skript nicht mit Minus-Zeichen im Dateinamen/Datenbanknamen umgehen und bringt für die jeweilige Datenbank dann einen Fehler…

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '-'.

Diesen Fehler habe ich behoben in dem ich die Variable @Filename im Shrink-Befehl nochmals in Hochkommata gesetzt habe.

+N'DBCC SHRINKFILE('''+@FileName+''',512)'+CHAR(10)

Nun läuft das Skript sauber durch, wobei ich die Restgröße ebenfalls von 1MB auf 512MB erhöht habe, je nach System und Verwendung sollte hier zur Vermeidung ungewollter zuvieler virtueller Logfiles ggfs sogar auf 1024 erhöht werden. Wir richten uns hier nach den Empfehlungen der Best-Practises von Brent Ozar.

Björn Peters

Björn arbeitet auch weiterhin aus Mexiko als Senior Consultant – Microsoft Data Platform und Cloud für die Kramer&Crew in Köln. Auch der Community bleibt er aus der neuen Heimat treu, er engagiert sich auf Data Saturdays oder in unterschiedlichen Foren. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure SQL für Science-Fiction, Backen 😉 und Radfahren.