Archive

Posts Tagged ‘T-SQL’

PowerShell Function to Automate Availability Group Failover

November 18, 2017 1 comment

I’ve been working with Availability Groups (AG) since their release in SQL Server 2012 and have always wanted to leverage PowerShell to administer them.  Recently I received a request to develop an automated process for failing Availability Groups over gracefully prior to server patching.  Believe it or not, but a hard shutdown of the primary replica is NOT the best way to force AG failover.

The function takes a replica name as input and queries system tables for Availability Groups running as secondary that are online, healthy, and synchronous.  For each AG found, the function generates an ALTER AVAILABILITY GROUP statement.  If the -noexec parm is set to 0, the command will be executed.  If -noexec is set to 1, the command will be written out to a file.

When writing the function, I started out trying to use the native PowerShell Availability Group cmdlets.  After several false starts, I found it easier to develop the T-SQL code in Management Studio and use Invoke-Sqlcmd to execute the code.  The code is available below.  I hope you can put it to use.


<#
Author: Frank Gill
Date: 2017-11-17
#>
function Invoke-AgFailover {
<#
.SYNOPSIS
Checks specified instance for healthy, synchronous Availability Groups running as secondary and
fails them over
.DESCRIPTION
Checks the instance passed in for healthy, synchronous Availability Groups running as secondary and
fails them over.  If the instance is not hosting secondary replicas, a message will be output.
If there are AGs running as secondary, a message will output for each, including AG name, destination,
and failover duration.
.EXAMPLE
Invoke-AgFailover -Instance YourSecondaryInstance -NoExec 0;
Any Availability Groups running as secondary on YourSecondaryInstance will be failed over.
.EXAMPLE
Invoke-AgFailover -Instance YourSecondaryInstance -NoExec 1;
If Availability Groups are running as secondary on YourSecondaryInstance, T-SQL commands for each AG failover
will be generated and written to C:\AGFailover\failover_YourAgName_YYYYMMDD_HHMMSS.sql.
.PARAMETER Instance
The instance to check for secondary replicas.
.PARAMETER NoExec
Set to 1 to generate T-SQL script for failover.
#>
[CmdletBinding()]
param
(
[Parameter(Mandatory=$True,
Position = 1,
ValueFromPipeline=$True,
ValueFromPipelineByPropertyName=$True,
HelpMessage='Which instance do you want to check for secondary replicas?')]
[Alias('secondaryinstance')]
[string[]]$instance,

[Parameter(Mandatory=$True,
Position = 2,
ValueFromPipeline=$True,
ValueFromPipelineByPropertyName=$True,
HelpMessage='Set to 1 if you want to execute the database restore.  Otherwise ')]
[Alias('dontrun')]
[string]$noexec
)
process
{
<# If the $noexec is set to 1, create file path to hold out files #>
if($noexec -eq 1)
{
$rundate = Get-Date -Format yyyyMMdd_HHmmss;
$outpath = "C:\AgFailover";
if((Test-Path -Path $outpath) -eq $true)
{
Remove-Item -Path "$outpath\*" -Recurse;
}
else
{
New-Item -Path $outpath -ItemType Directory;
}
}

<# Create query to check for failover-eligible AGs #>
$query = "SELECT g.[name], ar.replica_server_name
FROM sys.dm_hadr_availability_replica_states r
INNER JOIN sys.availability_replicas ar
ON ar.group_id = r.group_id
AND ar.replica_id = r.replica_id
INNER JOIN sys.availability_groups g
ON g.group_id = ar.group_id
WHERE r.role_desc = N'SECONDARY'
AND r.recovery_health_desc = N'ONLINE'
AND r.synchronization_health_desc = N'HEALTHY'
AND ar.availability_mode_desc = N'SYNCHRONOUS_COMMIT';"

<# Execute failover-eligible query #>
$secondaries = Invoke-Sqlcmd -ServerInstance "$instance" -Database master -Query $query;

<# Output message if there are no failover-eligible AGs #>
if($secondaries.Count -eq 0)
{
Write-Output "There are no Availability Group replicas available to fail over to $instance."
}

<# If eligible AGs exist, loop through them #>
foreach($secondary in $secondaries)
{
$secreplica = $secondary.replica_server_name;
$ag = $secondary.name;

$query = "ALTER AVAILABILITY GROUP $ag FAILOVER;"

<# If $noexec is set to 0, execute the AG failover
and output a message when complete #>
if($noexec -eq 0)
{
$starttime = Get-Date;
Invoke-Sqlcmd -ServerInstance "$instance" -Database master -Query $query;
$endtime = Get-Date;
$duration = (New-TimeSpan -Start $starttime -End $endtime).Seconds;
Write-Output "Failed Availability Group $ag to replica $instance in $duration seconds";
}
<# If $noexec is not set to 0, write a file out to the path built above for each AG #>
else
{
$comment = "/* Run against instance $instance */" ;
$comment | Out-File -FilePath "$outpath\failover_$ag`_$rundate.sql" -Append;
$use = "USE master;";
$use | Out-File -FilePath "$outpath\failover_$ag`_$rundate.sql" -Append;
$query | Out-File -FilePath "$outpath\failover_$ag`_$rundate.sql" -Append;
}
}
}
}

