Notes on SQL

Introduction

In a previous article article, I wrote about how to find out where the SQL Server error log is located. That particular article was in case your server instance did not start and you needed more information.

If your SQL Server instance is working, there is another way to find the location of the error logs – with a SQL query, which can then be used within PowerShell.

Background

In my case, for auditing purposes, we have been asked to retain error logs for one year and I was after a process that could remove error logs that were last written to over a year ago.

PowerShell seemed a natural choice for this and I will admit I considered it a good excuse to finally have a more serious look at it.

Detail

I was unable to find a cmdlet that could tell me where the error logs are stored, so decided to execute a SQL command and use the results to then locate the files and delete any as required.

The following SQL code returns the location and name of the error log files.

Listing 1: Retrieve the error log location and name

 
SELECT SERVERPROPERTY('ErrorLogFileName')

This command proves to be useful as it can be executed from PowerShell and therefore used within a PowerShell script.

The brevity of the script belies the amount of work that went into researching the methods used:

Listing 2: The full Powershell script

$NowDate = Get-Date

$Days = -365

$DS = Invoke-Sqlcmd  -Query "SELECT SERVERPROPERTY('ErrorLogFileName') AS LogLocation "| select -expand LogLocation

$ds = $ds + ".*"

Get-ChildItem $ds | Where-Object { $_.LastWriteTime -lt $NowDate.AddDays($Days) } | Remove-Item

There are three distinct parts to this script.

First of all, get today’s date and the number of days that we would like to subtract from that date. I use days instead of years because it makes the script more flexible and easier to test.

The number of days is a negative number because I use the ‘AddDays’ method (there isn’t, to my knowledge, a ‘SubtractDays’ method), so it needs to be negative.

Listing 3: First part of PowerShell script

$NowDate = Get-Date

$Days = -365

Then I need to execute the SQL command that returns the location of the error log. In this example, I have to give the column a name (‘LogLocation’), because it returns a record-set and I just need a variable as my result. Therefore, by giving the column a name, I can extract it with the ‘-Expand’ and it is stored within $DS as a string variable.

Then I add ‘.*’ on to the end of that string, so (as an example)

C:\Program Files\Microsoft SQL Server\MSSQL\Logs\ERRORLOG

Will become

C:\Program Files\Microsoft SQL Server\MSSQL\Logs\ERRORLOG.*

Listing 4: Second part od PowerShell script

$DS = Invoke-Sqlcmd  -Query "SELECT SERVERPROPERTY('ErrorLogFileName') AS LogLocation "| select -expand LogLocation

$ds = $ds + ".*"

The final command has three parts to it. Locate all of the files specified by the variable $DS, filter to select those where the last-written date is greater than (today – 365 days) and delete those files.

Listing 5: Third part of PowerShell script

Get-ChildItem $ds | Where-Object { $_.LastWriteTime -lt $NowDate.AddDays($Days) } | Remove-Item

The server instance is set to retain the maximum number of 99 error log files and this PowerShell script is scheduled to execute as a job at the start of each month, removing any files that we are no longer required to keep.

 

Changing the Last-written date on a file

For testing purposes, I needed to create files that had a last-written date to match my test conditions. Unsurprisingly, I found a PowerShell command for that. To change the timestamp for filename ‘ERRORLOG.88’, use:

Listing 6: Changing a file’s last-written timestamp

(Get-Item " C:\Program Files\Microsoft SQL Server\MSSQL\Logs\ ERRORLOG.88").LastWriteTime=("01 February 2019 10:00:00")