Archive for the ‘Project: SQL Server Health Check Report’ Category

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 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