Validate SQL Server Database Mail Settings

November 13, 2017 1 comment

In my last post, I shared a script to automate the migration of SQL Server Database Mail settings. In this post, I show how to send test e-mails from all Database Mail profiles on an instance. The migration I was working on contained 21 Database Mail profiles. The following script will send a test e-mail from each profile to confirm successful configuration. I hope you can put this code to use in your migrations.

USE msdb;

SET NOCOUNT ON;

/* Drop and create temp table
   and declare local variables */
DROP TABLE IF EXISTS #profiles;

CREATE TABLE #profiles
(RowId INT IDENTITY(1,1)
,ProfileName SYSNAME);

/* Update @recipient 
   Set @noexec = 1 to print the EXEC sp_send_dbmail statements
   Set @noexec = 0 to execute the statements immediately */
DECLARE @profilename SYSNAME,
@sqlstr NVARCHAR(2000),
@loopcount INT = 1,
@looplimit INT,
@noexec INT = 1,
@recipient NVARCHAR(255) = N'fgill@concurrency.com';

/* Insert Database Mail profile names into temp table
   and initialize the loop limit variable */
INSERT INTO #profiles
(ProfileName)
SELECT name
FROM sysmail_profile;

SELECT @looplimit = MAX(RowId) FROM #profiles;

/* Loop through the list of Database Mail profiles and 
   execute sp_send_dbmail for each one */
WHILE @loopcount <= @looplimit
BEGIN

	SELECT @profilename = ProfileName FROM #profiles WHERE RowId = @loopcount;


	SELECT @sqlstr = CONCAT('EXEC msdb.dbo.sp_send_dbmail  
		@profile_name = ''',
	@profilename,''',  
		@recipients = ''', @recipient, ''',  
		@body = ''Database mail succeeded for ', @profilename, ' on SQLWEBDB-01.'',  
		@subject = ''Database mail test for ', @profilename, ';''');

	SELECT @loopcount += 1;

	IF @noexec = 1
	BEGIN

		PRINT @sqlstr;

	END
	ELSE
	BEGIN

		EXEC sp_executesql @sqlstr;

	END
		
END

Migrating SQL Server Database Mail Settings

November 6, 2017 3 comments

This week, I was working on a migration for a client.  The migration was moving databases from a stand-alone instance to a two-node Availability Group.  When it came to moving the Database Mail settings, I discovered they had 21 sets of profiles and accounts.  Not wanting to manually create 42 Database Mail profiles, I set out to automate the process.  A web search yielded this blog post by Iain Elder. This script does what I was looking for, but would only generate settings for a single Database Mail profile.  Using Iain’s code as a starting point, I modified it to create Database Mail settings for all profiles on an instance.  The script is listed below. I hope this simplifies your SQL Server migrations.

USE msdb;
GO

/*
	Author -		Frank Gill, Concurrency
	Date -			2017-08-18
	Description -	Script queries sys tables to generate dynamic SQL statements
					to recreate database mail accounts and profiles
					Code modified to generate multiple mail profiles from the script found here:
					https://basetable.wordpress.com/2012/04/03/script-out-database-mail-settings/
	Note -			Execute query in SQL Server Management Studio and output Results to Text (Ctrl + T)
					Copy the results to a separate query window 
					For mail acounts with passwords, the password will appear as 'NotTheRealPassword'
					You will need to change this to the real password
					Each result set will contain a header row of Text
					Do a group replace (Ctrl + H) of Text to --Text to comment the headers out
					
*/

SET NOCOUNT ON;

/* Drop and create temp table 
   Declare local variables */
IF OBJECT_ID('tempdb.dbo.#sysmail_info') IS NOT NULL
BEGIN

	DROP TABLE #sysmail_info;

END

CREATE TABLE #sysmail_info
(RowId INT IDENTITY(1,1)
,account_name SYSNAME
,email_address NVARCHAR(120)
,display_name NVARCHAR(128)
,replyto_address NVARCHAR(128)
,[description] NVARCHAR(256)
,servername SYSNAME
,servertype SYSNAME
,[port] INT
,credential_identity NVARCHAR(4000)
,use_default_credentials BIT
,enable_ssl BIT
,profile_name SYSNAME
,sequence_number INT
,database_principal_name SYSNAME NULL
,is_default BIT);

DECLARE @loopcount INT = 1,
@looplimit INT,
@SQLText VARCHAR(MAX),
@ProfileText VARCHAR(MAX),
@CrLf CHAR(2) = CHAR(13) + CHAR(10);

/* Insert the required database mail info into the temp table */
INSERT INTO #sysmail_info
(account_name
,email_address
,display_name
,replyto_address
,[description]
,servername
,servertype
,[port]
,credential_identity
,use_default_credentials
,enable_ssl
,profile_name
,sequence_number
,database_principal_name
,is_default)
SELECT a.name AS account_name,
a.email_address,
a.display_name,
a.replyto_address,
a.[description],
servername,
servertype,
[port],
c.credential_identity,
s.use_default_credentials,
s.enable_ssl,
p.name AS profile_name,
pa.sequence_number,
dp.name AS database_principal_name,
pp.is_default
FROM msdb.dbo.sysmail_profile AS p
INNER JOIN msdb.dbo.sysmail_profileaccount AS pa ON
  p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account AS a ON
  pa.account_id = a.account_id
LEFT OUTER JOIN msdb.dbo.sysmail_principalprofile AS pp ON
  p.profile_id = pp.profile_id
LEFT OUTER JOIN msdb.sys.database_principals AS dp ON
  pp.principal_sid = dp.sid
LEFT OUTER JOIN msdb.dbo.sysmail_server AS s ON
  a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials AS c ON
  s.credential_id = c.credential_id;

/* Set loop limit to max RowId value */
SELECT @looplimit = MAX(RowId) FROM #sysmail_info;

/* Generate commands to enable database mail */
SELECT @SQLText = '
EXEC msdb.dbo.sp_configure
	@configname = ''show advanced options'',
	@configvalue = 1;
RECONFIGURE;

EXEC msdb.dbo.sp_configure
	@configname = ''Database Mail XPs'',
	@configvalue = 1;
RECONFIGURE;';

SELECT @SQLText AS [Text];

/* Loop through each row in the temp table 
   build commands to recreate database mail accounts and profiles */
WHILE @loopcount <= @looplimit
BEGIN

	SELECT @SQLText = '

	EXECUTE msdb.dbo.sysmail_add_profile_sp
	  @profile_name = ''' + profile_name + ''',
	  @description  = ''' + ISNULL([description],'') + ''';

	EXEC msdb.dbo.sysmail_add_account_sp
	  @account_name = ' + CASE WHEN account_name IS NULL THEN 'NULL' ELSE + '''' + account_name + '''' END + ',
	  @email_address = ' + CASE WHEN email_address IS NULL THEN 'NULL' ELSE + '''' + email_address + '''' END + ',
	  @display_name = ' + CASE WHEN display_name IS NULL THEN 'NULL' ELSE + '''' + display_name + '''' END + ',
	  @replyto_address = ' + CASE WHEN replyto_address IS NULL THEN 'NULL' ELSE + '''' + replyto_address + '''' END + ',
	  @description = ' + CASE WHEN [description] IS NULL THEN 'NULL' ELSE + '''' + [description] + '''' END + ',
	  @mailserver_name = ' + CASE WHEN servername IS NULL THEN 'NULL' ELSE + '''' + servername + '''' END + ',
	  @mailserver_type = ' + CASE WHEN servertype IS NULL THEN 'NULL' ELSE + '''' + servertype + '''' END + ',
	  @port = ' + CASE WHEN [port] IS NULL THEN 'NULL' ELSE + '''' + CONVERT(VARCHAR,[port]) + '''' END + ',
	  @username = ' + CASE WHEN credential_identity IS NULL THEN 'NULL' ELSE + '''' + credential_identity   + '''' END + ',
	  @password = ' + CASE WHEN credential_identity IS NULL THEN 'NULL' ELSE + '''NotTheRealPassword''' END + ',
	  @use_default_credentials = ' + CASE WHEN use_default_credentials = 1 THEN '1' ELSE '0' END + ',
	  @enable_ssl = ' + CASE WHEN enable_ssl = 1 THEN '1' ELSE '0' END + ';

	EXEC msdb.dbo.sysmail_add_profileaccount_sp
	  @profile_name = ''' + profile_name + ''',
	  @account_name = ''' + account_name + ''',
	  @sequence_number = ' + CAST(sequence_number AS NVARCHAR(3)) + ';
	' +
	  COALESCE('
	EXEC msdb.dbo.sysmail_add_principalprofile_sp
	  @profile_name = ''' + profile_name + ''',
	  @principal_name = ''' + database_principal_name + ''',
	  @is_default = ' + CAST(is_default AS NVARCHAR(1)) + ';
	', '')
	FROM #sysmail_info
	WHERE RowId = @loopcount;

	WITH R2(N) AS (SELECT 1 UNION ALL SELECT 1),
	R4(N) AS (SELECT 1 FROM R2 AS a CROSS JOIN R2 AS b),
	R8(N) AS (SELECT 1 FROM R4 AS a CROSS JOIN R4 AS b),
	R16(N) AS (SELECT 1 FROM R8 AS a CROSS JOIN R8 AS b),
	R32(N) AS (SELECT 1 FROM R16 AS a CROSS JOIN R16 AS b),
	R64(N) AS (SELECT 1 FROM R32 AS a CROSS JOIN R32 AS b),
	R128(N) AS (SELECT 1 FROM R64 AS a CROSS JOIN R64 AS b),
	Tally(N) AS (
	  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
	  FROM R128
	),
	SplitText(SplitIndex, [Text]) AS (
	  SELECT
		N,
		SUBSTRING(
		  @CrLf + @SQLText + @CrLf,
		  N + DATALENGTH(@CrLf),
		  CHARINDEX(
			@CrLf,
			@CrLf + @SQLText + @CrLf,
			N + DATALENGTH(@CrLf)
		  ) - N - DATALENGTH(@CrLf)
		)
	  FROM Tally
	  WHERE
		N < DATALENGTH(@CrLf + @SQLText) AND
		SUBSTRING(@CrLf + @SQLText + @CrLf, N, DATALENGTH(@CrLf)) = @CrLf
	)
	SELECT [Text]
	FROM SplitText
	ORDER BY SplitIndex;

	SELECT @loopcount += 1;

END

Return SQL Server Context Settings from sys.query_context_settings

October 30, 2017 1 comment

Today, I attended Erin Stellato’s (b|t) precon session Solving Common Performance Problems Using Query Store. In her presentation, Erin showed that the same query text can return multiple results in Query Store if the context settings for the queries are different. The context settings can be found in the sys.query_context_settings DMV in the set_options column. set_options is stored as a hex value. The following query will return the hex and integer value for each row in the table (NOTE: Query store must be enabled for the database to return values):

USE YourQueryStoreDatabase;

SELECT set_options, CONVERT(INT, set_options) AS IntSetOptions
FROM sys.query_context_settings;

The set_options value represents a bit mask, with each binary digit representing a specific set option. The full list of values can be found here. I created stored procedure ReturnSetOptions to take the IntSetOptions from the query above and return the set options represented. The code for the procedure is listed below.


/* Change database context if you want to create the procedure in
a database other than master */
USE master;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE IF EXISTS ReturnSetOptions;
GO
/* =============================================
-- Author:		Frank Gill
-- Create date: 2017-10-30
-- Description:	Stored Procedure to return SET options for a INT context_settings value
-- Sample Execution: EXEC ReturnSetOptions @ContextSettings = 251;
-- =============================================*/
CREATE PROCEDURE ReturnSetOptions
@ContextSettings INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DROP TABLE IF EXISTS #binaryvalues;

/* Create temp table to hold each binary position and its associated
context setting */
CREATE TABLE #binaryvalues
(RowId INT IDENTITY(1,1)
,BinaryValue INT
,BinaryFlag BIT
,SetOption SYSNAME);

/* Insert context setting information into temp table
Values found here
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-plan-attributes-transact-sql*/
INSERT INTO #binaryvalues
(BinaryValue
,BinaryFlag
,SetOption)
VALUES
(1,0,N'ANSI_PADDING'),
(2,0,N'Parallel Plan'),
(4,0,N'FORCEPLAN'),
(8,0,N'CONCAT_NULL_YIELDS_NULL'),
(16,0,N'ANSI_WARNINGS'),
(32,0,N'ANSI_NULLS'),
(64,0,N'QUOTED_IDENTIFIER'),
(128,0,N'ANSI_NULL_DFLT_ON'),
(256,0,N'ANSI_NULL_DFLT_OFF'),
(512,0,N'NoBrowseTable'),
(1024,0,N'TriggerOneRow'),
(2048,0,N'ResyncQuery'),
(4096,0,N'ARITH_ABORT'),
(8192,0,N'NUMERIC_ROUNDABORT'),
(16384,0,N'DATEFIRST'),
(32768,0,N'DATEFORMAT'),
(65536,0,N'LanguageID'),
(131072,0,N'UPON'),
(262144,0,N'ROWCOUNT');
--SELECT * FROM #binaryvalues;

/* Declare local variables */

DECLARE @RowId INT,
@BinaryValue INT,
@ModValue INT,
@DivValue INT,
@OddValue INT,
@MaxRowId INT,
@BinaryChar CHAR(1),
@BinaryString VARCHAR(MAX) = '';

/* Determine if input value is odd
Used for the one position of the binary value */
SELECT @OddValue = @ContextSettings % 2;

/* Return the largest value and row id from the temp table
less than the input value */
SELECT @RowId = RowId, @BinaryValue = BinaryValue
FROM #binaryvalues
WHERE BinaryValue <= @ContextSettings;

/* Loop backwards through the rows in the temp table
chacking to see if the binary position should be set */
WHILE @RowId > 1
BEGIN

/* Calculate the modulus and dividend for the largest binary value */
SELECT @ModValue = @ContextSettings % @BinaryValue;
SELECT @DivValue = @ContextSettings / @BinaryValue;

/* If the dividend is 1, the current binary position is set to 1*/
IF @DivValue = 1
BEGIN

UPDATE #binaryvalues
SET BinaryFlag = 1
WHERE RowId = @RowId;

END

/* Update the input value to the modulus for use in the next calculation */
SELECT @ContextSettings = @ModValue;

/* If @ContextSettings is greater than 1 get the previous values from the
temp table
Otherwise, short circuit the loop */
IF @ContextSettings > 1
BEGIN

SELECT @RowId = RowId, @BinaryValue = BinaryValue
FROM #binaryvalues
WHERE BinaryValue <= @ContextSettings;

END
ELSE
BEGIN

SELECT @RowId = 1;

END

END

/* If the input value is odd, set the 1 position to 1 */
IF @OddValue = 1
BEGIN

UPDATE #binaryvalues
SET BinaryFlag = 1
WHERE BinaryValue = 1;

END

/* Get the max row with a binary position set */
SELECT @MaxRowId = MAX(RowId)
FROM #binaryvalues
WHERE BinaryFlag = 1;

/* Loop through and concatenate the binary flag values
to generate the binary number equivalent to the input value */
WHILE @MaxRowId >= 1
BEGIN

SELECT @BinaryChar = CAST(BinaryFlag AS CHAR(1))
FROM #binaryvalues
WHERE RowId = @MaxRowId

SELECT @BinaryString = CONCAT(@BinaryString, @BinaryChar)

SELECT @MaxRowId -= 1;

END

/* Select the binary number */
SELECT @BinaryString AS BinaryValue;

/* Select the set options indicated by the binary number */
SELECT SetOption
FROM #binaryvalues
WHERE BinaryFlag = 1;

END
GO

Thanks to Erin for an excellent and informative training session.

Joining sys.dm_exec_requests to sp_whoisactive results

October 28, 2015 1 comment

Previously, I’ve blogged about returning formatted estimated_completion_time values from sys.dm_exec_requests and getting sortable output from sp_whoisactive. In this post I combine the two, joining the two result sets together. In my environment, we use Ola Hallengren’s Maintenance Solution to run our backups. Because the backup job defaults to run backups for all user databases, it can be difficult to tell which database is currently being backed up.

USE [master]
GO
 
--Drop procedure if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_join_whoisactive_execrequests]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sp_join_whoisactive_execrequests
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
-- Author:      Frank Gill
-- Create date: 2015-10-27
-- Description: This procedure executes Adam Machanic's sp_whoisactive, dumps the results to a temp table, and then joins those results to 
-- sys.dm_exec_requests.  This will return estimated completion time for the following commands along with the statement being executed
-- in a single pass:
-- ALTER INDEX REORGANIZE
-- AUTO_SHRINK option with ALTER DATABASE
-- BACKUP DATABASE
-- DBCC CHECKDB
-- DBCC CHECKFILEGROUP
-- DBCC CHECKTABLE
-- DBCC INDEXDEFRAG
-- DBCC SHRINKDATABASE
-- DBCC SHRINKFILE
-- RECOVERY
-- RESTORE DATABASE,
-- ROLLBACK
-- TDE ENCRYPTION
-- ============================================= */
CREATE PROCEDURE sp_join_whoisactive_execrequests
AS
BEGIN
 
	IF OBJECT_ID('tempdb..#WhoIsActive') IS NOT NULL 
	BEGIN
		SELECT 'Dropping'
		DROP TABLE #WhoIsActive
	END
 
	CREATE TABLE #WhoIsActive
	([dd hh:mm:ss.mss] VARCHAR(20)
	,[dd hh:mm:ss.mss (avg)] VARCHAR(20)
	,[session_id] SMALLINT
	,[sql_text] XML
	,[sql_command] XML
	,[login_name] SYSNAME
	,[wait_info] NVARCHAR(4000)
	,[tran_log_writes] NVARCHAR(4000)
	,[CPU] VARCHAR(30)
	,[tempdb_allocations] VARCHAR(30)
	,[tempdb_current] VARCHAR(30)
	,[blocking_session_id] SMALLINT
	,[blocked_session_count] VARCHAR(30)
	,[reads] VARCHAR(30)
	,[writes] VARCHAR(30)
	,[physical_reads] VARCHAR(30)
	,[query_plan] XML
	,[used_memory] VARCHAR(30)
	,[status] VARCHAR(30)
	,[tran_start_time] DATETIME
	,[open_tran_count] VARCHAR(30)
	,[percent_complete] VARCHAR(30)
	,[host_name] SYSNAME
	,[database_name] SYSNAME
	,[program_name] SYSNAME
	,[start_time] DATETIME
	,[login_time] DATETIME
	,[request_id] INT
	,[collection_time] DATETIME)

	/* Execute sp_whoisactive and write the result set to the temp table created above */
	EXEC master.dbo.sp_WhoIsActive
	@get_plans = 2, 
	@get_outer_command = 1, 
	@get_transaction_info = 1, 
	@get_avg_time = 1, 
	@find_block_leaders = 1,
	@destination_table = #WhoIsActive

	/* Join the #whoisactive temp table to sys.dm_exec_requests to get estimated completion time and query information in one pass */ 
	SELECT CASE WHEN ((r.estimated_completion_time/1000)%3600/60) < 10 THEN '0' +
	CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%3600/60) 
	ELSE CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%3600/60)
	END  + ':' + 
	CASE WHEN ((r.estimated_completion_time/1000)%60) < 10 THEN '0' +
	CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%60)
	ELSE CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%60)
	END
	AS [Time Remaining],
	r.percent_complete,
	r.session_id,
	w.login_name,
	w.[host_name],
	w.sql_command,
	w.sql_text 
	FROM #WhoIsActive w
	RIGHT OUTER JOIN sys.dm_exec_requests r
	ON r.session_id = w.session_id
	WHERE r.percent_complete > 0;
 
END

This procedure takes the output from sp_whoisactive and joins it to sys.dm_exec_requests on session_id, returning the estimated completion time and current backup statement in a single pass. Here is a sample result set from the procedure (Click to Enlarge):

SpWhoIsActiveExecRequestsResults

The sql_command column contains the full command being run, in this example Ola Hallengren’s DatabaseBackup procedure. The sql_text column contains the actual SQL statement being run, BACKUP DATABASE [AdventureWorks2012] TO DISK…. The DatabaseBackup procedure runs database backups in alphabetical order based on the database name, so this allows tracking a job progress.

Many thanks to Adam Machanic (b|t) for sp_whoisactive, Ola Hallengren (b|t) for his Maintenance Solution, and Aaron Bertrand (b|t) for his advice on formatting the estimated completion time more elegantly. I finally had an opportunity to use it.

I hope you can put this procedure to good use. Let me know if you have any questions.

Generate ALTER Statements for Database Moves

October 13, 2014 1 comment

Last week, I posted Identifying Corrupt Rows USING DBCC CHECKDB and DBCC PAGE. I wrote the scripts in that post to identify corrupt rows in a client’s database. As we continued to investigate, things got strange. Repeated runs of DBCC CHECKDB were generating different results. Each one showed serious corruption, but on different pages with each run. In an effort to remove the storage system from the list of potential causes, I needed to move to data and log files for the database to a a different storage array. This post from TechNet gave me the steps I needed to run.

In this case, only one database had to be moved. To move more than one database, I wrote the following script to generate the statements specified in the TechNet article. In addition to the ALTER DATABASE statements, the script checks to see if the database is part of an availability group. If it is, a statement is generated to remove it. As adding a database to an availability group is far more complicated than removing it, I will leave that to the user.

As usual, I hope you find this useful. And remember, executing code you find on the internet against your production instance always has the potential to become a resume updating event. Use caution and test.

/*---------------------------------------------------------------
Created By - Frank Gill
Created On - 2014-10-13
 
Script to generate ALTER DATABASE statement to move a database to
a different storage location
---------------------------------------------------------------*/

USE master;

SET NOCOUNT ON;

/* Drop temp table if it exists */

IF OBJECT_ID('tempdb..#dbfiles') IS NOT NULL
BEGIN

	DROP TABLE #dbfiles;

END

/* Declare local variables and create temp table */
DECLARE @dbname SYSNAME;
DECLARE @agname SYSNAME;
DECLARE @sqlstr NVARCHAR(4000);
DECLARE @physicalname NVARCHAR(260);
DECLARE @logicalname SYSNAME;
DECLARE @filetype TINYINT;
DECLARE @datapath NVARCHAR(1000);
DECLARE @logpath NVARCHAR(1000);
DECLARE @agdb BIT = 0;
DECLARE @loopcount INT = 1;
DECLARE @looplimit INT;

CREATE TABLE #dbfiles
(logicalname SYSNAME
,physicalname NVARCHAR(260)
,filetype TINYINT)

/* Initialize variables */
SET @dbname = 'Your_Database';
SET @datapath = 'Your_Data_Path';
SET @logpath = 'Your_Log_Path';

/* If the database is part of an availability group, generate a statement to remove it prior to taking the database offline */
IF(SELECT replica_ID FROM sys.databases WHERE name = @dbname) IS NOT NULL
BEGIN

	PRINT '/* This database is part of an availability group and must be removed prior to taking the database offline
   You will need to add the database back to the availability group after the database files are moved */'

	SELECT @agname = g.name FROM sys.databases d
	INNER JOIN sys.availability_replicas r
	ON d.replica_id = r.replica_id
	INNER JOIN sys.availability_groups g
	ON g.group_id = r.group_id
	WHERE d.name = @dbname;

	SET @sqlstr = 'ALTER AVAILABILITY GROUP [' + @agname + ']
REMOVE DATABASE [' + @dbname + '];';

	PRINT @sqlstr;
	
	SET @agdb = 1;

END

/* Generate a statement to set the database offline */
SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
SET OFFLINE WITH ROLLBACK IMMEDIATE';

PRINT @sqlstr;

/* Get a list of database files for your database and insert them into the temp table for use in the loop */
SET @sqlstr = 'INSERT INTO #dbfiles
SELECT name, physical_name, type FROM ' + @dbname + '.sys.database_files;'

EXEC sp_executesql @sqlstr;

/* Set the loop limit to the number of files returned from the insert statement */
SET @looplimit = @@ROWCOUNT;

WHILE @loopcount &lt;= @looplimit
BEGIN

	/* Get the first set of physical and logical file name and generate an ALTER DATABASE statement using the file type to determine the new physical location */

	SELECT TOP 1 @logicalname = logicalname, @physicalname =  REVERSE(SUBSTRING(REVERSE(physicalname),1,(CHARINDEX('\',REVERSE(physicalname))-1))), @filetype = filetype FROM #dbfiles;

	IF @filetype = 0
	BEGIN

 SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
MODIFY FILE 
(NAME = ' + @logicalname + ' 
,FILENAME = ''' + @datapath + @physicalname + '''' + ');';
 
 END
 ELSE IF @filetype = 1
 BEGIN

 SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
MODIFY FILE 
(NAME = ' + @logicalname + ' 
,FILENAME = ''' + @logpath + @physicalname + '''' + ');';
 END

	PRINT @sqlstr;

	SET @loopcount += 1;

	DELETE TOP (1) FROM #dbfiles 

END

/* Generate a statement to bring the database back online */
SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
SET ONLINE';

PRINT @sqlstr;


IF @agdb = 1
BEGIN

	PRINT '/* Reminder!  ' + @dbname + 'is part of an availability group 
You will need to add the database back to the availability group if necessary */'

END

Identifying Corrupt Rows Using DBCC CHECKDB and DBCC PAGE

October 6, 2014 1 comment

Four months ago, when I blogged about giving all the presentations, things were starting to ramp up pat my new job. I’ve really enjoyed the last few months, but the combination of my presenting schedule and the responsibilities of the new gig has left little time for blogging.

This week, I ran into a corruption issue that gave me a chance to make a deeper dive into internals than I have in a while. It also gave me the opportunity to develop a script to dump the results of DBCC PAGE to a table.

This week, we discovered corruption in a client’s database we were migrating. Digging deeper into the results of DBCC CHECKDB, I found that six objects in the database were corrupted badly enough to require data loss. One of those objects contained 47 irreparable pages.

In an effort to provide the client as much information as possible about the corrupted data, I set out to identify the rows contained on the corrupt pages. Here are the scripts I used.

First, I needed to create tables to hold the results of DBCC CHECKDB and DBCC PAGE. For the PageResults table, I guessed at the column definitions. For DBCCResults, I made all of the numeric columns BIGINT to prevent conversion errors.

USE [Your_Database]
GO

/* Create table to hold the results of DBCC CHECKDB */

/****** Object:  Table [dbo].[DBCCResults]    Script Date: 9/30/2014 11:00:47 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF OBJECT_ID('Your_Database..DBCCResults') IS NOT NULL
BEGIN

	DROP TABLE DBCCResults;

END

IF OBJECT_ID('Your_Database..PageResults') IS NOT NULL
BEGIN

	DROP TABLE PageResults;

END

CREATE TABLE PageResults
(ParentObject VARCHAR(100)
,[Object] VARCHAR(1000)
,[Field] VARCHAR(100)
,[VALUE] VARCHAR(1000))

CREATE TABLE [dbo].[DBCCResults](
	[Error] [bigint] NULL,
	[Level] [bigint] NULL,
	[State] [bigint] NULL,
	[MessageText] [varchar](7000) NULL,
	[RepairLevel] [varchar](7000) NULL,
	[Status] [bigint] NULL,
	[DbId] [bigint] NULL,
	[DbFragID] [bigint] NULL,
	[ObjId] [bigint] NULL,
	[IndId] [bigint] NULL,
	[PartID] [bigint] NULL,
	[AllocID] [bigint] NULL,
	[RidDbid] [bigint] NULL,
	[RidPruid] [bigint] NULL,
	[File] [bigint] NULL,
	[Page] [bigint] NULL,
	[Slot] [bigint] NULL,
	[RefDbid] [bigint] NULL,
	[RefPruId] [bigint] NULL,
	[RefFile] [bigint] NULL,
	[RefPage] [bigint] NULL,
	[RefSlot] [bigint] NULL,
	[Allocation] [bigint] NULL
) ON [PRIMARY]

GO

After creating the tables, I needed to generate DBCC CHECKDB statements for each database. The script below uses sys.databases as the driver, and you can modify the results with an appropriate WHERE clause. The script is set to print the DBCC CHECKDB commands. Replacing the PRINT statement with EXEC sp_executesql will execute the statement after generation. Upon execution, the results of DBCC CHECKDB will be inserted into the DBCC CHECKDB table.

USE master;
GO

/* Drop temp table if it exists */

IF OBJECT_ID('tempdb..#databases') IS NOT NULL
BEGIN

	DROP TABLE #databases

END

/* Declare local variables and create temp tables */
DECLARE @sqlstr NVARCHAR(2000);
DECLARE @dbname SYSNAME;
DECLARE @loopcount TINYINT = 1;
DECLARE @looplimit TINYINT;

CREATE TABLE #databases
(dbname SYSNAME);

/* This will generate statements for all user databases 
   Change the WHERE clause to limit or expand the results */
INSERT INTO #databases
SELECT name FROM master.sys.databases
WHERE name > 4;

/* Get the loop limit */
SELECT @looplimit = @@ROWCOUNT;

/* Build the DBCC CHECKDB statement for each database 
   This code will print each statement
   Replace the PRINT statement with EXEC sp_executesql @sql str to execute the code */
WHILE @loopcount <= @looplimit
BEGIN

	SELECT TOP 1 @dbname = dbname FROM #databases;

	SELECT @sqlstr = 'INSERT INTO Your_Database..DBCCResults EXEC(''DBCC CHECKDB(' + @dbname + ') WITH TABLERESULTS, NO_INFOMSGS'')';

	PRINT @sqlstr;

	SELECT @loopcount += 1;

	DELETE FROM #databases WHERE dbname = @dbname;

END


Once, the DBCCResults table was populated, I needed to dump the contents of the irreparable pages. For corrupted pages, DBCC CHECKDB returns two possible RepairLevels — repair_rebuild indicates the page can be rebuilt to fix the corruption; repair_allow_data_loss means that the page is corrupted so badly it cannot be repaired. The only option is to delete the page and restore consistency to the object structure. This link from Paul Randal provides a much more detailed explanation.

USE Your_Database;

/* Drop temp tables if they exist */
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#DBCCPages') IS NOT NULL
BEGIN
	
	DROP TABLE #DBCCPages;

END

IF OBJECT_ID('tempdb..#PageResults') IS NOT NULL
BEGIN
	
	DROP TABLE #PageResults;

END

/* Create temp tables */
CREATE TABLE #PageResults
(ParentObject VARCHAR(100)
,[Object] VARCHAR(1000)
,[Field] VARCHAR(100)
,[VALUE] VARCHAR(1000))

/* Declare local variables */
DECLARE @loopcount INT = 1;
DECLARE @looplimit INT;
DECLARE @sqlstr NVARCHAR(4000);
DECLARE @pagenum BIGINT;

/* Select information about the corrupt data
   This example selects rows with a RepairLevel of repair_allow_data_loss for a single object */
SELECT DISTINCT O.name, T.RepairLevel, T.IndId, T.PartID, T.Page 
INTO #DBCCPages 
FROM DBCCResults T
INNER JOIN Your_Database.sys.objects O
ON t.ObjId = O.object_id
WHERE RepairLevel = 'repair_allow_data_loss'
AND O.name = 'Your_Table'
ORDER BY O.name, T.Page;

/* Set the loop limit */
SET @looplimit = @@ROWCOUNT;

/* Build a DBCC PAGE statement for each corrupt page and execute it
   Insert the results into the #PageResults temp table */
WHILE @loopcount <= @looplimit
BEGIN

	SELECT TOP 1 @pagenum = Page FROM #DBCCPages

	SET @sqlstr = 'DBCC PAGE (Your_Database,1,' + CAST(@pagenum AS NVARCHAR) + ',3) WITH TABLERESULTS'

	INSERT INTO PageResults
	EXEC sp_executesql @sqlstr;

	SET @loopcount += 1;

	DELETE FROM #DBCCPages WHERE Page = @pagenum;
	
END


Because I needed to provide the client with a list of rows affected by the corruption, I had to dump all pages identified as repair_allow_data_loss. The script below queries DBCC Results to return a list of PageIDs for the irreparable pages. It runs DBCC PAGE…WITH TABLERESULTS for each PageID and writes the results to the PageResults table. Here is a sample of the output:

DBCCPAGEWithTableResults

You can see that the results contain a set of rows for each slot, or table row, contained on the page. I needed to take each set of those rows and pivot them into a single row. The script below does that. You will need to add a MIN(CASE statement for each row you want to pivot.

USE Your_Database;
/* Select data from PageResults to return the key value for each row contained on a corrupt page */

SELECT ParentObject,
     MIN(CASE Field WHEN 'col1' THEN VALUE END) AS col1,
     MIN(CASE Field WHEN 'col2' THEN VALUE END) AS col2,
     MIN(CASE Field WHEN 'col3' THEN VALUE END) AS col3,
     MIN(CASE Field WHEN 'col4' THEN VALUE END) AS col4,
     MIN(CASE Field WHEN 'col5' THEN VALUE END) AS col5
INTO Your_Database..PageResults
   FROM #PageResults
   GROUP BY ParentObject
   ORDER BY col1, col2, col3, col4, col5; 

I hope this post provides more insight into the tools and methods available for analyzing corruption and determining its extent.