I wanted to keep it simple avoid solutions involving CLR or using OLE functions. Yes, a CLR solution would probably be the best in terms of both performance and robustness, but I wanted to keep the solution within the scope of a single T-SQL function and I think the solution below works quite well.

Below you’ll find the complete code for the function along with comments and usage documentation. Please take note of the assumptions being made about the data in the code comments notes section. I did test this against a large data set with many different variations which are also mentioned in the code comments. If you come across any examples that don’t parse correctly with this code, please let me know in a comment below. Hope you enjoy.
 

SET ANSI_NULLS ON; 
GO
SET QUOTED_IDENTIFIER ON; 
GO
IF EXISTS (SELECT * FROM [sys].[objects] WHERE [object_id] = OBJECT_ID(N'[dbo].[ParseAddress]') AND [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT')) 
BEGIN 
	DROP FUNCTION [dbo].[ParseAddress]; 
END; 
GO 

/*========================================================================================================= 
Script:		dbo.ParseAddress.sql 

Synopsis:	Function to parse an address string containing city, state and zip code information. This 
			should work for any United States or Canada address string. Tested using multiple variations 
			including 5-digit zip codes, 5-digit zip code + 4-digit extension separated with 1+ space(s),
			hyphen, or no separation and Canadian 6-character postal codes with the same variations. This
			function will also discard non-alpha characters and multiple spaces used to separate city, 
			state and zip code information.

Usage:		SELECT	 [Address] 
					,[City]  = [dbo].[ParseAddress]([Address], N'City') 
					,[State] = [dbo].[ParseAddress]([Address], N'State') 
					,[Zip]   = [dbo].[ParseAddress]([Address], N'Zip') 

Notes:		The following assumptions are made about the data for parsing: 

					- Zip code information is the last part of the string
					- Zip code will always contain 5+ characters. 
					- State will always be two letters. 
					- City will always end with a letter. 

=========================================================================================================== 
Revision History: 

Date			Author				Description 
----------------------------------------------------------------------------------------------------------- 


===========================================================================================================*/ 
CREATE FUNCTION [dbo].[ParseAddress] 
( 
	@String NVARCHAR(64), 
	@Get NVARCHAR(64) 
) 
RETURNS NVARCHAR(64) 
AS 
BEGIN 
	DECLARE @Address AS NVARCHAR(64); 
	DECLARE @City    AS NVARCHAR(25); 
	DECLARE @State   AS NVARCHAR( 2); 
	DECLARE @Zip     AS NVARCHAR(10); 
	DECLARE @Index   AS TINYINT     ; 
	DECLARE @Char    AS NCHAR(    1); 
	DECLARE @Value   AS NVARCHAR(64); 

	-- Remove any leading or trailing white space
 	SET @Address = LTRIM(RTRIM(@String)); 
	-- Initialize string index
	SET @Index = 1; 
	
	WHILE (@Index <= LEN(@Address)) 
	BEGIN 
		SET @Char = SUBSTRING(REVERSE(@Address), @Index, 1); 

		IF (@Zip IS NULL OR LEN(@Zip) < 5) 
		BEGIN 
			-- Continue reading valid characters for @Zip
			WHILE (PATINDEX(N'[a-zA-Z0-9]', @Char) = 1) 
			BEGIN 
				SET @Zip = ISNULL(STUFF(@Zip, 1, 0, UPPER(@Char)), UPPER(@Char)); 
				SET @Index = @Index + 1; 
				SET @Char = SUBSTRING(REVERSE(@Address), @Index, 1); 
			END; 
		END; 

		IF (@State IS NULL OR LEN(@State) <> 2) 
		BEGIN 
			-- Continue reading valid characters for @State
			WHILE (PATINDEX(N'[a-zA-Z]', @Char) = 1) 
			BEGIN 
				SET @State = ISNULL(STUFF(@State, 1, 0, UPPER(@Char)), UPPER(@Char)); 
				SET @Index = @Index + 1; 
				SET @Char = SUBSTRING(REVERSE(@Address), @Index, 1); 
			END; 
		END; 

		-- The last character of city should be an alpha character
		IF (PATINDEX(N'[a-zA-Z]', @Char) = 1) 
		BEGIN 
			-- Just assign the rest of the string to the @City variable
			SET @City = SUBSTRING(@Address, 1, LEN(@Address) - @Index + 1); 
			BREAK; 
		END; 

		SET @Index = @Index + 1; 
	END; 

	-- Removes double-spaces from the city name 
	WHILE (CHARINDEX(SPACE(2), @City) > 0) 
	BEGIN 
		SET @City = REPLACE(@City, SPACE(2), SPACE(1)); 
	END; 

	-- Format US Postal Codes that have 4 digit extension by stuffing the hyphen in correct position 
	IF (PATINDEX(N'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]', @Zip) = 1) 
	BEGIN 
		SET @Zip = STUFF(@Zip, 6, 0, N'-'); 
	END; 

	-- Format Canadian Postal Codes by stuffing a space in correct position 
	IF (PATINDEX(N'[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]', @Zip) = 1) 
	BEGIN 
		SET @Zip = STUFF(@Zip, 4, 0, N' '); 
	END; 

	IF (@Get = N'City')  SET @Value = @City; 
	IF (@Get = N'State') SET @Value = @State; 
	IF (@Get = N'Zip')   SET @Value = @Zip; 

	RETURN @Value; 
END; 
GO

The post Using T-SQL to Parse Address City, State and Zip Code appeared first on SQL Professionals.

In this first code section is the table definition I will be using to store the data collected by the PowerShell script. Please note that I have opted to define the [UsedSpaceKB], [PercentFree] and [PercentUsed] fields as computed columns to simplify the reports which will be utilizing this data. I have also chosen to use kilobytes (kb) as my unit of measurement here because I have other solutions that monitor SQL Server table and index growth which also stores measurements in units of kb.

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_PADDING ON;
GO
CREATE TABLE [dbo].[DiskVolume]
(
	 [DiskVolumeID] INT IDENTITY(1, 1) NOT NULL
	,[CheckDate] DATETIME NOT NULL
	,[Name] VARCHAR(128) NULL
	,[Label] VARCHAR(128) NULL
	,[DriveLetter] VARCHAR(2) NOT NULL
	,[CapacityKB] BIGINT NOT NULL
	,[FreeSpaceKB] BIGINT NOT NULL
	,[UsedSpaceKB] AS ([CapacityKB] - [FreeSpaceKB])
	,[PercentFree] AS (CONVERT(DECIMAL(18, 2), CAST([FreeSpaceKB] AS FLOAT) / CAST([CapacityKB] AS FLOAT) * 100))
	,[PercentUsed] AS (CONVERT(DECIMAL(18, 2), CAST([CapacityKB] - [FreeSpaceKB] AS FLOAT) / CAST([CapacityKB] AS FLOAT) * 100))
) 
ON [PRIMARY];
GO
SET ANSI_PADDING OFF;
GO

This next code section shows the definition of the procedure used to insert data into the table above. The idea is to pass in one XML parameter as data, containing all the information collected from all the disk drives. The procedure then reads the XML text provided as input, parses the text by using the MSXML parser, and then inserts the parsed data into the table.

The second parameter specifies how many days of data to retain in the table and then the procedure purges any expired data after completing the insert.

Reference: http://msdn.microsoft.com/en-us/library/ms187367.aspx

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[InsertDiskVolume]') AND [type] IN (N'P', N'PC'))
BEGIN
	EXECUTE ('CREATE PROCEDURE [dbo].[InsertDiskVolume] AS RAISERROR(''UNDEFINED!'', 16, 1);');
END;
GO

ALTER PROCEDURE [dbo].[InsertDiskVolume]
(
	@XmlData XML,
	@HistoryDays SMALLINT = 90
)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @hdoc AS INT;

	--Create an internal representation of the XML document.
	EXECUTE [dbo].[sp_xml_preparedocument] @hdoc OUTPUT, @XmlData;

	INSERT INTO [dbo].[DiskVolume] 
	(
			 [CheckDate]
			,[Name] 
			,[Label] 
			,[DriveLetter] 
			,[CapacityKB] 
			,[FreeSpaceKB] 
	)
	SELECT	 [CheckDate] = CURRENT_TIMESTAMP
			,[Name] = [Volume].[Name]
			,[Label] = [Volume].[Label]
			,[DriveLetter] = [Volume].[DriveLetter]
			,[CapacityKB] = [Volume].[Capacity] / 1024
			,[FreeSpaceKB] = [Volume].[FreeSpace] / 1024
	FROM	 OPENXML(@hdoc, '/root/cimv2/Win32_Volume', 0) 
			 WITH 
			 (
				 [Name] VARCHAR(128) '@Name'
				,[Label] VARCHAR(128) '@Label'
				,[DriveLetter] VARCHAR(2) '@DriveLetter'
				,[Capacity] BIGINT '@Capacity'
				,[FreeSpace] BIGINT '@FreeSpace'
			 )
			 AS [Volume];

	EXECUTE [dbo].[sp_xml_removedocument] @hdoc;
	
	DELETE	
	FROM	[dbo].[DiskVolume]
	WHERE	[CheckDate] < DATEADD(d, -@HistoryDays, CURRENT_TIMESTAMP);
END;
GO

So, now that we have our table and our insert procedure defined, the next thing to do is to test the insert procedure by passing in an XML parameter just as our PowerShell script would. I found this step to be a very helpful in the development process because the XML data needs to adhere to a specific format in the PowerShell code.

To test the procedure I have created the following T-SQL code to build an XML structured string, CAST it is as an XML data type and then call the procedure and see if the table was populated with any records.

SET NOCOUNT ON;

DECLARE @XmlData AS XML = N''
DECLARE @VarData AS VARCHAR(MAX) = '';

--Create xml structured string to test our insert procedure
SET @VarData = @VarData + N'<root>';
SET @VarData = @VarData + N'<cimv2>';
SET @VarData = @VarData + N'<Win32_Volume Name="E:\" Label="SQL" DriveLetter="E:" Capacity="214612045824" FreeSpace="213974716416"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="N:\" Label="MSDTC-SQL" DriveLetter="N:" Capacity="1038086144" FreeSpace="999579648"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="M:\" Label="MSDTC" DriveLetter="M:" Capacity="1038086144" FreeSpace="999579648"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="Q:\" Label="Quorum" DriveLetter="Q:" Capacity="1038086144" FreeSpace="991666176"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="X:\" Label="SQL-Audit" DriveLetter="X:" Capacity="536734593024" FreeSpace="536624115712"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="F:\" Label="SQL-Data" DriveLetter="F:" Capacity="2684218179584" FreeSpace="1120017907712"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="J:\" Label="SQL-Index" DriveLetter="J:" Capacity="536734531584" FreeSpace="339802390528"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="G:\" Label="SQL-Log" DriveLetter="G:" Capacity="1073605443584" FreeSpace="501874098176"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="L:\" Label="SQL-Perf" DriveLetter="L:" Capacity="107237863424" FreeSpace="90478137344"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="R:\" Label="SQL-TempDBData1" DriveLetter="R:" Capacity="107237801984" FreeSpace="46044217344"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="S:\" Label="SQL-TempDBData2" DriveLetter="S:" Capacity="107237801984" FreeSpace="107143036928"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="T:\" Label="SQL-TempDBLog" DriveLetter="T:" Capacity="53550710784" FreeSpace="49429544960"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="C:\" Label="" DriveLetter="C:" Capacity="214640357376" FreeSpace="98290384896"></Win32_Volume>';
SET @VarData = @VarData + N'</cimv2>';
SET @VarData = @VarData + N'</root>';

--Cast our xml structured string to an xml type
SET @XmlData = CAST(@VarData AS XML);

--Call our procedure
EXECUTE [dbo].[InsertDiskVolume] @XmlData;

--Was the data inserted?
SELECT *
FROM [dbo].[DiskVolume];

If everything was done correctly, you should have results similar to the following screenshot:

Now all we need is the Powershell script to generate the same XML structured data to pass into the stored procedure. The following is the PowerShell code, created to generate the XML data and calls the insert procedure. You should be able to copy/paste the PowerShell code below into your IDE of choice and run it without error. I’ve highlighted line #6 where you will need to change the connection string parameters and line #11 where you will need to changed the procedure name if needed.

[object]$Volumes = Get-WmiObject -NameSpace "root/cimv2" -ComputerName "." Win32_Volume -Filter "DriveType = 3 AND Label != 'System Reserved'";
[string]$XmlData = "<root><cimv2>"; 
$Volumes | % { $XmlData = $XmlData + "<Win32_Volume Name=`"" + $_.Name + "`" Label=`"" + $_.Label + "`" DriveLetter=`"" + $_.DriveLetter + "`" Capacity=`"" + $_.Capacity + "`" FreeSpace=`"" + $_.FreeSpace + "`"></Win32_Volume>" }; 
$XmlData = $XmlData + "</cimv2></root>";
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = "Server=<ServerName/Instance>;Database=<DatabaseName>;Integrated Security=TRUE;"; 
$SqlConnection.Open();
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$SqlCommand.CommandTimeout = 120;
$SqlCommand.Connection = $SqlConnection;
$SqlCommand.CommandText = "EXECUTE [dbo].[InsertDiskVolume] @XmlData = N'$XmlData';";
$Result = $SqlCommand.ExecuteNonQuery();
$SqlConnection.Close();

The last step is to create a SQL Agent Job to run the PowerShell script on a recurring schedule. The following code will create such a job that runs every 24 hours and I’ve highlighted a few lines where changes have been made to the PowerShell code from above:

Line #45 is highlighted because I’m using the SQL Agent Token “MACH” and Escape Macro for the “Computer Name” and added a an extra set of single-quotes around “System Reserved”.

Line #48 is highlighted because I’m using the SQL Agent Token “SRVR” and Escape Macro for the Server Name running SQL Server. If the SQL Server instance is a named instance, this includes the instance name. You will still need to specify the appropriate database name and security options for the connection string.

Line #53 is highlighted because, again, you need to make sure the correct procedure name is specified and I’ve also added an extra set of single-quotes around the $XmlData PowerShell variable.

USE [msdb]; 
GO 
BEGIN TRANSACTION; 
	DECLARE @ReturnCode AS INT = 0; 
	DECLARE @jobId AS BINARY(16);
	
	IF NOT EXISTS (SELECT [name] FROM [msdb].[dbo].[syscategories] WHERE [name] = N'[Uncategorized (Local)]' AND [category_class] = 1) 
	BEGIN 
		EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_category] 
				@class=N'JOB', 
				@type=N'LOCAL', 
				@name=N'[Uncategorized (Local)]'; 
				
		IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 
	END 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_job] 
			@job_name=N'Collect Disk Space Data', 
			@enabled=1, 
			@notify_level_eventlog=0, 
			@notify_level_email=0, 
			@notify_level_netsend=0, 
			@notify_level_page=0, 
			@delete_level=0, 
			@description=N'No description available.', 
			@category_name=N'[Uncategorized (Local)]', 
			@owner_login_name=N'sa', 
			@job_id = @jobId OUTPUT; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_jobstep] 
			@job_id=@jobId, 
			@step_name=N'Query WMI', 
			@step_id=1, 
			@cmdexec_success_code=0, 
			@on_success_action=1, 
			@on_success_step_id=0, 
			@on_fail_action=2, 
			@on_fail_step_id=0, 
			@retry_attempts=0, 
			@retry_interval=0, 
			@os_run_priority=0, 
			@subsystem=N'PowerShell', 
			@command=N'[object]$Volumes = Get-WmiObject -NameSpace "root/cimv2" -ComputerName "$(ESCAPE_NONE(MACH))" Win32_Volume -Filter "DriveType = 3 AND Label != ''System Reserved''";
	[string]$XmlData = "<root><cimv2>"; $Volumes | % { $XmlData = $XmlData + "<Win32_Volume Name=`"" + $_.Name + "`" Label=`"" + $_.Label + "`" DriveLetter=`"" + $_.DriveLetter + "`" Capacity=`"" + $_.Capacity + "`" FreeSpace=`"" + $_.FreeSpace + "`"></Win32_Volume>" }; $XmlData = $XmlData + "</cimv2></root>";
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
	$SqlConnection.ConnectionString = "Server=$(ESCAPE_NONE(SRVR));Database=<DatabaseName>;Integrated Security=TRUE;"; 
	$SqlConnection.Open();
	$SqlCommand = New-Object System.Data.SqlClient.SqlCommand;
	$SqlCommand.CommandTimeout = 120;
	$SqlCommand.Connection = $SqlConnection;
	$SqlCommand.CommandText = "EXECUTE [dbo].[InsertDiskVolume] @XmlData = N''$XmlData'';";
	$Result = $SqlCommand.ExecuteNonQuery();
	$SqlConnection.Close();', 
			@database_name=N'master', 
			@flags=0; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_update_job] 
			@job_id = @jobId, 
			@start_step_id = 1; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_jobschedule] 
			@job_id=@jobId, 
			@name=N'Daily - Every 24 hour(s)', 
			@enabled=1, 
			@freq_type=4, 
			@freq_interval=1, 
			@freq_subday_type=1, 
			@freq_subday_interval=0, 
			@freq_relative_interval=0, 
			@freq_recurrence_factor=0, 
			@active_start_date=20140707, 
			@active_end_date=99991231, 
			@active_start_time=0, 
			@active_end_time=235959; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_jobserver] 
			@job_id = @jobId, 
			@server_name = N'(local)'; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

