Weekend Scripter: Manage SQL Server Virtual Log File with PowerShell


Summary: Guest blogger, Microsoft PFE, Jonathan Yong, talks about using Windows PowerShell to manage the SQL Server virtual log file.

Microsoft Scripting Guy, Ed Wilson, is here. This is the week of new guest bloggers it seems. Today, please welcome Microsoft PFE, Jonathan Yong...

Some time back, I was discussing a SQL Server administration use case with another engineer. The customer’s DBA wanted to get a list of all virtual log files for each database in the instance to decide which database’s log file needed tuning. The easy part is that DBCC LOGINFO will provide this information.

The tricky part is that the DBA wanted to store the output data from DBCC LOGINFO into a database table. In the output of DBCC LOGINFO, there is a column call CreateLSN, which is a very long field. We couldn’t find the right data type and size to store it (despite what the SQL Server documentation claims).

Due to its length, an error occurred when we tried to insert the value of CreateLSN into the database table. We couldn’t ignore this field by using a subquery. We also wanted to avoid using OPENROWSET because it requires enabling different permissions.

What came to my mind is to try to use Windows PowerShell to automate this. This approach worked, and the solution is not that complicated. The general idea is as follows:

  • Store the list of SQL Server instances that you want to target in a text file.
  • Get the list of databases for each instance in the text file, and create a PSObject with the instance and database name.
  • For each instance-DB object pair, run DBCC LOGINFO against the database, and create a PSObject with a custom property that maps to the output column of DBCC LOGINFO (except CreateLSN, which is the problematic column).
  • With this information in hand, the rest is up to your imagination as to what you want to do with it.

Following is the complete script. You can also download the script from the Script Center Repository: Managing SQL Server Virtual Log File with Powershell.

$sqlListAllDbs = "sp_databases"

$sqlInstance = get-content .sqlserverInstanceList.txt

$dbList  = $sqlInstance | % {

    $instanceName = $_

    #Write-verbose $instanceName

    Invoke-Sqlcmd -Query $sqlListAllDbs -ServerInstance $instanceName  | % {

        $dbName = $_.Database_name

        #Write-verbose $dbName

        $o = new-object psobject -Property @{

            InstanceName = $sqlInstance

            DbName = $dbName

        }

        $o   

      }

}

$dbList | % {

    $dbObject = $_

    Invoke-Sqlcmd -Query "dbcc loginfo" -ServerInstance $dbObject.InstanceName  -Database $dbObject.DbName  | % {

        $o = new-object psobject -Property @{

            Instance = $dbObject.InstanceName

            Database =  $dbObject.DbName

            FileId = $_.FileId

            FileSize = $_.FileSize

            StartOffset = $_.StartOffset

            FSeqNo = $_.FSeqNo

            Status = $_.Status

            Parity = $_.Parity

        }

        $o   

    }

} |  sort Instance,Database |

Out-GridView

#ft Instance,Database,FileId,FileSize,StartOffset,FSeqNo,Status,Parity

Thank you, Jonathan, for an interesting post.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

Comments (2)

  1. jrv says:

    You don’t need PowerShell. This can be done easily in the database using a stored procedure:

    Try this:

    DROP TABLE #dbcc_out
    go
    CREATE TABLE #dbcc_out(
    RecoveryUnitId int,
    FileID varchar(3),
    FileSize numeric(20,0),
    StartOffset bigint,
    FSeqNo bigint,
    Status char(1),
    Parity varchar(4),
    CreateLSN numeric(25,0)
    )

    INSERT INTO #dbcc_out
    EXEC (‘DBCC LOGINFO’)
    select * from #dbcc_out

  2. jrv says:

    I stumbled across this post which explains why you are having issues creating the table. It is worth noting along with a pretty good SP for managing log data.

    http://www.sqlphilosopher.com/wp/2012/08/a-few-changes-in-sql-server-2012-dbcc-loginfo-and-dbcc-checkdb/

Skip to main content