Archive for the ‘Uncategorized’ Category

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)

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

SET @Recipients = '' -- specify your recipients here, separate with ;

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

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

		SELECT @TableHTML = 
		<div style="font-family:Verdana; font-size:14pt;"><div style="font-weight:bold; width:auto; float:left;">'+@instancename+'</div><div style=" width:auto; float:left; padding-left:5px; "> 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>
		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>' +
		-- 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;
set nocount off	

This is what it looks like from the email report:
Instance Level Info

How to query the OS System Info?

If you are working on documenting your company’s SQL Server environment or simply just making assessment on the Operating System specifications and you have wondered since you have over 100 hundred SQL Server instances, how in the world do you have the time for the manual effort to logon each of the Window Servers to find their OS level information? Is there an easy way to automate this process with a TSQL script? Yes! My friends, there is. Talk is cheap. Let’s cut to the chase. Let’s review what you need to be aware of before running it and then you can just copy and paste the script into your multiple server query window after you had registered all your SQL Servers that you wish to run the script on to gather the system info.

What do you need to be aware of?

  • The script will need to turn the advanced options on in order to turn the xp_cmdshell option on.
  • If the xp_cmdshell was off, it will be turned on to load the system info data into a temp table.
  • After the system info data is extracted, the xp_cmdshell will get to turn back to off if it was off.
  • The advanced options will also get returned to its original setting.

This is what it will look like after you had run the script against one instance (click on the following image to magnify the size of the image):
System Info

Here’s the script. Enjoy!

Name: SystemInfo.sql
Description: TSQL script to query the host name, domain name, time zone, OS, system type, total physical memory, and system boot time
Developer: JP Chen
Date Created: October 30, 2013 10:00 PM
-- variables delcarations
declare @HostName nvarchar(125)
	, @DomainName nvarchar(125)
	, @TimeZone nvarchar(125)
	, @OS nvarchar(125)
	, @SystemType nvarchar(125)
	, @Processor nvarchar(125)
	, @TotalPhysicalMemory nvarchar(125)
	, @SystemBootTime nvarchar(125)
	, @xp_cmdshell bit
	, @isshowadvancedoptions bit

if  @@microsoftversion / power(2, 24) >= 9
	-- 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
		exec sp_configure 'show advanced options', 1;

	-- 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
		exec sp_configure 'xp_cmdshell', 1;
	-- 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%'
	-- processor		   							   
	select @Processor = ltrim(replace(data, 'Processor(s):','')) 
				   from #systeminfo 
				   where data like 'Processor(s)%'
	-- 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%'
	-- output
	select @HostName as [Host Name]
			, @DomainName [Domain Name]
			, @TimeZone [Time Zone]
			, @OS [OS]
			, @Processor [Processor(s)]
			, @SystemType [System Type]
			, @TotalPhysicalMemory [Total Physical Memory]							   
			, @SystemBootTime [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
		exec sp_configure 'xp_cmdshell', @xp_cmdshell;

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

How to query the job execution durations for the past 2 weeks?

You need to find the job execution durations for each of the jobs for the past 2 weeks – pronto! Your boss or client doesn’t want to hear it or care about why it may be a challenge as the run_duration column in the msdb..sysjobhistory system table is formatted in HHMMSS and it is based on the job steps and you will need to join it to the msdb..sysjobs system table to perform complicated subqueries to get the results.  What do you do – hot shot? What do you do?

You can start thinking about deploying diplomacy or beat around the bushes. But, please don’t do it, people will lose confidences in your capability, and that’s reserved for the politicians only not SQL Server DBAs. First, let’s remind yourself of who you are, be proud about it, and then let’s run this script:

-- query job execution durations for the past 2 weeks
select serverproperty('servername') as InstanceName, job_name, run_datetime, run_duration
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
        select DISTINCT
   as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
where DATEDIFF(DAY, run_datetime, GETDATE()) < 15 -- search for the # of days you wish to see the job histories
order by job_name, run_datetime

Please note that the original script was copied from I have modified it to add the instance name and filter the query to search for the past 14 days so that you can run it for multiple instances of SQL Server using a multi-server query.

I hope this will help you out of a jam and save you a few hours of creating your own script so that you can at least enjoy the 4th quarter of a Monday Night Football game.



SQL Server Script to Display Job History



How to view and purge the job history?

Have you ever right-clicked on a SQL Server Agent job and then select View History to read the history of the job to review for more details to troubleshoot the job failure? Well, you are not alone. All SQL Server database administrators have done it. The jobs will show both successful and failure histories. Ever wondered how to remove the job history that’s no longer needed to reduce the job history loading time or reduce the disk space usage for the MSDB?

Before purging the job history you should be aware that the history is stored in the MSDB.DBO.SYSJOBHISTORY system table. If you join the MSDB.DBO.SYSJOBSsystem table to it, you can view the meaning data that are relevant to the job history. Here’s one way of querying it:

-- Querying the job history information
SELECT as JobName
FROM MSDB.DBO.sysjobs jobs JOIN MSDB.DBO.sysjobhistory jobhistory
	ON jobs.job_id = jobhistory.job_id
ORDER BY jobhistory.run_date desc

SQL Server has a system stored proc to purge the job history: MSDB.DBO.SP_PURGE_JOBHISTORY. But, before running the following line of code to purge the job history, make sure you know your business rules and the number of days to retain the job history and then submit a change request. The following code can be utilized to purge the job history with a retention day of 31:

-- Remove the job history based on the retention days you wish to keep
-- Must read and know your business rules before setting your retention days
DECLARE @RetentionDays INT
,@CutOffDate VARCHAR(10)

SET @RetentionDays = 31 -- change the retention days here
SET @CutOffDate = CONVERT(VARCHAR(10), GETDATE()-@RetentionDays, 112)

-- purge the job history based on the cut off date
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @CutOffDate