DBA notified before tempdb fills on SQL Server

Below is the script which would create an Alert,Job to  monitor Tempdb usage

script comprises of following parts :-

1,  create a job tempdb full alert
2,  create an alert which will trigger the job when condition is met i.e (tempdb files is about to reach its limits , and drive on which the file resides is having 10% free space of teh total alloted space on Drive )
3, you can uncomment notification section if the DBA needs to be notified of issue via e-mail

tempdb full alert job functioning :

a, checks the drive where temdp file resides, uses power shell script to get total allotted space and free space details of the drive
b, extract the active transactions running against tempdb (or) dbcc results of the open transactions sessions held on tempdb
c, pull the information in html format and log the information in event viewer

(you can make use of  sp_send_dbmail stored procedure to get the extracted information, rather than logging on to event viewer)


scripts to perform the activity :-

If exists (select * from msdb.dbo.sysjobs where name='Tempdb_Full_alert')  select 'job alreday exist'
else

begin


USE [msdb]

DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'Tempdb_Full_alert',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'Raise RPD when tempdb log file percent used is more than 90 and free space on the drive is less than 10 percent of total space',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId

EXEC msdb.dbo.sp_add_jobserver @job_name=N'Tempdb_Full_alert', @server_name = @@servername

USE [msdb]

EXEC msdb.dbo.sp_add_jobstep @job_name=N'Tempdb_Full_alert', @step_name=N'Raise Rpd',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
set concat_null_yields_null off
declare @svrName varchar(255)
declare @sql varchar(400)
--by default it will take the current server name, we can the set the server name as well
set @svrName = @@SERVERNAME
set @sql = ''powershell.exe -c "Get-WmiObject -ComputerName '' + QUOTENAME(@svrName,'''''''') + '' -Class Win32_Volume -Filter ''''DriveType = 3'''' | select name,capacity,freespace | foreach{$_.name+''''|''''+$_.capacity/1048576+''''%''''+$_.freespace/1048576+''''*''''}"''
--creating a temporary table
CREATE TABLE #output
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql

/*
--script to retrieve the values in GB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(''|'',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(''|'',line)+1,
      (CHARINDEX(''%'',line) -1)-CHARINDEX(''|'',line)) )) as Float)/1024,0) as ''capacity(GB)''
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(''%'',line)+1,
      (CHARINDEX(''*'',line) -1)-CHARINDEX(''%'',line)) )) as Float) /1024 ,0)as ''freespace(GB)''
from #output
where line like ''[A-Z][:]%''
order by drivename
*/

--script to retrieve the values in MB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(''|'',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(''|'',line)+1,
      (CHARINDEX(''%'',line) -1)-CHARINDEX(''|'',line)) )) as Float),0) as capacity_MB
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(''%'',line)+1,
      (CHARINDEX(''*'',line) -1)-CHARINDEX(''%'',line)) )) as Float),0) as freespace_MB
into #temp
from #output
where line like ''[A-Z][:]%''



declare @drive varchar (3)
set @drive= (select filename from sys.sysaltfiles where dbid=2 and filename like ''%.ldf%'')

