Powershell script to delete SQL server databases matching a filter

I previously shared a SQL server script to bulk delete all SQL server databases which matched a search filter (http://tinyurl.com/oucvxfs), I have recently written a powershell script to perform the same process and to remove the database backup history.

Firstly, we will need to specify the parameters required to run the script.

Param ([Parameter(Mandatory=$true)][string] $ServerInstance,
[Parameter(Mandatory=$true)][string] $Filter)

In order to invoke SQL statements from within Powershell, we will need to import the SQLPS module into the current powershell session:

Import-Module SQLPS -DisableNameChecking

In order to return all databases which match the filter specified as a parameter, we will invoke a SELECT statement to return the information from the sys.databases view:

$Databases = Invoke-SQLcmd -ServerInstance $ServerInstance -Query ("SELECT * from sys.databases where NAME like '%$Filter%'")

For each line returned in the database variable, we will loop through each one using the name value to determine the database to remove the backup history and then finally drop the database.

ForEach ($Database in $Databases)
Invoke-SQLcmd -ServerInstance $ServerInstance -Query ("EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'" + $Database.Name + "'")
Invoke-SQLcmd -ServerInstance $ServerInstance -Query ("DROP DATABASE [" + $Database.Name + "]")

You can run the script as below, to connect to the server instance SERVER1\SQL and remove database containing the string ABCD:

./Remove-BulkSQLServerDatabases.ps1 -ServerInstance SERVER1\SQL -Filter ABCD

Monitor the status of SQL Server Agent jobs with Nagios XI

I am looking into monitoring the status of  SQL Server Agent jobs and reporting them with Nagios with the following requirements

  • For each step report the ‘run_status’.
  • Provide date arithmetic to determine a time span for when the step was last run in days.
  • Return a status of Critical if the run_status is  reported as ‘0’ and Warning if the run_status is reported as ‘1’ and the ‘run_date’ is greater than the time span.

The powershell script will require two parameters for both the SQL Agent step name and the number of days to compare the ‘run_date’ agaisnt a calculated time span object.

Param ([string] $StepName,[string] $Days)

As I will be querying the SQL Server database engine instance and in this case a majority of those are SQL Server 2008 R2 I will need to import the SQLPS module into my session.

Import-Module SQLPS -DisableNameChecking

Below is my the query I will be invoking to return the information for the last run where the step_name is specified as a parameter.

$Query = Invoke-SQLCmd - ServerInstance "." -Query ("SELECT TOP 1 * from msdb..sysjobhistory h WHERE step_name = " + $StepName + " order by instance_id desc")

Once the information has been returned I will need to use DateTime.ParseExact method in order to convert  the ‘run_date’ value to a date string of ‘dd/MM/yyyy’ and store this as a variable.

$LastRun = [datetime]::parseexact(($Query.run_date),"yyyyMMdd",$null).toString('dd/MM/yyyy')

The run_duration value returns the amount of time the step was running in seconds, I want to return this as a string ‘dd:hh:mm:ss’ by using the New-TimeSpan cmdlet and formatting the output.

$Duration = (New-TimeSpan -Seconds $Query.run_duration).ToString()

In order to provide the date arithmetic I will subtract the  number of days specified in the parameter from the current date.

$Timespan = (Get-Date) - (New-TimeSpan -Days $Days -Hours 0 -Minutes 0)

In order to configure the service status and the status information conditional logic will be used to determine the return code. As per my requirements, I require the to report the service status to be ‘OK’ if the run_status is equal to ‘1’ and the ‘run_date’ is greater than the TimeSpan variable generated from the date arithmetic.

If ($Query.run_status -eq "1" -and [datetime]::parse($LastRun ) -ge [datetime]::parse($TimeSpan) )
$returncode = 0
"Completed succesfully on " + $LastRun + " with run duration " + $Duration

We will report a service status of ‘Warning if the ‘run_status’ is equal to ‘1’ and the ‘run_date’ is less then the TimeSpan variable generated from the date arithmetic.

ElseIf ($Query.run_status -eq "1" -and $LastRun -lt $Timespan )
$returncode = 1
"Completed succesfully on " + $LastRun + " with run duration " + $Duration

Finally, if the ‘run_status’ is equal to ‘0’ the service status will be reported as ‘Critical.

ElseIf ($Query.run_status -eq "0")
$returncode = 2
"Completed with failure on " + $LastRun  + " with run duration " + $Duration

Once the status information has been generated the script will exit the powershell session returning the exit code.

exit $returncode

While the script was created to be executed as an external script within Nagios, this can be run standalone from Windows Powershell as below.

./Check-SQLServerAgentJob,ps11 -StepName <step_name> -Days <number of days>

If your are looking to add external scripts to Nagios such as this one see the below link for more information;


The full Windows Powershell script can be downloaded from the below link:


Checking to see if CLR Integration is enabled for SQL Server

In order to check if command language runtime (CLR) integration feature is enabled within SQL Server, run the following query agaisnt the master database:

SELECT * FROM sys.configurations
WHERE name = ‘clr enabled’

If CLR is enabled the value returned will be ‘1’, as displayed in the ‘value’ row. By default this feature is turned off and the value would be ‘0’.

In order to enable CLR, use the clr enabled option of the sp_configure stored procedure:

sp_configure ‘show advanced options’, 1;
sp_configure ‘clr enabled’, 1;

To disable CLR, set the clr enabled option to ‘0’.

Change the port number of SSAS Instance

If you have a named instance of Analysis Services this will listen on a dynamic TCP port which is assigned when the service starts.

Only a default instance will listen on TCP port 2383.

In order to change this to a static port follow the below steps;

1) Connect to the Analysis Services instance using Microsoft SQL Management Studio.

2) Select ‘Properties’

3) Select ‘General’

4) Browse to ‘Port’ and change the value to the specified TCP port. (By default this value should be ‘0’)

5) Select ‘OK’

6) Restart the Analysis Services instance.

You can confirm the Analysis Services instance is now listening on the above port by running the following command:

netstat /aob

Search for the TCP port you have specified in Step 4 and confirm the executable ‘msmdsrv.exe’ is listening on this port.