Archive for the ‘Active Trace Files’ Category

How to query and display the active trace files?

While trace files will be removed in future versions of SQL Server and Microsoft recommends you to avoid using this feature in new development work and use Extended Events instead, trace files have been used extensively in the past decade. The switch over to Extended Events will not be instantaneous and you will still need to know how to work with it for all SQL Servers that are running versions 2000 to 2012 as more than plenty of instances will have trace files running on them in addition to the default trace file.

To view all the active trace files:

-- Display information about all active traces
-- http://technet.microsoft.com/en-us/library/ms173875.aspx
-- property of 5: current trace status
-- value of 1: running
SELECT traceid
, property
, value
FROM :: fn_trace_getinfo(0)

To send yourself an html report email on the active trace files, you can use the following code:

-- Display information about all active traces
-- http://technet.microsoft.com/en-us/library/ms173875.aspx
 /*NOTE: Replace the recipients email addresses in line #18 */

set nocount on
-- declare the required variables 
DECLARE @Recipients VARCHAR(123) -- recipients for this alert
, @instancename varchar(55)
, @IsDBMailEnabled BIT
, @MailProfile VARCHAR(123)
, @MailSubject VARCHAR(123)
, @Subject VARCHAR(123)
, @TableHTML VARCHAR(MAX)

select @instancename = CONVERT(varchar, SERVERPROPERTY('SERVERNAME'));


SET @Recipients = 'email@company.com' -- specify your recipients here, separate with ;

-- check if database mail is enabled
SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'Database Mail XPs'

IF @IsDBMailEnabled = 0
	RAISERROR ('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

ELSE
	BEGIN
		SELECT @TableHTML = 
		'<html>
		<div style="font-family:Verdana; font-size:14pt;"><div style="font-weight:bold; width:auto; float:left;">'+@instancename+'</div><div style=" width:auto; float:left; padding-left:5px; "> Active Trace Files:</div><br></div></div><br>
		<table style="border: 1px solid #000000; border-collapse:collapse; table-layout:fixed; font-family:Arial; font-size:11px;" cellpadding=0 cellspacing=0 border=0>' +
		'<tr style="border: 1px solid #000000; font-family:Verdana; font-size:12px; background-color:#3B8D24; color:white; height:30px;">
			<th style="border: 1px solid #000000;" align=center>TraceID</th>
			<th style="border: 1px solid #000000;" align=center>Property</th>
			<th style="border: 1px solid #000000;" align=center>Value</th>
		</tr>'
		
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(varchar, [TraceID]) + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(varchar, [Property]) + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(varchar(max), ISNULL([value], 'NULL')) + '</td></tr>'FROM :: fn_trace_getinfo(0)

		SELECT @TableHTML = @TableHTML +'</table></html>'
			
		-- Send email notification
		SELECT @MailSubject = CONVERT(VARCHAR(50),@instancename) + ' Active Trace Files'
		
		-- Find the database mail profile name
		create table #dbmail_profile
		(
		profileid int null,
		profilename varchar(125) null,
		accountid int null,
		accountname varchar(125) null,
		sequencenumber int null
		)
		insert #dbmail_profile
		EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

		select @MailProfile = profilename
		from #dbmail_profile
		where sequencenumber = 1

		drop table #dbmail_profile

		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @MailProfile,
			@recipients = @Recipients,
			@subject = @MailSubject,
			@body_format = 'HTML',
			@body = @TableHTML;
	END
set nocount off	

This is an example of what the report looks like:
Active Trace Files

REFERENCES:
sys.fn_trace_getinfo
http://technet.microsoft.com/en-us/library/ms173875.aspx