System Versioned Tables In SQL Server
SQL Server audit is an important feature for keeping up database security. Versioning tables helps us to do this.
As a SQL DBA to work on an issue after getting it from the customer/client/app team, it’s best if a DBA identifies the issue before the team comes to DBA. By configuring monitoring alerts, DBA will get the issue before the customer/client/app team informs to DBA. So in this blog I will write down how to configure alerts for blocking queries in SQL server.
First you need to configure database mail. For that open Sql server management studio and then connect to your instance and expand the management folder, right click Database Mail and select Configure Database mail.
Chose Set up Database Mail by performing and specify its SMTP accounts and click Next
If you are configuring for first time it will ask to enable the feature click Yes
In the New Profile window provide a Profile name and click Add button
In the next window provide the following details of your mail server.
Click Next.
Check the public and click Next.
Click Next.
Click Finish.
To create a job expand the Sql Server Agent and right click →New Job…
Enter the job name and click on Steps in left panel
Click New.
Enter the step name and paste the below script in the command box.
NOTE : This script will send the blocking queries which are blocked for more than 3 minutes. If you want to set different time you can change in the where condition b.wait_duration_ms>180000
SET NOCOUNT ON
DECLARE @xml nvarchar(max)
SELECT @xml = Cast((SELECT @@SERVICENAME AS ‘td’,’’,b.session_id AS ‘td’,
‘’,
(b.wait_duration_ms/1000)/60 AS ‘td’,
‘’,
b.wait_type AS ‘td’,
‘’,
b.blocking_session_id AS ‘td’,
‘’,
t.text AS ‘td’
FROM sys.dm_os_waiting_tasks b inner join sys.dm_exec_requests r on r.session_id= b.session_id
OUTER APPLY
sys.dm_exec_sql_text(sql_handle) t
WHERE b.blocking_session_id <> 0 and b.wait_duration_ms>180000
FOR xml path(‘tr’), elements) AS NVARCHAR(max))
Declare @body nvarchar(max)
SET @body =
‘<html>
<head>
<style>
table, th, td
{
border: 1px solid black;
border-collapse: collapse;
text-align: center;
}
</style>
</head>
<body>
<H2>
Blocking queries
</H2>
<table>
<tr>
<th>Instance Name</th><th> Blocked Session ID </th> <th> Wating in minutes </th> <th> Wait type</th>
<th> Blocking Session ID</th><th>Query waiting to execute</th>
</tr>’
SET @body = @body + @xml + ‘
</table>
</body>
</html>’
if(@xml is not null)
BEGIN
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = ‘MSSQL_Alerts’,
@body = @body,
@body_format =’html’,
@recipients = ‘yourmailid@.com’,
@subject = ‘Blocking queries Alert’;
END
SET NOCOUNT OFF
Click the parse button to check any syntax error in the script and click ‘OK’.
Click on Schedules in left panel and click new
Enter the Schedule Name and select the recurring time and click ok. The schedule shown in picture is recommended.
Click OK.
Now SQL will check for blocking queries every 10 minutes and send the mail to the given mail id. And thus you would be able to find any blocking queries and take immediate steps.
