Removing Old MSDE Log Databases

Removing Old MSDE Log Databases

Microsoft Internet Security and Acceleration Server 2004

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

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' 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

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

Manage Your Profile | Contact Us | Newsletter

Product Families

Resources

About Microsoft

Popular Places

Popular Searches

Popular Downloads