Using DMO to Shrink database

  • How can I use DMO to shrink a database and log file?

  • The database and logfile objects both support a shrink method.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Thanks for the quick response. Can you point to where I can find an example or do you have one? Thanks again.

  • First thing I had handy, might need some touching up:

    Dim mSQLServer

    Dim mDatabase

    Dim mBackup

    Dim strDB

    Dim oSQLSupport

    Dim strMessage

    Dim strExcludeList

    Dim sBakPath

    Dim oLogFile

    On Error Resume Next

    sBakPath = "D:\sqldata\backup"

    strExcludeList = "TEMPDB/PUBS/NORTHWIND/DISTRIBUTION/MODEL"

    Print "The following db's were excluded from the transaction log backup: " & strExcludeList

    Set mSQLServer = CreateObject("SQLDMO.SQLServer")

    Set oSQLSupport = CreateObject("HIGSQLSupport.SQLSupport")

    mSQLServer.LoginSecure = True

    mSQLServer.Connect "."

    For Each mDatabase In mSQLServer.Databases

    For Each oLogFile In mDatabase.TransactionLog.LogFiles

    If oLogFile.Size >= 50 Then

    print mdatabase.name

    If InStr(strExcludeList, UCase(mDatabase.Name)) = 0 Then

    strMessage = strMessage & mDatabase.Name & Chr(13) & Chr(10)

    Set mBackup = CreateObject("SQLDMO.Backup")

    mBackup.Database = mDatabase.Name

    strDB = ""

    If UCase(mDatabase.Name) = "MASTER" Then

    strDB = sBakPath & "\FULL_" & UCase(mDatabase.Name) & "_" & oSQLSupport.FileName & ".bak"

    mBackup.Files = strDB

    mBackup.Action = 0

    Else

    If mDatabase.DBOption.TruncateLogOnCheckpoint <> 0 Then

    mDatabase.DBOption.TruncateLogOnCheckpoint = 0

    End If

    strDB = sBakPath & "\LOG_" & UCase(mDatabase.Name) & "_" & oSQLSupport.FileName & ".bak"

    mBackup.Files = strDB

    mBackup.Action = 3

    End If

    If strDB <> "" Then

    mBackup.SQLBackup mSQLServer

    End If

    Set mBackup = Nothing

    oLogFile.Shrink 10, 0

    End If

    End If

    Next

    Next

    mSQLServer.Disconnect

    Set mSQLServer = Nothing

    Set oSQLSupport = Nothing

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Thanks Andy - I will give it a try.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply