Archive for April, 2014

How to query and display the SQL Server port #?

Yes. You can query and display the SQL Server port # through TSQL. You can do so by querying the Windows Registry using the xp_regread stored proc. The following code is how it can be done:

DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @tcp_port OUTPUT

SELECT SERVERPROPERTY('SERVERNAME') AS [Instance Name]
,  @tcp_port AS [SQL Server Port #]

If you wish to send yourself an html report or you will be adding this section to your SQL Server Health Check Report, you can use the following code:

-- Query and display and SQL Server port #
 /*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)
, @TableHTML VARCHAR(MAX)

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


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

DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @tcp_port OUTPUT

-- 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; "> SQL Server Port #:</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>Instance Name</th>
		<th style="border: 1px solid #000000;" align=center>Port #</th>
	</tr>'
		
	SELECT @TableHTML = @TableHTML +
		'<tr>' +
		'<td style="border: 1px solid #000000;">' + @instancename + '</td>'+
		'<td style="border: 1px solid #000000;">' + @tcp_port  + '</td></tr>'

	SELECT @TableHTML = @TableHTML +'</table></html>'
			
	-- Send email notification
	SELECT @MailSubject = @instancename + ' SQL Server Port #'
		
	-- 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 it looks like:
SQL Server Port #

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)
, @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; "> 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>
		</tr>'
		
		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;
	END

set nocount off	

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

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

How to query the Windows Registry and display the SQL Server services accounts?

You wish to find out the accounts that are being used as the startup accounts for the SQL Server service and SQL Server Agent service. Rather than having to RDP into the server and then opening the SQL Server Configuration Manager to view all the “Log On As” accounts, is there an easier way? Yes there is. You can query the Windows Registry by using the XP_REGREAD stored proc. Here’s the code to get you more than what you need:

IF OBJECT_ID('TEMPDB..#SQLServerServiceAccounts') IS NOT NULL
DROP TABLE #SQLServerServiceAccounts


/*============================================
sql server services credentials.
identify the services credentials for the sql server service, sql server agent service, sql server analysis service, 
sql server integration service, sql server reporting service, sql server fulltext search service, sql server browser service, 
sql server active directory helper, sql server vss writer.
  ============================================*/  
--set nocount on 
if object_id('tempdb..#servicecredentials') is not null
	drop table #servicecredentials;
if object_id('tempdb..#services') is not null
	drop table #services;
if object_id('tempdb..#getinstances') is not null
	drop table #getinstances;
if object_id('tempdb..#tchkkey') is not null 
	drop table #tchkkey

create table #tchkkey (keyexist int)
	
create table #servicecredentials
	([servername] nvarchar(255)
	,[servicename] nvarchar(255)
	,[serviceaccount] nvarchar(255))

create table #services
	(serviceid int identity(1,1)
	,servicename nvarchar(255))
	
create table #getinstances
	(instanceid int identity(1,1)
	,value nvarchar(100)
	,instancenames nvarchar(100)
	,data nvarchar(100))

-- list of sql server services in registry
insert into #services (servicename) values  ('mssql$')
insert into #services (servicename) values  ('mssqlserver')
insert into #services (servicename) values  ('sqlagent$')
insert into #services (servicename) values  ('sqlserveragent')
insert into #services (servicename) values  ('msolap$')
insert into #services (servicename) values  ('mssqlserverolapservice')
insert into #services (servicename) values  ('reportserver$')
insert into #services (servicename) values  ('reportserver')
insert into #services (servicename) values  ('msdtsserver')
insert into #services (servicename) values  ('msdtsserver100')
insert into #services (servicename) values  ('msftesql$')
insert into #services (servicename) values  ('sqlbrowser')
insert into #services (servicename) values  ('mssqlserveradhelper')
insert into #services (servicename) values  ('mssqlserveradhelper100')
insert into #services (servicename) values  ('sqlwriter')
insert into #services (servicename) values  ('msftesql$')
insert into #services (servicename) values  ('msftesql')
insert into #services (servicename) values  ('mssqlfdlauncher$')
insert into #services (servicename) values  ('mssqlfdlauncher')

insert into #getinstances (value, instancenames, data)
execute xp_regread
  @rootkey = 'hkey_local_machine',
  @key = 'software\microsoft\microsoft sql server',
  @value_name = 'installedinstances'

declare  @servicename varchar(255)
		,@serviceinsname varchar(255)
		,@serviceaccountname varchar(255) 
		,@key_value varchar(100)
		,@serviceid int
		,@instanceid int
		,@instancename varchar(100)
set @serviceid = 0
set @instanceid = 0
------------------------------------
while exists (select 1 from #services where serviceid > @serviceid)
begin
	select top 1 @serviceid = s.serviceid, @servicename = s.servicename from #services s where s.serviceid > @serviceid order by s.serviceid asc

	if @servicename like '%$%' 
	begin 
		while exists (select 1 from #getinstances where instanceid > @instanceid)
		begin
			select top 1 @instanceid = i.instanceid, @instancename = i.instancenames from #getinstances i where i.instanceid > @instanceid order by i.instanceid asc
			set @serviceinsname = @servicename + @instancename 

			set @key_value = 'system\currentcontrolset\services\' + @serviceinsname

			-- make sure registry branch is existing
			insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
			-- make sure registry branch is existing
			if exists(select * from #tchkkey where keyexist = 1)	
				execute master..xp_regread 'hkey_local_machine', @key_value, 'displayname', @serviceinsname output
			truncate table #tchkkey
		
			-- make sure registry branch is existing
			insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
			-- make sure registry branch is existing
			if exists(select * from #tchkkey where keyexist = 1)
				execute master..xp_regread 'hkey_local_machine', @key_value, 'objectname', @serviceaccountname output
			truncate table #tchkkey

			if @serviceaccountname is not null
			begin
				insert into #servicecredentials
				(servername, servicename, serviceaccount)
				values
				(cast(serverproperty('servername') as nvarchar), @serviceinsname, @serviceaccountname)
				set @serviceaccountname = null
			end
		end
		set @instanceid = 0
	end
	else
	begin
		set @key_value = 'system\currentcontrolset\services\' + @servicename

		-- make sure registry branch is existing
		insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
		-- make sure registry branch is existing
		if exists(select * from #tchkkey where keyexist = 1)
			execute master..xp_regread 'hkey_local_machine', @key_value, 'displayname', @servicename output
		truncate table #tchkkey
		
		-- make sure registry branch is existing
		insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
		-- make sure registry branch is existing
		if exists(select * from #tchkkey where keyexist = 1)
			execute master..xp_regread 'hkey_local_machine', @key_value, 'objectname', @serviceaccountname output
		truncate table #tchkkey
		
		if @serviceaccountname is not null
		begin
			insert into #servicecredentials
			(servername, servicename, serviceaccount)
			values
			(cast(serverproperty('servername') as nvarchar), @servicename, @serviceaccountname)
			set @serviceaccountname = null
		end
	end
end


SELECT sc.servicename AS [ServiceName], 
	sc.serviceaccount AS [ServiceAccount]
FROM #servicecredentials sc

In case you wish to send this out as an html report via email, this is how to do it:

/*============================================
sql server services credentials.
identify the services credentials for the sql server service, sql server agent service, sql server analysis service, 
sql server integration service, sql server reporting service, sql server fulltext search service, sql server browser service, 
sql server active directory helper, sql server vss writer.
  ============================================*/  
 /*NOTE: Replace the recipients email addresses in line #154 */

set nocount on
declare @instance_name varchar(55);
select @instance_name = CONVERT(varchar, SERVERPROPERTY('SERVERNAME'));

IF OBJECT_ID('TEMPDB..#SQLServerServiceAccounts') IS NOT NULL
DROP TABLE #SQLServerServiceAccounts

if object_id('tempdb..#servicecredentials') is not null
	drop table #servicecredentials;
if object_id('tempdb..#services') is not null
	drop table #services;
if object_id('tempdb..#getinstances') is not null
	drop table #getinstances;
if object_id('tempdb..#tchkkey') is not null 
	drop table #tchkkey

create table #tchkkey (keyexist int)
	
create table #servicecredentials
	([servername] nvarchar(255)
	,[servicename] nvarchar(255)
	,[serviceaccount] nvarchar(255))

create table #services
	(serviceid int identity(1,1)
	,servicename nvarchar(255))
	
create table #getinstances
	(instanceid int identity(1,1)
	,value nvarchar(100)
	,instancenames nvarchar(100)
	,data nvarchar(100))

-- list of sql server services in registry
insert into #services (servicename) values  ('mssql$')
insert into #services (servicename) values  ('mssqlserver')
insert into #services (servicename) values  ('sqlagent$')
insert into #services (servicename) values  ('sqlserveragent')
insert into #services (servicename) values  ('msolap$')
insert into #services (servicename) values  ('mssqlserverolapservice')
insert into #services (servicename) values  ('reportserver$')
insert into #services (servicename) values  ('reportserver')
insert into #services (servicename) values  ('msdtsserver')
insert into #services (servicename) values  ('msdtsserver110')
insert into #services (servicename) values  ('msftesql$')
insert into #services (servicename) values  ('sqlbrowser')
insert into #services (servicename) values  ('mssqlserveradhelper')
insert into #services (servicename) values  ('mssqlserveradhelper110')
insert into #services (servicename) values  ('sqlwriter')
insert into #services (servicename) values  ('msftesql$')
insert into #services (servicename) values  ('msftesql')
insert into #services (servicename) values  ('mssqlfdlauncher$')
insert into #services (servicename) values  ('mssqlfdlauncher')

insert into #getinstances (value, instancenames, data)
execute xp_regread
  @rootkey = 'hkey_local_machine',
  @key = 'software\microsoft\microsoft sql server',
  @value_name = 'installedinstances'

declare  @servicename varchar(255)
		,@serviceinsname varchar(255)
		,@serviceaccountname varchar(255) 
		,@key_value varchar(100)
		,@serviceid int
		,@instanceid int
		,@instancename varchar(100)
set @serviceid = 0
set @instanceid = 0
------------------------------------
while exists (select 1 from #services where serviceid > @serviceid)
begin
	select top 1 @serviceid = s.serviceid, @servicename = s.servicename from #services s where s.serviceid > @serviceid order by s.serviceid asc

	if @servicename like '%$%' 
	begin 
		while exists (select 1 from #getinstances where instanceid > @instanceid)
		begin
			select top 1 @instanceid = i.instanceid, @instancename = i.instancenames from #getinstances i where i.instanceid > @instanceid order by i.instanceid asc
			set @serviceinsname = @servicename + @instancename 

			set @key_value = 'system\currentcontrolset\services\' + @serviceinsname

			-- make sure registry branch is existing
			insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
			-- make sure registry branch is existing
			if exists(select * from #tchkkey where keyexist = 1)	
				execute master..xp_regread 'hkey_local_machine', @key_value, 'displayname', @serviceinsname output
			truncate table #tchkkey
		
			-- make sure registry branch is existing
			insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
			-- make sure registry branch is existing
			if exists(select * from #tchkkey where keyexist = 1)
				execute master..xp_regread 'hkey_local_machine', @key_value, 'objectname', @serviceaccountname output
			truncate table #tchkkey

			if @serviceaccountname is not null
			begin
				insert into #servicecredentials
				(servername, servicename, serviceaccount)
				values
				(cast(serverproperty('servername') as nvarchar), @serviceinsname, @serviceaccountname)
				set @serviceaccountname = null
			end
		end
		set @instanceid = 0
	end
	else
	begin
		set @key_value = 'system\currentcontrolset\services\' + @servicename

		-- make sure registry branch is existing
		insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
		-- make sure registry branch is existing
		if exists(select * from #tchkkey where keyexist = 1)
			execute master..xp_regread 'hkey_local_machine', @key_value, 'displayname', @servicename output
		truncate table #tchkkey
		
		-- make sure registry branch is existing
		insert into #tchkkey execute master..xp_regread 'hkey_local_machine', @key_value
		-- make sure registry branch is existing
		if exists(select * from #tchkkey where keyexist = 1)
			execute master..xp_regread 'hkey_local_machine', @key_value, 'objectname', @serviceaccountname output
		truncate table #tchkkey
		
		if @serviceaccountname is not null
		begin
			insert into #servicecredentials
			(servername, servicename, serviceaccount)
			values
			(cast(serverproperty('servername') as nvarchar), @servicename, @serviceaccountname)
			set @serviceaccountname = null
		end
	end
end

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

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;">'+@instance_name+'</div><div style=" width:auto; float:left; padding-left:5px; "> SQL Server Services Accounts:</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>Service Name</th>
			<th style="border: 1px solid #000000;" align=center>Service Account</th>
		</tr>'
		
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td style="border: 1px solid #000000;">' + [servicename] + '</td>' +
			'<td style="border: 1px solid #000000;">' + [serviceaccount] + '</td></tr>'FROM #servicecredentials 

		SELECT @TableHTML = @TableHTML +'</table></html>'
			
		
		-- Send email notification
		SELECT @MailSubject = @instance_name + ' SQL Server Services Accounts'
		
		-- 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	

Here’s an example of what it looks like:
SQL Server Instances and Service Accounts

How to query and display the disk drives space and utilization information?

Have you ever wondered how to query and display the disk drives space and utilization information? By utilizing the OLE Automation Objects in TSQL, now you can. You can read more about OLE Automation Objects here: http://technet.microsoft.com/en-us/library/ms189933.aspx. You will need to enable ‘ole automation procedures’ before you can use it.
The following is the code to show the:

  • Drive
  • Drive Label
  • Total Capacity MB
  • Space Used MB
  • Free Space MB
  • % Free

It is critical to note that it does not work for mounted volumes. If you are not sure what a mounted volume is then probably you don’t need to concern about it yet. When you figured out on how to modify to get the following code work for mounted volumes, please share the code with me.

Here we go:

-- disk drives space and usage info

-- turn on show advanced options
if  @@microsoftversion / power(2, 24) >= 9
begin

	declare @oleautomationprocedures bit
	declare @isshowadvancedoptions bit
	
	-- check if enabled 'show advanced options', if no enable it
	select @isshowadvancedoptions = convert(int, isnull(value, value_in_use))
	from  sys.configurations
	where  name = 'show advanced options'	
	if @isshowadvancedoptions = 0
	begin
		exec sp_configure 'show advanced options', 1;
		reconfigure;
	end

	-- check if enabled 'ole automation procedures', if not then enable it
	select @oleautomationprocedures = convert(int, isnull(value, value_in_use)) 
	from  sys.configurations
	where  name = 'ole automation procedures' ;
	if @oleautomationprocedures = 0
	begin
		exec sp_configure 'ole automation procedures', 1;
		reconfigure;
	end
	
end

set nocount on
declare @hr int;
declare @fso int;
declare @driveletter char(1);
declare @odrive int;
declare @totalsize varchar(20);
declare @volumename nvarchar(256);
declare @mb numeric; 
set @mb = 1048576;
set @driveletter = '';

if object_id('tempdb..#fixeddrives') is not null
	drop table #fixeddrives;

create table #fixeddrives (
	[Drive]	varchar(10) primary key, -- drive letter
	[Drive Label] nvarchar(256), -- drive label
	[Total Capacity MB] numeric(10,2), -- total capacity of the drive in mb
	[Space Used MB] numeric(10,2), -- space used in mb
	[Free Space MB] numeric(10,2), -- free space in mb
	[% Free] numeric(10,2) -- percentage free as of now
);

insert into #fixeddrives([Drive], [Free Space MB]) 
exec master..xp_fixeddrives;

exec @hr=sp_OACreate 'scripting.filesystemobject',@fso out 
if @hr <> 0 exec sp_oageterrorinfo @fso

while exists (select 1 from #fixeddrives d where d.Drive > @driveletter)
begin
	select top 1 @driveletter = d.Drive 
	from #fixeddrives d 
	where d.Drive > @driveletter 
	order by d.Drive asc

	exec @hr = sp_OAMethod @fso,'getdrive', @odrive out, @driveletter; 
		if @hr <> 0 exec sp_oageterrorinfo @fso;
	
	exec @hr = sp_OAGetProperty @odrive,'totalsize', @totalsize out;
		if @hr <> 0 exec sp_oageterrorinfo @odrive;

	exec @hr = sp_OAGetProperty @odrive,'volumename', @volumename out;
		if @hr <> 0 exec sp_oageterrorinfo @fso;
	
	update #fixeddrives 
	set [Total Capacity MB]= @totalsize/@mb ,
			[Drive Label]= @volumename
	where
		Drive = @driveletter; 
		
end

exec @hr=sp_OADestroy @fso; 

if @hr <> 0 
exec sp_oageterrorinfo @fso;

update #fixeddrives 
set [Space Used MB]= [Total Capacity MB] - [Free Space MB],
		[% Free] = [Free Space MB]* 100/[Total Capacity MB];

select [Drive]
, [Drive Label]
, [Total Capacity MB]
, [Space Used MB]
, [Free Space MB]
, [% Free]
from #fixeddrives		

-- revert the option on show advanced options
if  @@microsoftversion / power(2, 24) >= 9
begin
	if (select convert(int, isnull(value, value_in_use)) from sys.configurations
		where  name = 'ole automation procedures') <> @oleautomationprocedures
	begin
		exec sp_configure 'ole automation procedures', @oleautomationprocedures;
		reconfigure;
	end

	if (select convert(int, isnull(value, value_in_use)) from sys.configurations
		where  name = 'show advanced options') <> @isshowadvancedoptions
	begin	
		exec sp_configure 'show advanced options', @isshowadvancedoptions
		reconfigure with override
	end
	
end

In case you wish to send a report via email for the disk drives space and utilization information, this is the code:

-- disk drives space and usage info
/*NOTE: Replace the recipients email addresses in line #128 */

set nocount on
-- turn on show advanced options
if  @@microsoftversion / power(2, 24) >= 9
begin

	declare @oleautomationprocedures bit
	declare @isshowadvancedoptions bit
	
	-- check if enabled 'show advanced options', if no enable it
	select @isshowadvancedoptions = convert(int, isnull(value, value_in_use))
	from  sys.configurations
	where  name = 'show advanced options'	
	if @isshowadvancedoptions = 0
	begin
		exec sp_configure 'show advanced options', 1;
		reconfigure;
	end

	-- check if enabled 'ole automation procedures', if not then enable it
	select @oleautomationprocedures = convert(int, isnull(value, value_in_use)) 
	from  sys.configurations
	where  name = 'ole automation procedures' ;
	if @oleautomationprocedures = 0
	begin
		exec sp_configure 'ole automation procedures', 1;
		reconfigure;
	end
	
end


declare @hr int;
declare @fso int;
declare @driveletter char(1);
declare @odrive int;
declare @totalsize varchar(20);
declare @volumename nvarchar(256);
declare @mb numeric; 
declare @instancename varchar(55);
set @mb = 1048576;
set @driveletter = '';
select @instancename = CONVERT(varchar, SERVERPROPERTY('SERVERNAME'));


if object_id('tempdb..#fixeddrives') is not null
	drop table #fixeddrives;

create table #fixeddrives (
	[Drive]	varchar(10) primary key, -- drive letter
	[Drive Label] nvarchar(256), -- drive label
	[Total Capacity MB] numeric(10,2), -- total capacity of the drive in mb
	[Space Used MB] numeric(10,2), -- space used in mb
	[Free Space MB] numeric(10,2), -- free space in mb
	[% Free] numeric(10,2) -- percentage free as of now
);

insert into #fixeddrives([Drive], [Free Space MB]) 
exec master..xp_fixeddrives;

exec @hr=sp_OACreate 'scripting.filesystemobject',@fso out 
if @hr <> 0 exec sp_oageterrorinfo @fso

while exists (select 1 from #fixeddrives d where d.Drive > @driveletter)
begin
	select top 1 @driveletter = d.Drive 
	from #fixeddrives d 
	where d.Drive > @driveletter 
	order by d.Drive asc

	exec @hr = sp_OAMethod @fso,'getdrive', @odrive out, @driveletter; 
		if @hr <> 0 exec sp_oageterrorinfo @fso;
	
	exec @hr = sp_OAGetProperty @odrive,'totalsize', @totalsize out;
		if @hr <> 0 exec sp_oageterrorinfo @odrive;

	exec @hr = sp_OAGetProperty @odrive,'volumename', @volumename out;
		if @hr <> 0 exec sp_oageterrorinfo @fso;
	
	update #fixeddrives 
	set [Total Capacity MB]= @totalsize/@mb ,
			[Drive Label]= @volumename
	where
		Drive = @driveletter; 
		
end

exec @hr=sp_OADestroy @fso; 

if @hr <> 0 
exec sp_oageterrorinfo @fso;

update #fixeddrives 
set [Space Used MB]= [Total Capacity MB] - [Free Space MB],
		[% Free] = [Free Space MB]* 100/[Total Capacity MB];


-- revert the option on show advanced options
if  @@microsoftversion / power(2, 24) >= 9
begin
	if (select convert(int, isnull(value, value_in_use)) from sys.configurations
		where  name = 'ole automation procedures') <> @oleautomationprocedures
	begin
		exec sp_configure 'ole automation procedures', @oleautomationprocedures;
		reconfigure;
	end

	if (select convert(int, isnull(value, value_in_use)) from sys.configurations
		where  name = 'show advanced options') <> @isshowadvancedoptions
	begin	
		exec sp_configure 'show advanced options', @isshowadvancedoptions
		reconfigure with override
	end
end



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

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; "> Disk Drives Space and Utilization Information:</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>Drive</th>
			<th style="border: 1px solid #000000;" align=center>Drive Label</th>
			<th style="border: 1px solid #000000;" align=center>Total Capacity [MB]</th>
			<th style="border: 1px solid #000000;" align=center>Space Used [MB]</th>
			<th style="border: 1px solid #000000;" align=center>Free Space [MB]</th>
			<th style="border: 1px solid #000000;" align=center>% Free</th>
		</tr>'
		
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td style="border: 1px solid #000000;">' + [Drive] + '</td>' +
			'<td style="border: 1px solid #000000;">' + [Drive Label]+ '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(varchar, [Total Capacity MB]) + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(varchar,[Space Used MB]) + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(varchar,[Free Space MB]) + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(varchar,[% Free]) + '</td></tr>'FROM #fixeddrives

		SELECT @TableHTML = @TableHTML +'</table></html>'
			
		
		-- Send email notification
		SELECT @MailSubject = @instancename + ' Disk Drives Space and Utilization Information'
		
		-- 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	

Here’s an example of what the report look like via email:
Disk Drives Space and Utilization Informaiton

How to query and display the instance level information?

Rather than right-clicking on the instance in SQL Server Management Studio (SSMS) and then clicking on properties to view the server properties, you can query and view them by using the SERVERPROPERTY() system function. The following is the TSQL code:

-- SQL Server Instance Level Info
SELECT SERVERPROPERTY('ServerName') AS [InstanceName]
	,SERVERPROPERTY('ProductVersion') AS [VersionBuild]
	,SERVERPROPERTY ('Edition') AS [Edition]
	,SERVERPROPERTY('ProductLevel') AS [ServicePack]
	,SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsWindowsAuthentication]
	,SERVERPROPERTY('Collation') AS [Collation]
	,SERVERPROPERTY('IsClustered') AS [IsClustered]
	,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName]

To send this report to yourself or the recipients through email, you can use the following code:

-- SQL Server Instance Level Information
-- SQL Server instance name, version, edition, service pack, authentication, collation, clustering, and current node name
/* Note: Change the recipients in line #19 */

set nocount on

-- declare the required variables 
DECLARE @Recipients VARCHAR(123) -- recipients for this alert
DECLARE @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@companyname.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; "> Instance Level Information:</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>Instance Name</th>
			<th style="border: 1px solid #000000;" align=center>SQL Server Version</th>
			<th style="border: 1px solid #000000;" align=center>Edition</th>
			<th style="border: 1px solid #000000;" align=center>Service Pack</th>
			<th style="border: 1px solid #000000;" align=center>Is Windows Authentication?</th>
			<th style="border: 1px solid #000000;" align=center>Server Collation</th>
			<th style="border: 1px solid #000000;" align=center>Is Clustered?</th>
			<th style="border: 1px solid #000000;" align=center>Current Node Name</th>
		</tr>'
		
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(nvarchar, SERVERPROPERTY('ServerName'))  + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(nvarchar, SERVERPROPERTY('ProductVersion')) + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(nvarchar, SERVERPROPERTY ('Edition'))  + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(nvarchar, SERVERPROPERTY('ProductLevel'))  + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(nvarchar, SERVERPROPERTY('IsIntegratedSecurityOnly'))  + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(nvarchar, SERVERPROPERTY('Collation'))  + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(nvarchar, SERVERPROPERTY('IsClustered'))  + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(nvarchar, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))  + '</td>' +
			'</tr></table></html>'
		
		-- Send email notification
		SELECT @MailSubject = @instancename + ' Instance Level Info'
		
		-- 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 what it looks like from the email report:
Instance Level Info

How to query and display the Operating System level information?

While considered as informational, the Operating System level information is critical to knowing the limitation for upgrades and SQL Server configurations. In this blog, we will query and display the following details and send the report to our email addresses:

  • Host name
  • Domain name
  • Time zone
  • Operating System version, edition, and service pack
  • System type
  • Total RAM in MB
  • System Boot Time
  • # of Logical CPUs
  • Virtual Machine Type

These are the same type of information you can find by running the systeminfo.exe in the command prompt. To get these information displayed through TSQL, you will need to enable xp_cmdshell.

Here’s the code:

-- Operating System Level Information
-- OS Version and Edition, Service Pack, # of CPUs, Total Physical Memory, Clustering, Virtual Machine
/* Note: Change the recipients in line #128 */

set nocount on
-- variables delcarations
declare @HostName nvarchar(125)
	, @DomainName nvarchar(125)
	, @TimeZone nvarchar(125)
	, @OS nvarchar(125)
	, @SystemType nvarchar(125)
	, @TotalPhysicalMemory nvarchar(125)
	, @SystemBootTime nvarchar(125)
	, @xp_cmdshell bit
	, @isshowadvancedoptions bit
	, @CPUs tinyint
	, @VirtualMachineType nvarchar(25)

if  @@microsoftversion / power(2, 24) >= 9
begin
	-- check if enabled 'show advanced options', if no enable it
	select @isshowadvancedoptions = convert(int, isnull(value, value_in_use))
	from  sys.configurations
	where  name = 'show advanced options'	

	if @isshowadvancedoptions = 0
	begin
		exec sp_configure 'show advanced options', 1;
		reconfigure;
	end

	-- check if enabled 'xp_cmdshell', if not then enable it
	select @xp_cmdshell = convert(int, isnull(value, value_in_use)) 
	from  sys.configurations
	where  name = 'xp_cmdshell';
	
	if @xp_cmdshell = 0
	begin
		exec sp_configure 'xp_cmdshell', 1;
		reconfigure;
	end
	
	-- drop the #systeminfo if it exists
	if object_id('tempdb..#systeminfo') is not null
	drop table #systeminfo;
	
	-- create the #systeminfo table
	create table #systeminfo 	
	(data nvarchar(255))
	
	-- load the systeminfo into the #systeminfo table
	insert into #systeminfo 
	exec master..xp_cmdshell 'systeminfo'
	


	-- host name
	select @HostName = ltrim(replace(data, 'Host Name:',''))
								   from #systeminfo
								   where data like 'Host Name%'
	-- domain name
	select @DomainName = ltrim(replace(data, 'Domain:',''))
								   from #systeminfo
								   where data like 'Domain%'

	-- time zone
	select @TimeZone = ltrim(replace(data, 'Time Zone:',''))
								   from #systeminfo
								   where data like 'Time Zone%'

	-- os		   							   
	select @OS = (select ltrim(replace(data, 'os name:','')) 
				   from #systeminfo 
				   where data like 'os name%') 
				  + right(@@version, len(@@version)+3- charindex ('build', @@version))

	-- system type		   							   
	select @SystemType = ltrim(replace(data, 'System Type:','')) 
				   from #systeminfo 
				   where data like 'System Type%'
		      
	-- system type		   							   
	select @SystemType = ltrim(replace(data, 'System Type:','')) 
				   from #systeminfo 
				   where data like 'System Type%'				   

	-- total physical memory		
	select @TotalPhysicalMemory = ltrim(replace(data, 'Total Physical Memory:',''))
								   from #systeminfo
								   where data like 'Total Physical Memory%'

	-- system boot time		
	select @SystemBootTime = ltrim(replace(data, 'System Boot Time:',''))
								   from #systeminfo
								   where data like 'System Boot Time%'

	-- revert the option on show advanced options
	if (select convert(int, isnull(value, value_in_use)) from sys.configurations
		where  name = 'xp_cmdshell') <> @xp_cmdshell
	begin
		exec sp_configure 'xp_cmdshell', @xp_cmdshell;
		reconfigure;
	end

	if (select convert(int, isnull(value, value_in_use)) from sys.configurations
		where  name = 'show advanced options') <> @isshowadvancedoptions
	begin	
		exec sp_configure 'show advanced options', @isshowadvancedoptions
		reconfigure with override
	end			
	
end 	

-- CPU count, isClustered, CurrentNode, and Virtual Machine Type
SELECT @CPUs = cpu_count
, @VirtualMachineType = virtual_machine_type_desc
FROM sys.dm_os_sys_info


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

SET @Recipients = 'email@companyname.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;">'+@@servername+'</div><div style=" width:auto; float:left; padding-left:5px; "> Operating System Level Information:</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>Host Name</th>
			<th style="border: 1px solid #000000;" align=center>Domain Name</th>
			<th style="border: 1px solid #000000;" align=center>Time Zone</th>
			<th style="border: 1px solid #000000;" align=center>Operating System</th>
			<th style="border: 1px solid #000000;" align=center>System Type</th>
			<th style="border: 1px solid #000000;" align=center>Total RAM [MB]</th>
			<th style="border: 1px solid #000000;" align=center>System Boot Time</th>
			<th style="border: 1px solid #000000;" align=center># of Logical CPUs</th>
			<th style="border: 1px solid #000000;" align=center>Virtual Machine Type</th>
		</tr>'
		
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td style="border: 1px solid #000000;">' + @HostName + '</td>' +
			'<td style="border: 1px solid #000000;">' + @DomainName+ '</td>' +
			'<td style="border: 1px solid #000000;">' + @TimeZone + '</td>' +
			'<td style="border: 1px solid #000000;">' + @OS + '</td>' +
			'<td style="border: 1px solid #000000;">' + @SystemType + '</td>' +
			'<td style="border: 1px solid #000000;">' + @TotalPhysicalMemory + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(VARCHAR, @SystemBootTime, 13) + '</td>' +
			'<td style="border: 1px solid #000000;">' + CONVERT(VARCHAR, @CPUs) + '</td>' +
			'<td style="border: 1px solid #000000;">' + @VirtualMachineType + '</td></tr></table></html>'
		
		-- Send email notification
		SELECT @MailSubject = CONVERT(VARCHAR(50),@@servername) + ' OS Level Info'
		
		-- 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 what it looks like from the email report:
OS Level Info

How to create individual job step output files as per the execution times and purge them on a scheduled time?

Pop Quiz again HotShot! There’s a critical SQL Server Agent job that was created N years ago by some very seniored SQL Server DBAs with collaborations with what some had claimed the smartest SQL Server Developers under the sky. The job has more than 23 steps and the last step is to notify the VIPs (Very Import People) in your company on the results. The job is scheduled to run daily and it fails on step 5 and step 13 intermittently for the past 6 months. Everyone was too busy to attempt to fix this job failure that’s very critical to company. You are the newly hired SQL Server DBA. On your first day on the new job, you have been delegated to work with the Development team resolve this job failure. You will need to enable the job output and make sure they do not get overwritten so that the Development team get to review the job output log files for troubleshooting. What do you do? What do you do?! Remember: there’s no crying for DBAs! Let’s attempt our usual troubleshooting methodology: 1.Fact finding. 2. Investigation and research. 3. Exploring and testing your options in a testing environment. 4. Rollout to production when ready. 5. Get a Grande Frappuccino from the nearest Starbucks.
1. Facts:
1) Steps 5 and 13 have been failing intermittently for the past 6 months.
2) Step 5 has not job output.
3) Step 13 has job output and on each execution the last job out will overwrite the previous one as per the default option. In case the latest execution is successful, it will overwrite the previous job failure output. Therefore, you will not be able to see the previous failure or any other failure before it.

2. After some research and investigation, you find that:
1) You will need to enable tokens in order to dynamically generate the output file name. See “Use Tokens in Job Steps” for more details: http://msdn.microsoft.com/en-us/library/ms175575.aspx
2) After you had enabled the token, you will need to add in the “Output file:” textbox the directory for the job output, the job step name, and the date and time variables to dynamically generate the output file. For example: C:\OutputFiles\Example_Step1_$(ESCAPE_NONE(DATE))_$(ESCAPE_NONE(TIME)).log
3) To purge the old output file, you can use a Powershell script to delete the *.log files in the job output file rather than using the xp_delete_file system proc. I know I had blogged about it a few months back, but it has a mind of its own and it’s not worth to waste our time on it any further.

3. Test the solution based on our research and investigation in a testing environment.
1) Enable the token:

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO

2) Create a test job. For example: “Test Job Output.”
3) Add a job step to run some basic select statements. For example:

SELECT SERVERPROPERTY('SERVERNAME') [Instance_Name], GETDATE() [Time_Now]

4) Click on the “Advanced” page and add in the “Output file:” text box:

