Notification for SQL Server Database in Single User Mode


By:   |   Updated: 2019-11-29   |   Comments   |   Related: More > 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:

  1. Prepare SQL script to pull the database status from each database in single user mode.
  2. Prepare a server list for all the servers in your environment.
  3. 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.
  4. 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:

database status script 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.
server list

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:

  1. Define various parameters used in the script. Update to match your environment.
  2. Function to write to the log data.
  3. Function to connect to SQL Server and pull the result.
  4. Import CSV file and Server Names.
  5. Define SQL server code to be used.
  6. Connect to SQL server one by one and run the SQL code.
  7. Build HTML table
  8. Send email if there are any databases in single user mode.
  9. Capture error and write to the error log if any.
  10. 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
    [email protected]()
try{
import-csv $csvServerList | foreach{
$Server=$_.Servername
$Database=$_.dbNameList
$Environment=$_.Environment
  
Write-Log "We are going to connect the $Server"
[email protected]" 
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:

Automated alert Email Sample

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. 

error log output

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.

SQL server job step

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


Last Updated: 2019-11-29


get scripts

next tip button



About the author
MSSQLTips author Atul Gaikwad 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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools