Removing Old MSDE Log Databases
Microsoft Internet Security and Acceleration (ISA) Server 2004 includes the MSSQL$MSFW service, which is an instance of the Microsoft SQL Server Desktop Engine (MSDE 2000) that can be used for logging. By default, ISA Server 2004 saves the information in the Microsoft Firewall service and Web proxy logs in MSDE databases. Each database is stored in two files, an .mdf file and an .ldf file, in the %ProgramFiles%\Microsoft ISA Server\ISALogs folder. For each log, ISA Server creates a new database every day.
Normally, ISA Server moves the data in each database that is more than seven days old to a summary database and deletes it. However, for various reasons, old databases may remain and occupy disk space that is needed for logging. When the space allocated for logging is filled, an attempt by the Firewall service to access the sp_batch_insert stored procedure fails, and an error is generated. By default, the Log failure alert shuts down the Firewall service and causes ISA Server to go into lockdown mode. If the action of the Log failure alert is disabled, the Firewall service continues to run when logging failures occur. However, by default, connections are dropped when logging errors occur. This way, if ISA Server does not go into lockdown mode, traffic is blocked when logging failures occur, but logging and the flow of most types of traffic can resume when the condition that causes the logging failures is resolved.
Normal operation with logging can be restored by removing old MSDE log databases to make disk space available for logging. However, old MSDE log files can be deleted only after they are detached from the current server.
The Microsoft Visual Basic® Scripting Edition (VBScript) code in DropOldDbs.vbs (listed later in this document) detaches all ISA Server MSDE log databases older than 30 days that are stored on the local computer. After these databases are detached, the .mdf and .ldf files can be copied to another location and deleted.
Usage:CScript DropOldDbs.vbs
To detach MSDE log databases older than 30 days
Create an instance of the WshShell object and retrieve its FullName property to verify that the script was launched in a Command Prompt window using Cscript.exe. If the script was not launched with Cscript.exe, instruct the user to run the script from a command prompt, display the command to enter, and quit.
Get the name of the local ISA Server computer by calling the ExpandEnvironmentStrings method of the WshShell object to expand the %ComputerName% environment variable.
Create an ADODB Connection object for a database connection and use the name of the local ISA Server computer as the data source to open it.
Create and open an ADODB Recordset object to obtain the collection of databases.
Create an ADODB Command object for running the sp_detach_db stored procedure.
Get the date that was 30 days before the current date and convert it to a string in the YYYYMMDD format.
Iterate through the collection of databases in the Recordset object, find databases that are over 30 days old, and then detach them.
For each database whose name includes a date in the YYYYMMDD format that is more than 30 days before the current date and does not include the string "_log", call a subprocedure that runs the sp_detach_db stored procedure to detach it.
Script Listing: DropOldDbs.vbs
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Copyright (c) Microsoft Corporation. All rights reserved.
' THIS CODE IS MADE AVAILABLE AS IS, WITHOUT WARRANTY OF ANY KIND. THE ENTIRE
' RISK OF THE USE OR THE RESULTS FROM THE USE OF THIS CODE REMAINS WITH THE
' USER. USE AND REDISTRIBUTION OF THIS CODE, WITH OR WITHOUT MODIFICATION, IS
' HEREBY PERMITTED.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This script detaches all ISA Server MSDE log databases older than 30 days.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
Main
Sub Main
Dim shell ' A WshShell object
Dim serverName ' A String
Dim cn ' An ADODB Connection object
Dim rs ' An ADODB Recordset object
Dim cmd ' An ADODB Command object
Dim cmdText ' A String
Dim dbName ' A String
Dim oldDate ' A Variant
Dim oldDateText ' A String
Dim oldDay ' A String
Dim oldMonth ' A String
Dim datePart ' A String
Dim dbCounter ' An Integer
Dim dDbCounter ' An Integer
' If the script was not launched with Cscript, display the usage and quit.
On Error Resume Next
Set shell = CreateObject("WScript.Shell")
If Err.Number <> 0 Then
ReportError "creating the WshShell object."
WScript.Quit
End If
On Error GoTo 0
If LCase(Right(WScript.FullName, 11)) <> "cscript.exe" Then
WScript.Echo "Usage:" & VbCrLf _
& " This script can be run from a command prompt " & VbCrLf _
& " by entering the following command:" & VbCrLf _
& " Cscript " & WScript.ScriptName
WScript.Quit
End If
' Get the name of the local ISA Server computer.
On Error Resume Next
serverName = shell.ExpandEnvironmentStrings("%ComputerName%")
If Err.Number <> 0 Then
ReportError "reading the %ComputerName% environment variable."
WScript.Quit
End If
On Error GoTo 0
' Create and open an ADODB Connection object.
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionTimeout = 25
cn.Provider = "SQLOLEDB"
cn.Properties("Data Source").Value = serverName & "\MSFW"
cn.Properties("Integrated Security").Value = "SSPI"
On Error Resume Next
cn.Open
If Err.Number <> 0 Then
ReportError "opening a database connection."
WScript.Quit
End If
On Error GoTo 0
' Create and open an ADODB Recordset object to get the collection
' of active databases.
Set rs = CreateObject("ADODB.Recordset")
cmdText = "SELECT name, size FROM sysaltfiles WHERE name like 'ISALOG_%'"
rs.Open cmdText, cn
' Create an ADODB Command object.
On Error Resume Next
Set cmd = CreateObject("ADODB.Command")
If Err.Number <> 0 Then
ReportError "creating a Command object."
WScript.Quit
End If
On Error GoTo 0
' Get the date that was 30 days ago and convert it to a string
' in the YYYYMMDD format.
oldDate = DateAdd("d", -30, Now)
oldMonth = Month(oldDate)
If Len(oldMonth) < 2 Then oldMonth = "0" & oldMonth
oldDay = Day(oldDate)
If Len(oldDay) < 2 Then oldDay = "0" & oldDay
oldDateText = Year(oldDate) & oldMonth & oldDay
WScript.Echo "Databases that were created before " _
& FormatDateTime(oldDate, vbShortDate) & " will be detached." & vbCrLf
' Iterate through the databases, find databases that are over 30 days old,
' and then detach them.
dbCounter = 0
dDbCounter = 0
rs.MoveFirst
Do While Not rs.EOF
dbName = rs("name")
If Mid(dbName, 24, 4) <> "_log" Then
dbCounter = dbCounter + 1
End If
If Left(dbName, 6) = "ISALOG" And Mid(dbName, 24, 4) <> "_log" Then
'Get the date part of the dbName
datePart = Mid(dbName, 8, 8)
' If the database is older than 30 days, call the DetachDB
' subprocedure to detach it.
If CLng(datePart) < CLng(oldDateText) Then
DetachDB cn, cmd, dbName
dDbCounter = dDbCounter + 1
End If
End If
rs.MoveNext
Loop
If dDbCounter > 0 Then
WScript.Echo "Detached " & dDbCounter & " of " & dbCounter _
& " databases."
Else
WScript.Echo "No active databases over 30 days old were found."
End If
End Sub
Sub DetachDB(cn, cmd, dbName)
WScript.Echo "Detaching " & dbName & " ..."
cmd.CommandText = "sp_detach_db " & dbName
Set cmd.ActiveConnection = cn
On Error Resume Next
cmd.Execute
If Err.Number <> 0 Then
ReportError "attempting to detach " & dbName & "."
WScript.Quit
End If
On Error GoTo 0
End Sub
Sub ReportError(message)
WScript.Echo "An error was encountered while " & message & vbCrLf _
& "Number : " & Hex(Err.Number) & vbCrLf _
& "Description : " & Err.Description
Err.Clear
End Sub