if exists (select * from #temp where drivename=@drive and freespace_MB < ( capacity_MB*0.1) )

begin

DECLARE @Body VARCHAR(8000) =''<br>              <br> Tempdb running out of space . Following are the transactions running against tempdb <br>   <br>              <table border=1>                     <tr>                           <td>session_id</td>                           <td>transaction_begin_time</td> <td>program_name</td>  <td>host_name</td>                           <td>login_name</td>                           <td>status</td>                           <td>Query</td>                     <tr>''
 print ''dmvs''              
select st.session_id,st.transaction_id,at.name,at.transaction_begin_time,db_name(dt.database_id) Dbname,es.login_time,es.host_name,es.program_name,es.login_name,es.status ,syp.last_batch,est.text query
into #temptable from sys.dm_tran_session_transactions st  join sys.dm_tran_active_transactions at on st.transaction_id=at.transaction_id
join sys.dm_tran_database_transactions dt on dt.transaction_id=st.transaction_id join sys.dm_exec_sessions es on st.session_id=es.session_id
join sys.sysprocesses syp on syp.spid=st.session_id cross apply sys.dm_exec_sql_text(syp.sql_handle) est
where dt.database_id=2 and st.session_id<>@@spid

if @@ROWCOUNT>0
begin

DECLARE  @sessionid int,@txtbegintime datetime,@programname nvarchar(128),@hostname nvarchar(128),@login nvarchar(128),@status nvarchar(30), @SQLTEXT nVARCHAR(MAX)
     DECLARE tempdb_Cursor CURSOR LOCAL FOR
              SELECT session_id,transaction_begin_time,program_name,host_name,login_name,status,Query FROM #temptable
              OPEN tempdb_Cursor;
              FETCH NEXT FROM tempdb_Cursor INTO @sessionid,@txtbegintime,@programname,@hostname,@login,@status,@SQLTEXT;
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@sessionid) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@txtbegintime) + ''</td>''
  SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR(128),@programname) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@hostname) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@login) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@status) + ''</td>''
  SET @Body = @Body + ''<td>'' +CONVERT(NVARCHAR(max),@sqltext) +''</td>''

                           SET @Body = @Body + ''</tr>''
                       FETCH NEXT FROM tempdb_Cursor INTO @sessionid,@txtbegintime,@programname,@hostname,@login,@status,@SQLTEXT;;
                 END;
              CLOSE tempdb_Cursor;
              DEALLOCATE tempdb_Cursor;
 drop table #temptable
     SET @BODY = @BODY + ''      </tr>              </table>              <br>              <br> ''

end
ELSE
begin


 -- Create the temporary table to accept the results.

CREATE TABLE #OpenTranStatus (
   ActiveTransaction varchar(25),
   Details sql_variant
   );
-- Execute the command, putting the results in the table.
INSERT INTO #OpenTranStatus
   EXEC (''DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS'');


print ''dbcc''
select er.session_id,blocking_session_id,er.status,start_time as transaction_begin_time,
wait_type,wait_resource,wait_time,last_wait_type,substring(et.text,(er.statement_start_offset/2)+1,
((case er.statement_end_offset
when -1 then datalength(et.text)
when 0 then datalength(et.text)
else er.statement_end_offset
end - er.statement_start_offset)/2)+1 ) as query,es.login_name,es.host_name,es.program_name into #temptable_dbcc  from sys.dm_exec_requests er
cross apply sys.dm_exec_sql_text(er.sql_handle) et left join sys.dm_exec_sessions es on er.session_id=es.session_id  where er.session_id in (select convert(int,details,0) from #OpenTranStatus where ActiveTransaction=''OLDACT_SPID'')

if @@ROWCOUNT>0 begin

DECLARE  @sessionid_dbcc int,@txtbegintime_dbcc datetime,@programname_dbcc nvarchar(128),@hostname_dbcc nvarchar(128),@login_dbcc nvarchar(128),@status_dbcc nvarchar(30), @SQLTEXT_dbcc nVARCHAR(MAX)
     DECLARE tempdb_Cursor_dbcc CURSOR LOCAL FOR
              SELECT session_id,transaction_begin_time,program_name,host_name,login_name,status,Query FROM #temptable_dbcc
 OPEN tempdb_Cursor_dbcc;
              FETCH NEXT FROM tempdb_Cursor_dbcc INTO @sessionid_dbcc,@txtbegintime_dbcc,@programname_dbcc,@hostname_dbcc,@login_dbcc,@status_dbcc,@SQLTEXT_dbcc;
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@sessionid_dbcc) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@txtbegintime_dbcc) + ''</td>''
  SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR(128),@programname_dbcc) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@hostname_dbcc) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@login_dbcc) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@status_dbcc) + ''</td>''
  SET @Body = @Body + ''<td>'' +CONVERT(NVARCHAR(max),@sqltext_dbcc) +''</td>''

                           SET @Body = @Body + ''</tr>''
                       FETCH NEXT FROM tempdb_Cursor_dbcc INTO @sessionid_dbcc,@txtbegintime_dbcc,@programname_dbcc,@hostname_dbcc,@login_dbcc,@status_dbcc,@SQLTEXT_dbcc;;
                 END;
              CLOSE tempdb_Cursor_dbcc;
              DEALLOCATE tempdb_Cursor_dbcc;
 drop table #temptable_dbcc
     SET @BODY = @BODY + ''      </tr>              </table>              <br>              <br> ''

