#Requires -Version 2

<#
.SYNOPSIS
    This script intentionally causes a deadlock in SQL Server.

.DESCRIPTION
    This script induces a deadlock using the scenario described by the 
    specified XML document.

.PARAMETER ConfigurationFilePath
    The path to the XML configuration file that describes the scenario for 
    deliberately causing a deadlock.

.PARAMETER BreakOnNon1205Error
    A switch that indicates that the script should break into interactive 
    debugging if an exception other than a 1205 error is thrown by one of 
    the T-SQL batches.

.PARAMETER DeadlockStatusMessageColor
    The color that should be used to display the "Deadlock induced" status 
    message.

.EXAMPLE

    PS C:\PS> & '.\Invoke-Deadlock.ps1' '.\DeadlockScenario01.xml';

.INPUTS
    System.String
        The path to the configuration file can be piped to the script.

.OUTPUTS
    System.Management.Automation.PSObject
        Details of the deadlock that was generated.

.NOTES
    Script name: Invoke-Deadlock.ps1
    Author:      Trevor Barkhouse
    Version:     3.0009.0020.0000 (2012-05-10)

.LINK
    http://sqlserversleuth.com/technical-presentations/deadlocks/
#>
[CmdletBinding()]
Param(
    [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
    [String]$ConfigurationFilePath,
    [Switch]$BreakOnNon1205Error,
    [String]$DeadlockStatusMessageColor = 'Red'
);

[Object]$Batch = $Null;
[Object[]]$Batches = @();
[String]$CommandText = '';
[XML]$ConfigurationSettings = $Null;
[Int]$ConnectionCount = -1;
[Int]$ConnectionCounter = -1;
[Int]$ConnectionID = -1;
[System.Data.SqlClient.SqlConnection[]]$Connections = @();
[String]$ConnectionString = '';
[DateTime]$ConnectionTime = [DateTime]::MinValue;
[String]$DatabaseName = '';
[Int]$Delay_milliseconds = -1;
[String]$ErrorMessage = '';
[Bool]$IsAsynchronous = $False;
[Object[]]$Processes = @();
[String]$ServerName = '';
[Int]$SessionID = -1;
[System.Data.SqlClient.SqlCommand]$SessionIDCommand = $Null;
[String]$StatusMessage = '';
[String[]]$StatusMessageParts = @();

If (Test-Path -LiteralPath $ConfigurationFilePath -PathType 'Leaf') {
    $ConfigurationSettings = [XML](Get-Content -LiteralPath $ConfigurationFilePath);

    If ($ConfigurationSettings.deadlockScenario) {
        $ServerName = $ConfigurationSettings.deadlockScenario.serverName.Trim();
        $DatabaseName = $ConfigurationSettings.deadlockScenario.databaseName.Trim();
        $ConnectionCount = [Int]($ConfigurationSettings.deadlockScenario.connectionCount.Trim());
        $Batches = @($ConfigurationSettings.deadlockScenario.batches.batch);

        If (
            ($ServerName.Length -gt 0) -and
            ($DatabaseName.Length -gt 0) -and
            ($ConnectionCount -gt 0) -and
            ($Batches.Length -gt 0)
        ) {
            #   Both connections will use the same connection string.  Note 
            #   the section on "Asynchronous Processing."
            $ConnectionString = "Server=$($ServerName);Database=$($DatabaseName);Trusted_Connection=True;Asynchronous Processing=True;Application Name=PowerShell_Invoke-Deadlock";

            #   Instantiate and open the desired number of connections to the 
            #   SQL Server instance.
            For ($ConnectionCounter = 0; $ConnectionCounter -lt $ConnectionCount; $ConnectionCounter++) {
                $Connections += $(New-Object -TypeName 'System.Data.SqlClient.SqlConnection' -ArgumentList $ConnectionString);
                $Connections[$ConnectionCounter].Open();
                $ConnectionTime = [DateTime]::Now;
                $SessionIDCommand = $Connections[$ConnectionCounter].CreateCommand();
                $SessionIDCommand.CommandText = 'SELECT @@SPID;';
                $SessionID = $SessionIDCommand.ExecuteScalar();
                $SessionIDCommand.Dispose();
                $Processes += (New-Object -TypeName 'System.Management.Automation.PSObject' | Select-Object -Property @{Name = 'Name'; Expression ={"$([Char]($ConnectionCounter + 65))"}}, @{Name = 'SessionID'; Expression ={$SessionID}}, @{Name = 'ConnectionTime'; Expression = {$ConnectionTime}});
                $StatusMessageParts += @("The Session ID for Process `"$([Char]($ConnectionCounter + 65))`" is $($SessionID)");
            }

            #   Output the session IDs of each of the connections.
            $StatusMessage = [String]::Join(
                "; ",
                $StatusMessageParts
            );
            Write-Host $StatusMessage -ForegroundColor 'Cyan';

            #   Iterate through the batches and execute each.
            ForEach ($Batch In $Batches) {
                #   Extract the batch settings.
                $ConnectionID = [Int]($Batch.connectionID);
                $CommandText = $Batch.commandText.get_InnerText().Trim();
                $IsAsynchronous = [Bool]::Parse($Batch.isAsynchronous);
                $Delay_milliseconds = [Int]($Batch.delay.get_InnerText());

                Write-Debug "`$ConnectionID = $($ConnectionID)";
                Write-Debug "`$CommandText = `"$($CommandText)`"";
                Write-Debug "`$IsAsynchronous = $($IsAsynchronous)";
                Write-Debug "`$Delay_milliseconds = $($Delay_milliseconds)";

                #   Execute the given batch.
                $Command = $Connections[$ConnectionID].CreateCommand();
                $Command.CommandText = $CommandText;
                Try {
                    If ($IsAsynchronous) {
                        [Void]$Command.BeginExecuteNonQuery();
                    }
                    Else {
                        [Void]$Command.ExecuteNonQuery();
                    }
                }
                Catch {
                    If ($_.ToString() -match '\bdeadlock victim\b') {
                        Write-Host 'Deadlock induced' -ForeGroundColor 'Red';
                    }
                    Else {
                        Write-Debug "$($_.ToString())";
                        If ($BreakOnNon1205Error) {
                            Set-PSDebug -Step;
                        }
                    }
                }
                Finally {
                    $Command.Dispose();
                }

                Start-Sleep -Milliseconds $Delay_milliseconds;
            }

            #   Close and free the resources used by each connection.
            For ($ConnectionCounter = 0; $ConnectionCounter -lt $ConnectionCount; $ConnectionCounter++) {
                $Connections[$ConnectionCounter].Close();
                $Connections[$ConnectionCounter].Dispose();
                ($Processes[$ConnectionCounter]) | Add-Member -MemberType 'NoteProperty' -Name 'DisconnectionTime' -Value $([DateTime]::Now);
            }

            #   Assume that a deadlock was successfully produced.
            Write-Host 'Deadlock induced' -ForeGroundColor $DeadlockStatusMessageColor;

            $Processes | Write-Output;
        }
        Else {
            $ErrorMessage = "The specified configuration file, `"$($ConfigurationFilePath)`", is invalid.";
            Throw (New-Object -TypeName 'System.InvalidOperationException' -ArgumentList $ErrorMessage);
        }
    }
    Else {
        $ErrorMessage = "The specified configuration file, `"$($ConfigurationFilePath)`", is invalid.";
        Throw (New-Object -TypeName 'System.InvalidOperationException' -ArgumentList $ErrorMessage);
    }
}
Else {
    $ErrorMessage = "The `"$($ConfigurationFilePath)`" file does not exist or is inaccessible.";
    Throw (New-Object -TypeName 'System.IO.FileNotFoundException' -ArgumentList $ErrorMessage);
}
