monitor sql server job status

SELECT name AS [Job Name]
,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time – (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time – (run_time/100) * 100)  /* secs */
,CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]
,CASE WHEN enabled=1 THEN ‘Enabled’
ELSE ‘Disabled’
END [Job Status]
,CASE WHEN SJH.run_status=0 THEN ‘Failed’
WHEN SJH.run_status=1 THEN ‘Succeeded’
WHEN SJH.run_status=2 THEN ‘Retry’
WHEN SJH.run_status=3 THEN ‘Cancelled’
ELSE ‘Unknown’
END [Job Outcome]
FROM   sysjobhistory SJH
JOIN   sysjobs SJ
ON     SJH.job_id=sj.job_id
WHERE  step_id=0
(run_time/10000)*60*60 /* hours */
+((run_time – (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time – (run_time/100) * 100)  /* secs */,
CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-1,GetDate())
ORDER BY name,run_date,run_time

Sql server setting up alert for long running queries

create procedure dbo.sp_send_long_running_alerts()



/* NOTE: You have to configure/set the following 3 variables */
DECLARE @AlertingThresholdMinutes int = 10;
DECLARE @MailProfileToSendVia sysname = ‘General’;
DECLARE @OperatorName sysname = ‘Alerts’;


DECLARE @LongestRunningTransaction int;
@LongestRunningTransaction =
MAX(DATEDIFF(n, dtat.transaction_begin_time, GETDATE()))
sys.dm_tran_active_transactions dtat
INNER JOIN sys.dm_tran_session_transactions dtst
ON dtat.transaction_id = dtst.transaction_id;

IF ISNULL(@LongestRunningTransaction,0) > @AlertingThresholdMinutes BEGIN

DECLARE @Warning nvarchar(800);
DECLARE @Subject nvarchar(100);

SET @subject = ‘[Warning] Long Running Transaction On ‘ + @@SERVERNAME;
SET @Warning = ‘Check SSMS > Server > Reports > Top Transactions By Age.’;

EXEC msdb..sp_sendmail
@profile_name = @MailProfileToSendVia,
@name = @OperatorName,
@subject = @subject,
@body = @warning;

Monitor Sql Server disk space

Example: EXEC [DBA_DiskSpaceMntr]
@mailto = ‘dba mail’,
@CDrivethreshold = 10240,
@OtherDrivethreshold = 20480

CREATE PROCEDURE [dbo].[DBA_DiskSpaceMntr]
@mailto nvarchar(4000),
@CDrivethreshold INT,
@DDrivethreshold INT,
@YDrivethreshold INT,
@OtherDrivethreshold INT
declare @count int;
declare @DiskFreeSpace int;
declare @tempfspace int;
declare @tempdrive char(1);
declare @mailbody nvarchar(4000);
declare @MailSubject nvarchar(1000);
declare @AlertMessage nvarchar(4000);
declare @altflag bit;
declare @sub nvarchar(4000);
declare @cmd nvarchar(4000);
set @count = 0;
SET @mailbody = ”;
SET @cmd = ”;
set nocount on
IF EXISTS(select * from sys.sysobjects where id = object_id(‘#driveinfo’))
drop table #driveinfo
create table #driveinfo(id int identity(1,1),drive char(1), fspace int)
insert into #driveinfo EXEC master..xp_fixeddrives
SELECT @DiskFreeSpace = fspace FROM #driveinfo where drive in (‘C’)
IF @DiskFreeSpace < @CDrivethreshold
SET @MailSubject = ‘Drive C: free space is low on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar)
SET @mailbody = ‘Drive C: on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar) + ‘ has only ‘ + CAST(@DiskFreeSpace AS VARCHAR) + ‘ MB left. Please free up space on this drive. ‘
–select * FROM #driveinfo where drive in (‘L’)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLDBA_Support’,
@recipients= @mailto,
@subject = @MailSubject,
@body = @mailbody,
–@file_attachments = @logfile,
@body_format = ‘HTML’
SELECT @DiskFreeSpace = fspace FROM #driveinfo where drive in (‘D’)
IF @DiskFreeSpace < @DDrivethreshold
SET @MailSubject = ‘Drive D: free space is low on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar)
SET @mailbody = ‘Drive D: on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar) + ‘ has only ‘ + CAST(@DiskFreeSpace AS VARCHAR) + ‘ MB left. Please free up space on this drive. ‘
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLDBA_Support’,
@recipients= @mailto,
@subject = @MailSubject,
@body = @mailbody,
–@file_attachments = @logfile,
@body_format = ‘HTML’
SELECT @DiskFreeSpace = fspace FROM #driveinfo where drive in (‘Y’)
IF @DiskFreeSpace < @YDrivethreshold
SET @MailSubject = ‘Drive Y: free space is low on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar)
SET @mailbody = ‘Drive Y: on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar) + ‘ has only ‘ + CAST(@DiskFreeSpace AS VARCHAR) + ‘ MB left. Please free up space on this drive. ‘
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘profile_name’,
@recipients= @mailto,
@subject = @MailSubject,
@body = @mailbody,
–@file_attachments = @logfile,
@body_format = ‘HTML’
set @mailbody=”;
while (select count(*) from #driveinfo ) >= @count
set @tempfspace = (select fspace from #driveinfo where id = @count and drive not in (‘C’,’Q’,’D’,’Y’))
set @tempdrive = (select drive from #driveinfo where id = @count and drive not in (‘C’,’Q’,’D’,’Y’))
if @tempfspace < @OtherDrivethreshold
SET @altflag = 1;
SET @mailbody = @mailbody + ‘<p>Drive ‘ + CAST(@tempdrive AS NVARCHAR(10)) + ‘ has ‘ + CAST(@tempfspace AS NVARCHAR(10)) + ‘ MB free</br>’
–SET @cmd = ‘dir /s /-c ‘ + @tempdrive + ‘:\ > ‘ + @logfile
–EXEC xp_cmdshell @cmd
set @count = @count + 1
IF (@altflag = 1)
SET @sub = ‘Monitor Space on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar)
set @mailbody = ‘The below drives on ‘ + cast(Serverproperty(‘Machinename’) as nVarchar) + ‘ have low disk space then threshold limit ‘ + CAST(@OtherDrivethreshold as VARCHAR(10)) +’ Please free up the space in below specified drives <p>’ + @mailbody
–print ‘Space on ‘ + @tempdrive + ‘: is very low: ‘ + str(@tempfspace)+ ‘MB’
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Profile name’,
@recipients= @mailto,
@subject = @sub,
@body = @mailbody,
–@file_attachments = @logfile,
@body_format = ‘HTML’
drop table #driveinfo
set nocount off

SQL Server 2016 SSRS Best Practice

1.Enable Caching
enable Report Caching for Reports that are frequently accessed because every time the report runs it will not hit the report server to process and render the report.

2: Run Report Caching and Snapshots during off peak hours

3: Monitor Performance of Database Source system
the health of the database source system should be periodically monitored.
4: Monitor Performance of Report Server system
Report Server system should be periodically monitored for the health.

5: Use saved authentication when configuring report data sources.
Configuring a data source to use a SQL Authentication login.

6: Set Report Execution Timeout
Set the Report Execution Timeout so that your query does not eat up all your resources.

7: Monitoring Execution Log Data to Identify Slow Reports
Monitor the execution log to identify where the report server is spending most time.

For detailed information please read this tip SSRS Execution Log.
8: Back up the SSRS Report Server Databases
It is always recommended to take a backup of the reportserver and reportservertempdb database periodically.
9: Practice SSRS migration to another server

10: Keep all reports under source control

SQL Server 2016 Top 10 New Features

SQL Server 2016 New features


Query Store

One common problem many organizations face when upgrading versions of SQL Server is changes in the query optimizer (which happen from version to version) negatively impacting performance. The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have issues recently, allowing rolling back to previous working version.



Microsoft had introduced Polybase, a SQL Server connector to Hadoop (and Azure Blob Storage) to its data warehouse appliance Analytics Platform System in 2015. But now Microsoft has incorporated that functionality into the regular on-premises product. This feature will allow you integrate with external data source with a lot of large text files — they can be stored in cloud or Hadoop, and queried as if they were database tables.


Stretch Database (data tiers)

Base on the useage of the data, they will be stored in SAN, local disk and network/cloud, tier based data access allow you have the most frequent used data in memory/SAN, while less frequest used data stored on localdisk, and even less used data on network or Cloud.


JSON Support

In addition to supporting direct querying to Hadoop, SQL Server 2016 adds support for the lingua franca of Web applications: Java Script Object Notation (JSON). The way this is implemented in SQL 2016 is very similar to the way XML support is built in. Making data exchange easier with applicatoin such as web development easier.


Row Level Security


Always Encrypted, increase data security such as for PII data.


In-Memory Enhancements

SQL Server 2014 introduced the concept of in-memory tables. These were optimally designed for high-speed loading of data with no locking issues or high-volume session state issues



Checking BLock email


spid varchar(90),

p_status varchar(90),

p_login varchar(90),

Hostname varchar(90),

blk_by varchar(90),

DBName varchar(90),

sqlCmmd varchar(max),



LastBatch Varchar(30),

ProgramName varchar(255),

Spid2 varchar(90),

RequestId INT


INSERT INTO ##temp exec sp_who2

select blocking.Hostname blocking_host,

blocking.DBName blocking_db,

blocking.p_login blocking_user,

blocking.ProgramName blocking_program,

blocking.sqlCmmd blocking_sql,

blocked_by.Hostname host_blocked,

blocked_by.DBName db_blocked,

blocked_by.p_login user_blocked,

blocked_by.ProgramName program_blocked,

blocked_by.sqlCmmd sql_blocked

INTO ##Blockings

FROM ##temp blocking

LEFT JOIN ##temp blocked_by

ON blocking.spid = blocked_by.blk_by

Declare @count INT

SELECT @count = COUNT(*) from ##Blockings

IF @count >1



@TableHead VARCHAR(1000),

@TableTail VARCHAR(1000)

SET @TableTail = ‘</table></body></html>’ ;

SET @TableHead = ‘<html><head>’ + ‘<style>’

+ ‘td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} ‘

+ ‘</style>’ + ‘</head>’ + ‘<body>’ + ‘Report generated on : ‘


+ ‘ <br> <table cellpadding=0 cellspacing=0 border=0>’

+ ‘<tr> <td bgcolor=#E6E6FA><b>blocking_host</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>blocking_db</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>blocking_user</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>blocking_sql</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>blocking_program</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>host_blocked</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>db_blocked</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>user_blocked</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>program_blocked</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>sql_blocked</b></td></tr>’ ;

SET @msgBody = ( SELECT td = blocking_host, ,

td = blocking_db, ,

td = blocking_user, ,

td = blocking_sql, ,

td = blocking_program,,

td = host_blocked, ,

td = db_blocked,,

td = user_blocked,,

td = program_blocked,,

td = sql_blocked,

FROM ##Blockings

FOR XML RAW(‘tr’),



SELECT @msgBody = @TableHead + ISNULL(@msgBody, ) + @TableTail

–PRINT @Body

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘profile_name’,

@recipients = ‘’,

@body = @msgBody,

@body_format = ‘HTML’,

@subject = ‘TEst mail from SQL’


Drop table ##temp

Drop table ##Blockings