Slow Database Offline

• GhandiDBA

  SSChasing Mays

  Points: 632

  More actions

  January 25, 2016 at 2:43 am

  #315313

  Hello,

  I've battling with a bit of an odd problem and I'm hoping someone can help. I have a cloning process running via PowerShell that periodically gets stuck for a large number of minutes taking a database offline prior to the cloning of the files underneath and I cannot find out why it's doing it.

  The basic process for the offline steps is as follows

• Create SQLPS Objects
• Kill all processes for that DB
• Get a list of any connections that exist on the database at that point
• Set restricted user
• Get a list of any connections that exist on the database at that point
• Set database offline

What's particularly odd is that the checks show that there are not any sessions on the database to explain it getting stuck and the behaviour is identical whether you use the SMO SetOffline() function or the TSQL SET OFFLINE WITH ROLLBACK IMMEDIATE. It also doesn't happen every time

PowerShell code snippet for the offline function (With lots of timespans to report where the delays are coming from)

function Set-DatabaseOffline

{

param

(

$instance,

$database

)

try

{

$stepStart = Get-Date

$machineName = (Get-HostFromAlias $instance)

$stepDuration = (New-TimeSpan -Start $stepStart -End (Get-Date))

Write-Host "Instance alias gathered in $($stepDuration)" -ForegroundColor DarkMagenta -BackgroundColor White

Write-Host "Creating SQLPS objects" -ForegroundColor Yellow

$stepStart = Get-Date

$serverObject = Get-Item "SQLSERVER:\SQL\$($machineName)\DEFAULT"

$dbObject = Get-Item "SQLSERVER:\SQL\$($machineName)\DEFAULT\Databases\$($database)"

$masterObject = Get-Item "SQLSERVER:\SQL\$($machineName)\DEFAULT\Databases\master"

$stepDuration = (New-TimeSpan -Start $stepStart -End (Get-Date))

Write-Host "SQLPS Objects created in $($stepDuration)" -ForegroundColor DarkMagenta -BackgroundColor White

Write-Host "Closing all open connections to $($database) on $($instance) and setting it offline" -ForegroundColor Yellow

$stepStart = Get-Date

$serverObject.KillAllProcesses($database)

$stepDuration = (New-TimeSpan -Start $stepStart -End (Get-Date))

Write-Host "All processes killed in $($stepDuration)" -ForegroundColor DarkMagenta -BackgroundColor White

# Validation - Check if we have any open connections to this DB still

[Int]$connectionCount = $serverObject.GetActiveDBConnectionCount($database)

Write-Host "There are now $($connectionCount) connections to $($database)" -ForegroundColor Yellow

if ($connectionCount -gt 0)

{

# Report the list of current connections for that database

$serverObject.EnumProcesses() | Where Database -ieq $database | Select Spid, Login, Host, Status, Program | FT -AutoSize

}

$stepStart = Get-Date

$masterObject.ExecuteNonQuery("IF (SELECT user_access_desc FROM sys.databases WHERE name = '$($database)') != 'RESTRICTED_USER' BEGIN ALTER DATABASE [$($database)] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE END;")

$stepDuration = (New-TimeSpan -Start $stepStart -End (Get-Date))

Write-Host "Database swapped to restricted access in $($stepDuration)" -ForegroundColor DarkMagenta -BackgroundColor White

# Validation - Check if we have any open connections to this DB still

[Int]$connectionCount = $serverObject.GetActiveDBConnectionCount($database)

Write-Host "There are now $($connectionCount) connections to $($database)" -ForegroundColor Yellow

if ($connectionCount -gt 0)

{

# Report the list of current connections for that database

$serverObject.EnumProcesses() | Where Database -ieq $database | Select Spid, Login, Host, Status, Program | FT -AutoSize

}

$stepStart = Get-Date

$dbObject.SetOffline()

$stepDuration = (New-TimeSpan -Start $stepStart -End (Get-Date))

Write-Host "Database taken offline in $($stepDuration)" -ForegroundColor DarkMagenta -BackgroundColor White

Write-Host "$($database) has successfully been taken offline" -ForegroundColor Yellow

}

catch

{

throw

}

}

Output from a delayed run

Creating SQLPS objects

SQLPS Objects created in 00:00:01.9461207

Closing all open connections to somedb on SOMESERVER and setting it offline

All processes killed in 00:00:00.2320159

There are now 0 connections to somedb

Database swapped to restricted access in 00:00:00.4600213

There are now 0 connections to somedb

Database taken offline in 00:07:59.3086630

somedb has successfully been taken offline

Database offline step completed in 00:08:02.4678561

Thanks very much in advance for any help as this is driving me mad!