end
ELSE
begin
print ''dbcc noinfo''
select spid as session_id,last_batch as transaction_begin_time,program_name,hostname as host_name,loginame as login_name,status,cmd as query into #temptable_dbcc_noinfo from sys.sysprocesses where spid=(select convert(int,details,0) from #OpenTranStatus where ActiveTransaction=''OLDACT_SPID'')
DECLARE  @sessionid_dbcc_noinfo int,@txtbegintime_dbcc_noinfo datetime,@programname_dbcc_noinfo nvarchar(128),@hostname_dbcc_noinfo nvarchar(128),@login_dbcc_noinfo nvarchar(128),@status_dbcc_noinfo nvarchar(30), @SQLTEXT_dbcc_noinfo nVARCHAR(MAX)
     DECLARE tempdb_Cursor_dbcc_noinfo CURSOR LOCAL FOR
              SELECT session_id,transaction_begin_time,program_name,host_name,login_name,status,Query FROM #temptable_dbcc_noinfo
 OPEN tempdb_Cursor_dbcc_noinfo;
              FETCH NEXT FROM tempdb_Cursor_dbcc_noinfo INTO @sessionid_dbcc_noinfo,@txtbegintime_dbcc_noinfo,@programname_dbcc_noinfo,@hostname_dbcc_noinfo,@login_dbcc_noinfo,@status_dbcc_noinfo,@SQLTEXT_dbcc_noinfo;
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@sessionid_dbcc_noinfo) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@txtbegintime_dbcc_noinfo) + ''</td>''
  SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR(128),@programname_dbcc_noinfo) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@hostname_dbcc_noinfo) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@login_dbcc_noinfo) + ''</td>''
                           SET @Body = @Body + ''<td>'' + CONVERT(VARCHAR,@status_dbcc_noinfo) + ''</td>''
  SET @Body = @Body + ''<td>'' +CONVERT(NVARCHAR(max),@sqltext_dbcc_noinfo) +''</td>''

                           SET @Body = @Body + ''</tr>''
                       FETCH NEXT FROM tempdb_Cursor_dbcc_noinfo INTO @sessionid_dbcc_noinfo,@txtbegintime_dbcc_noinfo,@programname_dbcc_noinfo,@hostname_dbcc_noinfo,@login_dbcc_noinfo,@status_dbcc_noinfo,@SQLTEXT_dbcc_noinfo;;
                 END;
              CLOSE tempdb_Cursor_dbcc_noinfo;
              DEALLOCATE tempdb_Cursor_dbcc_noinfo;
 drop table #temptable_dbcc_noinfo
     SET @BODY = @BODY + ''      </tr>              </table>              <br>              <br> ''


end

drop table #OpenTranStatus

end

EXEC master.dbo.xp_logevent 77007,@Body,INFORMATIONAL  
/*use sp_send_dbmail above incase mail needs to be received to DBA rather than logging in event viewer*/

 end


--script to drop the temporary table
drop table #temp
drop table #output




',
@database_name=N'tempdb',
@flags=0


USE [msdb]

EXEC msdb.dbo.sp_update_job @job_name=N'Tempdb_Full_alert',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'Raise RPD when tempdb log file percent used is more than 90 and free space on the drive is less than 10 percent of total space',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''




USE [msdb]

declare @unqjobid uniqueidentifier
set @unqjobid=(select job_id from msdb.dbo.sysjobs where name='Tempdb_Full_alert')

EXEC msdb.dbo.sp_add_alert @name=N'Tempdb Log File Percent Used',
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@performance_condition=N'SQLServer:Databases|Percent Log Used|tempdb|>|90',
@job_id=@unqjobid

/* uncomment below part if notification to team via email is required

if Exists (select name from msdb.dbo.sysoperators where email_address='someemail@domain.com')

begin

Declare @sqloperator  as varchar(128)
set @sqloperator=(select name from msdb.dbo.sysoperators where email_address='someemail@domain.com')
EXEC msdb.dbo.sp_add_notification @alert_name=N'Tempdb Log File Percent Used', @operator_name=@sqloperator, @notification_method = 1

end

*/

end