By: Atul Gaikwad | Updated: 2019-11-29 | Comments (1) | Related: > Database Configurations
Problem
While working on a new production server, somehow a database was changed to single user mode and we were not notified until the application team informed us that they were not able to connect to the database and were getting errors. This server was not live yet and we did not have monitoring enabled on this server yet.
So, how can we setup automated alerts to get notified if databases are in single user mode? And can we do this for all UAT and PROD server in single job or batch script? Can we use PowerShell for automation? The answer is yes for all of these questions. I will demonstrate how can we achieve this using a SQL script and PowerShell along with SQL Server Agent job and once the solution is built you can modify it and use for any type of automated alert you want to setup.
Solution
The solution was put together as follows:
- Prepare SQL script to pull the database status from each database in single user mode.
- Prepare a server list for all the servers in your environment.
- Prepare PowerShell script to connect each instance, one by one, and pull the database status using the SQL script prepared in step 1 and email DBA only if there are databases is in single user mode.
- Schedule a SQL Server Agent job to run on a set schedule to check database status on each instance (server list in step 2) and send e-mail alert notification to designated recipients.
Step 1: SQL script to find database status
SET NOCOUNT ON SELECT Name [Database Name], State_Desc [Status], User_Access_Desc [DB Access Type] FROM sys.databases GO
Output:
Step 2: Prepare file containing each instance you want to check
The PowerShell script is going to use the Servername as input to connect to the SQL server instance from file Serverlist.csv. The connections will be made using a trusted connection, but you could modify the process to use a SQL login to connect if necessary.
Here is what the Serverlist.csv file should look like. I placed this file in folder C:\Scripts.
- Servername - SQL Server instance name
- dbNameList - database to use when connecting to the instance (should be master in almost all cases)
- Environment - use this as needed. I broke mine up by DEV, UAT and PROD. This is just used for the emails.
The script will run against each server send email in HTML format only if an instance has any databases in single user mode.
Step 3: Prepare PowerShell script to connect each instance
We will use the SQL script from step 1, and connect to each instance and send an HTML formatted email if any database is in single user mode.
Here is how the PowerShell code is broken down:
- Define various parameters used in the script. Update to match your environment.
- Function to write to the log data.
- Function to connect to SQL Server and pull the result.
- Import CSV file and Server Names.
- Define SQL server code to be used.
- Connect to SQL server one by one and run the SQL code.
- Build HTML table
- Send email if there are any databases in single user mode.
- Capture error and write to the error log if any.
- Repeat for next server in input file
Things to do before running:
- Update the PowerShell script for the file and path of the server list
- Update the PowerShell script with your SMTP and email settings
[CmdletBinding()]
param(
[String[]] $csvServerList
)
$csvServerList = "C:\Scripts\Serverlist.csv" #input file path
$CurrentTime=Get-Date
$scriptInvocation = (Get-Variable MyInvocation -Scope 0).Value
$scriptPath='C:\Scripts
$ErrorLogfilePath=$scriptPath+'\'+"ErrorLog_SingleUser.txt"
###########################################################################################
# Functions
##########################################################################################
# Function to write log
Function Write-Log
{
Param([String[]]$text )
if(!(Test-Path $ErrorLogfilePath))
{
New-Item -path $ErrorLogfilePath -ItemType file
$text|Out-file $ErrorLogfilePath -Append
}
else
{
$text|Out-file $ErrorLogfilePath -Append
}
}
# Function to Connect Instance and get results
function ExecuteSqlQuery{
Param
(
[String[]] $Server,
[String[]] $Database,
[String[]] $SQLQuery
)
Try{
$Datatable = New-Object System.Data.DataTable
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$Database';Connection Timeout=60; trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SQLQuery
$Command.CommandTimeout = 1000
$DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $Command
$Dataset = new-object System.Data.Dataset
$DataAdapter.Fill($Dataset)
$Connection.Close()
return $Dataset
}
catch
{
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
Write-Log("Error occured in function ExecuteSqlQuery ")
Write-Log($ErrorMessage)
Write-Log($FailedItem)
}
}
Write-Log("Script started at : $CurrentTime")
$resultsDataTable = new-object System.Data.Dataset
$datarows1 =New-Object System.Data.DataTable
$results=@()
try{
import-csv $csvServerList | foreach{
$Server=$_.Servername
$Database=$_.dbNameList
$Environment=$_.Environment
Write-Log "We are going to connect the $Server"
$UserSqlQuery=@"
select @@servername ServerName, Name, State_desc, user_Access_Desc
FROM sys.databases
where State_desc = 'Online' and user_Access_Desc<>'Multi_user'
"@
$resultsDataTable = ExecuteSqlQuery -Server $Server -Database $Database -SQLQuery $UserSqlQuery
$datarows1=$resultsDataTable.Tables[0]
####################### HTML FORMAT ##################################
$HtmlTable1 = "<table border='1' align='Left' cellpadding='2' cellspacing='0' style='color:black;font-family:arial,helvetica,sans-serif;text-align:left;'>
<tr style ='font-size:13px;border-collapse: collapse;font-weight: normal;background:gray'>
<th>Server Name</th>
<th>Database Name</th>
<th>State</th>
<th>DB Access Status</th>
</tr>"
IF($datarows1.Rows.Count -gt 0)
{
foreach ($row in $datarows1)
{
$HtmlTable1 += "<tr style='font-size:13px;background-color:#FFFFFF'>
<td>" + $row.ServerName + "</td>
<td>" + $row.Name + "</td>
<td>" + $row.State_desc + "</td>
<td>" + $row.user_Access_Desc + "</td>
</tr>"
}
$HtmlTable1 += "</table>"
# Send Mail Inputs
$smtpserver = "SMTP.SQLDBAEXPERTS.com"
$from = "SQLDBA <[email protected]>"
$to = "<[email protected]>"
$cc = "<[email protected]>"
$subject = "HIGH Alert : Database in Single_user mode on " + $Environment
$body = $HtmlTable1 + "<br><br/><br/><b> Note:</b> This is Alert for DB Single User mode on $Environment. Please take action ASAP.<br/><br/><br/>"
Send-MailMessage -smtpserver $smtpserver -from $from -to $to -cc $cc -subject $subject -body $body -bodyashtml
Write-Log "Mail Sent successfully from $Server : " + Get-Date
}
##################################################################
Else
{
Write-Log ("No Data found for the server $Server in the database $Database " )
}
}
Write-Log("Script successfully executed" )
$CurrentTime=Get-Date
Write-Log("Script Ended : $CurrentTime")
}
Catch
{
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
Write-Log("Error occured in Main Process ")
Write-Log($ErrorMessage)
Write-Log($FailedItem)
$CurrentTime=Get-Date
Write-Log("Script Ended : $CurrentTime")
}
Once the PowerShell script is ready, you can run the PowerShell script manually from PowerShell editor (Powershell_ise.exe) to verify if the script is working as expected and if you are getting alerts for the databases in single user mode.
To test, set a test database to single user to meet the criteria to send the email and you should receive an email as shown below:
You can also check the Errorlog file which will be created in the C:\scripts (or whatever you set) folder with name Errorlog_Singleuser.txt. Here are the contents.
Step 4: Automate the PowerShell execution using a SQL Server Agent Job
We can create a SQL Server Agent Job to run the PowerShell script one time or schedule the PowerShell script to run at specific times (i.e. every 15 minutes or once a day).
Here are the job step settings. I named the PowerShell script DBStatus.PS1 and stored in the C:\Scripts folder.
Set a schedule and save the job.
Things to Note
The script will work properly if you run manually from the PowerShell editor, but it might fail if you run as a SQL Server Agent Job. This is because the SQL Server Agent service account may not have necessary privileges to read and write to the folder you specified or run the PowerShell script. To resolve the error, you might have to create a Proxy account for the login which has the necessary privileges to write to the errorlog and run PowerShell.
Now you have a solution to automate alerts using PowerShell. The script can be tweaked further to get any alerts you need, like backup status of databases, SQL job status and more.
Next Steps
- Let me know if you have other needs and I will see if I can help. Just enter your comments below.
- Please reference the following tip:
About the author
Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-11-29