C:\OutputFiles\Example_Step1_$(ESCAPE_NONE(DATE))_$(ESCAPE_NONE(TIME)).log

Where:
C:\OutputFiles\ is the existing directory for the output files.
Test_Step1 is the job step name.
_$(ESCAPE_NONE(DATE))_$(ESCAPE_NONE(TIME)) is the date and time of the job execution.
5) After the test job is created in step 4), run it 3 times or more.
6) Browse to the job output folder, you will then see the job outputs for each of the executions you had performed in step 5).
JobOutputFiles
7) To purge the output files older than 7 days or set your retention days, create a purge job and embed the following Powershell script. Set the retention day as the value for $Days and in the job step type, you will need to select “PowerShell”:

#----- define parameters -----#
#----- get current date ----#
$Now = Get-Date 
#----- define amount of days ----#
$Days = "7"
#----- define folder where files are located ----#
$TargetFolder = "C:\OutputFiles\"
#----- define extension ----#
$Extension = "*.log"
#----- define LastWriteTime parameter based on $Days ---#
$LastWrite = $Now.AddDays(-$Days)

#----- get files based on lastwrite filter and specified folder ---#
$Files = Get-Childitem $TargetFolder -Include $Extension -Recurse | Where {$_.LastWriteTime -le "$LastWrite"}

