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…
<pre>Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ‚-‚.</pre>

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 arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

Übernahme von MSSQL-Datenbank-Servern in unsere Verantwortung

Unser Arbeitgeber sieht eine seiner Kernkompetenzen im Bereich Outsourcing, dadurch kommen immer wieder neue Umgebungen, Installationen und Situationen rund um die Betreuung von MSSQL-Servern auf uns zu.
Somit stehen wir fast täglich vor der Aufgabe diese neuen Server erstmal zu analysieren und überprüfen, damit wir einen groben Überblick haben. Hierzu gehören Themen wie:

  • Welche Edition ist installiert? Standard, Enterprise? 32 oder 64 Bit?
  • Wieviele Instanzen gibt es?
  • Wie groß sind die Datenbanken?
  • Wo liegen die Datenbanken? Liegen die vielleicht sogar auf dem C-Laufwerk?
  • Wieviel Plattenplatz steht noch zur Verfügung?
  • Wann ist das letzte Backup gelaufen und womit wird gesichert?
  • Welche Wartungspläne gibt es? Wann laufen die?
  • Gibt es Auffälligkeiten in den Error-/System-Logs?
  • Welche User verfügen über „sysadmin“-Rechte?
  • Werden unsere „Best-Practise“-Standards eingehalten?
  • Können wir unsere Security-Baseline einhalten oder müssen wir Anpassungen durchführen?

Diese Fragestellungen tauchen jedes Mal wieder auf und jedes Mal benötigt man mehrere Stunden für einen Server, um sich die relevanten Punkte einzeln zusammenzuklicken. Dann müssen die entsprechend dokumentiert und ausgewertet werden. Jede MSSQL-Datenbank-Installation bzw Instanz muss dann aus diesen gesammelten Daten in eine CMDB eingepflegt werden.

Wie kann man solche wiederkehrenden Tätigkeiten vereinfachen?
Kann man das nicht irgendwie skripten?

Kann man bestimmt! Da wir aber nicht unbedingt die Experten in Sachen TSQL-Entwicklung sind, andere bestimmt ähnliche Themen haben und sich schon mal Gedanken gemacht haben, bemühten wir eine bekannte Suchmaschine… Zahlreiche Beiträge, Ideen und Skripte gelesen und ausprobiert.

Brentozar.com - sp_BLITZ - MSSQL Server Takeover Script
Brentozar.com - sp_BLITZ - SQL Server Takeover Script

Das einfachste und beste, was uns im Zusammenhang mit solchen „Betriebsübernahmen“ in die Hände kam, war die StoredProcedure „sp_blitz“ von Brent Ozar.
Brent und sein Team haben hier ein (aus unserer Sicht) super Skript geschrieben, es analysiert weitestgehend alle relevanten Themen wie Backup, Performance und Security und liefert zu jeder gefundenen „Auffälligkeit“ eine ausführliche und gut dokumentierte Beschreibung und Lösungsansätze. Mittels dieser ersten Analyse, welche wir in unserem Sharepoint ablegen, erhalten wir einen großen Teil an Informationen und Hinweise auf mögliche bestehende Probleme, hieraus erstellen wir dann einen ToDo-Plan für jeden MSSQL-Datenbankserver, den wir dann mit dem zuständigen Ansprechpartner besprechen und zur Umsetzung einen „Fahrplan“ aufsetzen.

Für alle anderen für uns wichtigen Punkte, die wir für unsere CMDB benötigen, ermitteln wir ebenfalls per Skript, dieses haben wir allerdings selbst geschrieben und ist auf unsere CMDB abgestimmt. Mittels beider Skripte konnten die benötigte Zeit für solch eine Übernahme mit gutem Gewissen von fast einem Tag auf gut 1 Stunde reduzieren.
Auch hier kommt wieder uns grundlegendes Motto zum Tragen: Nur ein ruhiger Tag ist ein guter Tag… möglichst alles automatisiert, standardisiert und dadurch genügend Zeit gewinnen um sich um andere Dinge zu kümmern. (z.B. aktuell MSSQL 2012 – Cluster Installation in den verschiedensten Ausprägungen – mit und/oder ohne AvailibilityGroups usw – darüber berichten wir später)

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.