Checking Database Log Files from Powershell

A while back I had written a post about ahow to check your VLF count for each database on a sql server.

It's all well and good to run this script via ssms, however it does give us some limitations in terms of scripting, and you have to use SSMS. Which while it's okay for some tasks, is not exactly my favorite way of writing code. Why not run it directly from your shell with powershell?

There are two parts to this, one is learning how to connect to SQL Server using SMO and the second is actually running the code that will return what you want. I will do a more in depth example of connecting to SQL server via SMO at a later date, so this will assume you have a Windows Auth to a sql server as well as SQL server installed locally.

Do not do this against a prod server.

Open your favorite text editor and create a new .ps1 file, add this to it:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 

$server = New-Object Microsoft.SqlServer.Management.SMO.server [YourSQlServerIpOrName]

This will get you connected to the SQL server, it's not the best way of connecting to SQL server, but for now it will work fine. The first line will load the SMO library that you will use to connect. The second creates the new SMO object and loads it with your server.

Next we want to add the code, to copy and paste go here, my code blocks are not indenting properly right now.:

There is a lot going on here, so let me explain this a bit.

The first thing we do is start a foreach loop, this will loop through all of the database in the SMO object where you have access. Inside the loop:

  1. Find the log files and get specific properties associated for them.
  2. Get the Database name
  3. Exec DBCC LOGINFO against the database and get the count of the files and the name ( file Id, group-object is a bit weird like that )

There is a second foreach loop, this is because a single database can have multiple log files and each of these can have their own VLF's, so we have to map them and build an object to output to the host for you to see.

You might notice that this isn't returning a table, this is easily resolved with assigning this to a variable and then piping it to format-table or ft.

$files | ft