foreach ($File in $Files) 
    {
    if ($File -ne $NULL)
        {
        Remove-Item $File.FullName | out-null
        }
    }

PowerShell_Script_To_Purge_JobOutput_Files

4. Rollout to production when ready.
You may need to submit a Change Request ticket, get the approval, or do some demonstrations to show and proof that it works before you can rollout to the production. If you encounter bureaucratic roadblocks, you are on your own. Watch some “Meet the Press” or “Face the Nation” on Sunday mornings to get some exposures on how to handle them diplomatically. ESPN SportCenter can’t help you on this subject matter.

5. Get a Grande Frappuccino from the nearest Starbucks and enjoy!

How to view the description of the last reboot of the Windows Server?

Most likely, you have experienced the scenario that your monitoring software or your own customize monitoring alerted you that SQL Server had restarted and you wish to find out the reasons on why it was restarted. You looked into the SQL Server Error Logs, but no details on the restarts. You wanted to look into the Event Viewer to find more details. It contains the Application, Internet Explorer, Security, and System sections. Each one of them has hundreds to thousands of entries. Mindboggling! What should you do? You can try to filter the System event logs on the “Event source” by the category of “USER32” to get started.

Steps:
1. Browse to “Computer Management,” under “System Tools” expand the “Event Viewer” main folder.
2. Right-click on “System” and then select “Properties.”
3. On the “System Properties” dialog box, click on the “Filter” tab.
4. On the “Event source:” drop-down box, select “USER32” and then click on the “OK” button:
Event_Source_USER32
5. Click on the most recent entry of the event on the result set to view the description of the reboot and the timestamp along with other relevant details:
USER32_Description