COMMIT TRANSACTION; 

GOTO EndSave 

QuitWithRollback: 
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION; 

EndSave:

Comments or suggestions are always welcome. The next blog post will be a follow-up on generating a weekly report and low-disk space alert emails. Please check back at a later time or follow us on twitter.

.

The post Monitoring Disk Space with SQL Server and PowerShell appeared first on SQL Professionals.

SELECT	 [JobName] = [jobs].[name]
		,[Category] = [categories].[name]
		,[Owner] = SUSER_SNAME([jobs].[owner_sid])
		,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Description] = [jobs].[description]
		,[Occurs] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'Once'
					WHEN   4 THEN 'Daily'
					WHEN   8 THEN 'Weekly'
					WHEN  16 THEN 'Monthly'
					WHEN  32 THEN 'Monthly relative'
					WHEN  64 THEN 'When SQL Server Agent starts'
					WHEN 128 THEN 'Start whenever the CPU(s) become idle' 
					ELSE ''
				END
		,[Occurs_detail] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'O'
					WHEN   4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
					WHEN   8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' + 
						LEFT(
							CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END , 
							LEN(
								CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END 
							) - 1
						)
					WHEN  16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
					WHEN  32 THEN 'The ' + 
							CASE [schedule].[freq_relative_interval]
								WHEN  1 THEN 'First'
								WHEN  2 THEN 'Second'
								WHEN  4 THEN 'Third'
								WHEN  8 THEN 'Fourth'
								WHEN 16 THEN 'Last' 
							END +
							CASE [schedule].[freq_interval]
								WHEN  1 THEN ' Sunday'
								WHEN  2 THEN ' Monday'
								WHEN  3 THEN ' Tuesday'
								WHEN  4 THEN ' Wednesday'
								WHEN  5 THEN ' Thursday'
								WHEN  6 THEN ' Friday'
								WHEN  7 THEN ' Saturday'
								WHEN  8 THEN ' Day'
								WHEN  9 THEN ' Weekday'
								WHEN 10 THEN ' Weekend Day' 
							END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' 
					ELSE ''
				END
		,[Frequency] = 
				CASE [schedule].[freq_subday_type]
					WHEN 1 THEN 'Occurs once at ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 2 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 4 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 8 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					ELSE ''
				END
		,[AvgDurationInSec] = CONVERT(DECIMAL(18, 2), [jobhistory].[AvgDuration])
		,[Next_Run_Date] = 
				CASE [jobschedule].[next_run_date]
					WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')
					ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' + 
						 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
				END
