SQL Transaktionsprotokoll verkleinern (sichern) – SQL 2005 – SQL 2008

Bereits vor 6 Jahren (wie die Zeit vergeht) hatte ich in einem Artikel „SQL 2000 Transaktionsprotokoll verkleinern“ beschrieben wie man ein zu groß gewordenes Transaktionsprotokoll verkleinern kann.

Da die neuere Version SQL 2008 nicht mehr über die Option WITH TRUNCATE_ONLY verfügt, funktioniert die beschrieben Methode nicht mehr.

Nachfolgend möchte ich daher eine alternative Method vorstellen, welche den gleichen  gewünschten Effekt hat.

Das verkleinern des Transaktionsprotokolls.

Damit die Verkleinerung des Transaktionprotokolls nicht zu problemen bei einer eventuellen Rücksicherung führt, würde ich vor dem verkleinern unbedingt eine Sicherung der Datenbank und des Transaktionsprotokolls durchführen. Denn nur die Sicherung des Transaktionsprotokolls ermöglicht auch das spätere verkleinern.

Hier also nun der Transakt SQL Code zur Speicherung der DB und des Logs mit anschließendem Verkleiner der Transaktionsprotokoll Datei.

USE pubs
BACKUP DATABASE pubs TO DISK='C:\SqlDataBackup\pubs.bak' WITH INIT
BACKUP LOG pubs_log TO DISK='C:\SqlDataBackup\pubs_log.bak' WITH INIT
DBCC SHRINKFILE(pubs_Log, 10)
GO

Am besten kann man das in einer Batch Datei (Kommando Stapeldatei) einbauen und z. B. regelmäßig als Tast auf dem Rechner laufen lassen.
Wer das regelmäßig ausführen möchte kann das auch in eine Batch Datei packen und von der Kommandozeile ausführen. Was übrigens auch dann als Task ausgeführt werden kann.

@ECHO OFF
set SQL_UTIL_HOME="%ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\osql"
rem Tragen Sie in der nachfolgenden Zeile den Namen des SQL Server ein z.B. (localhost)
set SQL_SERVERNAME=localhost
rem Tragen Sie in der nachfolgenden Zeile den Namen der zu sichernden Datenbank ein
set SQL_DBNAME=pubs
set SQL_LOGNAME=pubs_log
rem Tragen sie den Namen inkl. Pfad der Backup Datei ein
set SQL_BACKUP_FILE='C:\SqlBackup\pubs.BAK'
set SQL_LOG_FILE='C:\SqlBackup\pubs_log.BAK'
@ECHO ON
@ECHO Datenbank sichern
%SQL_UTIL_HOME% -S%SQL_SERVERNAME% -E -n -Q "BACKUP DATABASE %SQL_DBNAME% TO DISK = %SQL_BACKUP_FILE% WITH INIT;"
@ECHO Protokolldatei sichern
%SQL_UTIL_HOME% -S%SQL_SERVERNAME% -E -n -Q "BACKUP LOG %SQL_DBNAME% TO DISK = %SQL_LOG_FILE% WITH INIT;"
@ECHO Protokolldatei verkleinern
%SQL_UTIL_HOME% -S%SQL_SERVERNAME% -E -n -d%SQL_DBNAME%  -Q "DBCC SHRINKFILE(%SQL_LOGNAME%, 10)"

Wenn man nun noch ein Backup Programm wie Cobian Backup verwendet um die erzeugten Datenbank Backup Dateien auf einen FTP Server zu sichern, hat man eine kostenlose Lösung zur Sicherung der SQL Datenbank.

Ich denke, ich werde bald man in einem neuen Beitrag beschreiben, wie man Dedizierte WEB Server, die man bei einem Hoster stehen hat, mit Hilfe von Batch Dateien und Cobian Backup WEB Seiten inkl. Datenbanken ganz einfach und kostenlos sichern kann.

5 Gedanken zu „SQL Transaktionsprotokoll verkleinern (sichern) – SQL 2005 – SQL 2008“

    1. Hallo Herbert,
      vielen Dank für deine Anmerkungen.
      Du hast natürlich vollkommen recht, dass ohne Backup die LOG Sicherung nicht durchgeführt werden kann, ich hatte doch glatt die Reihenfolge vertauscht. Ich habe die Reihenfolge der Sicherungen, also zuerst DB dann LOG im Batch geändert.
      Warum ich aber osql nicht verwenden soll kann ich nicht ganz nachvollziehen.
      Aber du hast ja die SQLCMD Variante verlinkt.
      Beste Grüße
      HP

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.