Archive for the ‘SQL Server Error Log Location’ Category

How to query and display the SQL Server Error Log location?

Even if you had installed the SQL Server instances yourself, you may not be able to have all the SQL Server Error Log locations for all the SQL Server instances on top of your head. In case of emergency, you need to review the SQL Server Error Logs for troubleshooting purposes, how can you find out the location of the error log file instantly?

You can use the built-in SERVERPROPERTY() function to query the location of the SQL Server Error Log file. The following query will get the job done. Yes. Just one line. That’s all it takes:

SELECT SERVERPROPERTY('ErrorLogFileName') AS [SQL Server Error Log Location]

If you wish to send yourself an html report or adding this informational section to your SQL Server Health Check report, the following is the code:

-- Display the SQL Server Error Log Location
 /*NOTE: Replace the recipients email addresses in line #17 */

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)
select @instancename = CONVERT(varchar, SERVERPROPERTY('SERVERNAME'));

SET @Recipients = '' -- 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)

		SELECT @TableHTML = 
		<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; "> SQL Server Error Log Location:</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>SQL Server Error Log Location</th>
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(varchar(max), SERVERPROPERTY('ErrorLogFileName')) + '</td></tr>'
		SELECT @TableHTML = @TableHTML +'</table></html>'
		-- Send email notification
		SELECT @MailSubject = CONVERT(VARCHAR(50),@instancename) + ' SQL Server Error Log Location'
		-- 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;

set nocount off	

This is an example of what it looks like:
SQL Server Error Log Location