FROM	 [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK) 
		 LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK) 
				 ON [jobs].[job_id] = [jobschedule].[job_id] 
		 LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK) 
				 ON [jobschedule].[schedule_id] = [schedule].[schedule_id] 
		 INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK) 
				 ON [jobs].[category_id] = [categories].[category_id] 
		 LEFT OUTER JOIN 
					(	SELECT	 [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) + 
																(([run_duration] % 10000) / 100 * 60) + 
																 ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
						FROM	 [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
						WHERE	 [step_id] = 0 
						GROUP BY [job_id]
					 ) AS [jobhistory] 
				 ON [jobhistory].[job_id] = [jobs].[job_id];
GO

Reference: http://msdn.microsoft.com/en-us/library/ms178644.aspx

The post Insight into the SQL Server Agent Job Schedules appeared first on SQL Professionals.

What is an SQL Server Health Check?

A Health Check is an in-depth, comprehensive audit of your SQL Server database: its configuration, hardware, performance, stability, backup plans, and needs. It lets your DBA know what’s causing problems, what’s working great, and how to develop a plan for improvement.

At SQL Professionals, we pride ourselves on the extremely detailed and action-oriented reports that our Health Checks yield. While too many DBAs produce reports that gloss over facts or make fuzzy claims that could apply to 95% of databases, our reports are precise, easy to understand, and empowering (yes, empowering). Ideally, a Health Check doesn’t just identify problems: it lays out a roadmap for how to solve them.

How Your Business Benefits

Sounds good, you say, but what’s in it for my business?

Peace of Mind. Your SQL Server is a complicated piece of work that your business relies on every day. Just thinking about the number of things that could go wrong is enough to keep you up at night. A thorough Health Check means no SQL Server surprises will throw your business for a loop.

Plan of Action. Your Health Check will identify immediate concerns, long-term projects, and small tweaks to speed up sluggish performance issues—and prioritize them into a clear plan. SQL Professionals will let you know exactly how we’ll work on your servers, how long it’ll take, and what improvements you can expect to reap in return.

Long-Term Performance. A crucial part of every Health Check is an audit of current and future performance scalability. You’ll understand how much of a load your existing environment can take while still performing at an acceptable rate. Even better, you’ll know what it’ll take to scale up, so you can prepare for your long-term needs now.

New Perspective. Even if you already have an in-house DBA, the remote DBAs at SQL Professionals provide a collection of fresh eyes to evaluate your database issues and develop novel solutions. SQL Server database administration is a science—but it’s also an art. There’s more than one way to approach any given problem, and sometimes it takes an outsider’s perspective to spot the best way around a difficulty.

The post Why a SQL Server Health Check is the Best First Step appeared first on SQL Professionals.

Great databases are like great referees: they’re at their best when they go unnoticed. It’s only when something goes wrong—a blown call, unexpected downtime—that you take notice. Not good.

The solution, of course, is obvious: hire a dedicated SQL database administrator to keep your business humming. Unfortunately, hiring a (good) full-time DBA is expensive. Not only are SQL Server specialists a hot commodity, but you’ve also got to factor in benefits, taxes, office space, equipment, etc. “Ouch,” says your budget.

Unlike their full-time counterparts, a remote SQL DBA can keep your databases and your bottom line at their best. How? Let me count the ways.

1. Lower Costs

You’re not paying less for sub-standard talent, you’re just paying selectively for talent when you actually need it. Thanks to the flexible pricing most remote DBAs offer, you don’t need to pay a DBA for 40 hours a week when your databases only require 15 hours of work.

2. Expertise

Remote SQL Server DBAs aren’t jacks of all trades: they’re masters of one, and they continually hone their specialized skills. That means faster solutions, less trial and error, and experience you can trust.

With SQL Professionals, you don’t just get one SQL DBA—you benefit from our combined knowledge, experience, and expertise. We can even bring in Microsoft engineers and other outside specialists, thanks to our robust network.

3. Reliability

By contracting a team of remote SQL Server DBAs, you can be confident someone’s always on call, waiting to tamp down any problems at a moment’s notice. SQL Professionals prides itself on rapid responses and quick turn-around times; when you need support, we’ll have a DBA available who intimately understands your particular SQL environment.

4. Stability

With quality DBAs being so sought after, companies face a brutally high attrition rate. That means investing more time and money into training a new DBA every year or so. With the remote DBAs at SQL Professionals, you’ve got an entire team working for you. Even if you’re just using us part-time, we offer stability between the departures of your on-site DBAs and can help transfer knowledge and protocol seamlessly.

5. Supplementing Your Team

Even if you’ve already got a few DBAs, you may want to consider supplementing them with remote DBA support. Whether you need us to handle daily maintenance while they tackle high-visibility projects, or you give us the unexpected to keep your employees undistracted, we work harmoniously with existing admins.

The post 5 Ways a Remote SQL DBA Will Benefit Your Business appeared first on SQL Professionals.