Script to clear SQL Transactions logs

28 Oct

Script to clear SQL Transactions Logs

Did you know that when you backup a SQL database with Backup Exec (with the SQL Agent) the transaction log is not cleared? This means that if you use the full recovery model your transaction log keeps growing and growing. I tested this with Backup Exec v11D and you can only create a seperate scheduled job to backup the transactions logs but not one to just clear it or truncate it after successfull backup.

Therefore I made a VBS script that clears SQL transactions logs, an option would be to schedule this as a post backup job.

This is the script:

' ClrSqlLogs.vbs version 1.0
' Author: Remko Weijnen (r dot weijnen at gmail.com)

' Script to clear SQL Transaction logs
' Parameters:
' strServer = SQL Server name
' strDatabase = Database name
' strLogName = The name of the log file
'
' Script starts here:

' Force explicit declaration 
Option Explicit

' Make variables public so they can be accessed from the sub
Public objCon, objCom

' Create database connection object
Set objCon = CreateObject("ADODB.Connection")

' Create database command object
Set objCom = CreateObject("ADODB.Command")

' Clear logs
Call ClearLogs("MyServer", "MyDatabase", "MyDatabase_log")
Call ClearLogs("MyOtherServer", "MyOtherDatabase", "MyOtherDatabase_log")

' Cleanup
Set objCom = Nothing
Set objCon = Nothing

' Exit the script (just to be sure that code below is never Executed
WScript.Quit(0)

' The procedure that clears the log
Sub ClearLogs(strServer, strDatabase, strLogName)
   Dim strConnection
   ' Build connection string
   strConnection = "Driver={SQL Native Client};Server=" & strServer & ";Database=" & strDatabase & ";Trusted_Connection=yes;"

   WScript.Echo("Connection String: " & strConnection)
   ' Open Connection to SQL Server and Database
   ' Trusted connection so the currently logged-on account needs permissions
   WScript.Echo("Open connection")
   objCon.Open(strConnection)

   ' Connect AdoCommand to the Active connection
   objCom.ActiveConnection = objCon
   
   ' Execute DBCC SHRINKFILE
   objCom.CommandText = "DBCC SHRINKFILE(" & strLogName & ", 1)"
   WScript.Echo(vbTab & objCom.CommandText)
   objCom.Execute
   
   ' Truncate Log
'   WScript.Echo(vbTab & "BACKUP LOG WITH TRUNCAT")
   objCom.CommandText = "BACKUP LOG " & strDatabase & " WITH TRUNCATE_ONLY"
   WScript.Echo(vbTab & objCom.CommandText)
   objCom.Execute

   ' Execute DBCC SHRINKFILE again  
   objCom.CommandText = "DBCC SHRINKFILE(" & strLogName & ", 1)"
   objCom.Execute
   WScript.Echo("Close connection")
   objCon.Close
End Sub

' ClrSqlLogs.vbs version 1.0

' Author: Remko Weijnen (r dot weijnen at gmail.com)

' Script to clear SQL Transaction logs

' Parameters:

' strServer = SQL Server name

' strDatabase = Database name

' strLogName = The name of the log file

' Script starts here:

' Force explicit declaration

Option Explicit

' Make variables public so they can be accessed from the sub

Public objCon, objCom

' Create database connection object

Set objCon = CreateObject("ADODB.Connection")

' Create database command object

Set objCom = CreateObject("ADODB.Command")

' Clear logs

Call ClearLogs("MyServer", "MyDatabase", "MyDatabase_log")

Call ClearLogs("MyOtherServer", "MyOtherDatabase", "MyOtherDatabase_log")

' Cleanup

Set objCom = Nothing

Set objCon = Nothing

' Exit the script (just to be sure that code below is never Executed

WScript.Quit(0)

' The procedure that clears the log

Sub ClearLogs(strServer, strDatabase, strLogName)

Dim strConnection

' Build connection string

strConnection = "Driver={SQL Native Client};Server=" & strServer & ";Database=" & strDatabase & ";Trusted_Connection=yes;"

WScript.Echo("Connection String: " & strConnection)

' Open Connection to SQL Server and Database

' Trusted connection so the currently logged-on account needs permissions

WScript.Echo("Open connection")

objCon.Open(strConnection)

' Connect AdoCommand to the Active connection

objCom.ActiveConnection = objCon

' Execute DBCC SHRINKFILE

objCom.CommandText = "DBCC SHRINKFILE(" & strLogName & ", 1)"

WScript.Echo(vbTab & objCom.CommandText)

objCom.Execute

' Truncate Log

' WScript.Echo(vbTab & "BACKUP LOG WITH TRUNCAT")

objCom.CommandText = "BACKUP LOG " & strDatabase & " WITH TRUNCATE_ONLY"

WScript.Echo(vbTab & objCom.CommandText)

objCom.Execute

' Execute DBCC SHRINKFILE again

objCom.CommandText = "DBCC SHRINKFILE(" & strLogName & ", 1)"

objCom.Execute

WScript.Echo("Close connection")

objCon.Close

End Sub

ClrSQLLogs.zip (2997 downloads )

Please consider donating something (even a small amount is ok) to support this site and my work:

Filed under: script, SQL Server

2 Responses for "Script to clear SQL Transactions logs"

Remko November 8th, 2007 at 14:44 1
WordPress managed to mess up the pasted code again, I repasted it in again. (code in download was correct offcourse)
rublin December 28th, 2010 at 14:12 2
Hello.
Nice script. But I use script to backup ALL bases (http://gallery.technet.microsoft.com/scriptcenter/ru-ru/a59b4d2b-0d85-41e1-8a6d-8d4548d1d7d9), and I need script to shrink ALL transact logs. Can you help me?