Lesson Learned #53: Why do you need to use dm_operation_status dynamic management view?


Hello,

This week I worked in a new issue that remembered me the importance to use the dynamic management view called dm_operation_status in every operation that I'm doing with any database either Azure SQL Database or Azure SQL Managed Instance.

As most probably you know every database creation, modification, deletion, copy , etc.. is asyncronous process, that means, that once you submitted any command, for example, ALTER DATABASE or DROP DATABASE and you received the message that the operation has been completed with success, this operation may be still running in the background, because, looks like, that some of these operations are sending to our Management Operation Web Service using RESTAPI instead of a direct operation against the SQL Server Instance.

For this reason, I would like to share with you two script in PowerShell and TSQL that is my suggestion if you have a CRUD scheduled process for your database, in order to be sure that every  steps has been completed before continue working with the next step.

Disclaimer.- Please, use and review very carefully the following scripts and use by your own risk. The following operations are an example how to manage the operations using dm_operation_status.

 

TSQL for Azure SQL Managed Instance .- Pay attention in the operation column value of dm_operation_status text if you need to review the status of the operation for Azure SQL Database because it is different between them.

DECLARE @nTimes as integer = 0
DECLARE @State AS integer = 0
DECLARE @nJmp as integer =0
DECLARE @NDeleted as integer = 0
 
while(@nTimes<=2)
BEGIN
 
SET @nTimes=@nTimes+1
PRINT 'Time ' + convert(varchar(20),@nTimes)
 
set @NDeleted =0
set @State = -1
 
PRINT 'Deleted Database Started'
 
IF EXISTS(SELECT name from sys.databases WHERE name = 'DROP_DATABASE_DB')
BEGIN
SET @NDeleted =1
DROP DATABASE DROP_DATABASE_DB
PRINT 'Deleted Database Executed'
WAITFOR DELAY '00:00:05';
END
 
PRINT 'Deleted Checking Database Initiated'
 
IF @NDeleted=1
BEGIN
set @nJmp = 0
WHILE(@nJmp=0)
BEGIN
PRINT 'Checking deleting process state is active...'
SET @State = (SELECT TOP 1 State FROM sys.dm_operation_status WHERE major_resource_id = 'DROP_DATABASE_DB' AND operation = 'DropManagedDatabase' ORDER BY start_time DESC )
IF @State = 2
BEGIN
set @nJmp=1
END
PRINT 'Deleted Database Started - State:' + CONVERT(varchar(20),@State)
WAITFOR DELAY '00:00:05';
END
 
WHILE(EXISTS(SELECT TOP 1 name from sys.databases WHERE name = 'DROP_DATABASE_DB'))
BEGIN
PRINT 'Checking deleting process state is active...'
WAITFOR DELAY '00:00:05';
END
END
 
PRINT 'Deleted Database Finished'
 
PRINT 'Creating the database'
 
CREATE DATABASE DROP_DATABASE_DB
WAITFOR DELAY '00:00:05';
 
set @nJmp = 0
set @State = 0
 
WHILE(@nJmp=0)
BEGIN
PRINT 'Checking creating process state is active 1..'
SET @State = (SELECT TOP 1 State FROM sys.dm_operation_status WHERE major_resource_id = 'DROP_DATABASE_DB' AND operation = 'CreateManagedDatabase' ORDER BY start_time DESC )
IF @State = 2
BEGIN
set @nJmp=1
END
PRINT 'Created Database Phase 1 - State:' + CONVERT(varchar(20),@State)
WAITFOR DELAY '00:00:05';
END
 
set @nJmp = 0
set @State = 0
 
WHILE(@nJmp=0)
BEGIN
PRINT 'Checking creating process state is active 2 ..'
SET @State = (SELECT TOP 1 state from sys.databases WHERE name = 'DROP_DATABASE_DB' )
IF @State = 0
BEGIN
set @nJmp=1
END
PRINT 'Created Database Phase 2 - State:' + CONVERT(varchar(20),@State)
WAITFOR DELAY '00:00:05';
END
 
END

PowerShell for Azure SQL Database in a CREATE DATABASE .. COPY OF execution.

param(
[string]$ResourceGroupName = 'xxxxxx',
[string]$CopyToDbServerName = 'TargetServer',
[string]$CopyToDbName = 'TargetDatabase',
[string]$SourceDbServerName = 'SourceServer',
[string]$SourceDbName = 'SourceDatabase'
)

##$secpasswd = ConvertTo-SecureString -AsPlainText $AzureAccountPassword -Force
##$creds = New-Object System.Management.Automation.PsCredential($AzureAccountUsername, $secpasswd)
##Login-AzureRmAccount ##-ServicePrincipal -Credential $creds -TenantId $AzureAccountTenantId -SubscriptionId $AzureSubscriptionId

#Source Database - Connectivity details User DB and master DB
$server= "tcp:xxxxx.database.windows.net,1433"
$user="xxxxxxxxxxxx"
$password="xxxxxx"
$Db="master"
$Jump = 0

#Function to connect to the database
Function GiveMeConnectionSource{
for ($i=0; $i -lt 10; $i++)
{
try
{
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = "Server="+$server+";Database="+$Db+";User ID="+$user+";Password="+$password+";Connection Timeout=60"
$SQLConnection.Open()
break;
}
catch
{
Start-Sleep -s 5
}
}
Write-output $SQLConnection
}

$tempDatabaseName = "$($CopyToDbName)temp"

$SQLConnectionSource = GiveMeConnectionSource
$Command = "SELECT name from sys.databases WHERE name = '" + $tempDatabaseName + "'"
$SQLCommandExiste = New-Object System.Data.SqlClient.SqlCommand($Command, $SQLConnectionSource)
$Reader = $SQLCommandExiste.ExecuteReader();
Write-Host "Checking if the database exists..."
if($Reader.Read() -eq $True)
{
Write-Host "Deleting the database..."
##Remove-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $CopyToDbServerName -DatabaseName $tempDatabaseName
$Reader.Close()
$SQLCommand = "DROP DATABASE [" + $tempDatabaseName + "]"
$SQLCommandDrop = New-Object System.Data.SqlClient.SqlCommand($SQLCommand, $SQLConnectionSource)
$Executed = $SQLCommandDrop.ExecuteNonQuery();
Start-Sleep -s 5
}
$Reader.Close()
$SQLConnectionSource.Close()

Write-Host "Copying prod ($SourceDbName on server @$SourceDbServerName) to $tempDatabaseName on server $CopyToDbServerName"

$DbObject = New-AzureRmSqlDatabaseCopy `
-ResourceGroupName $ResourceGroupName `
-ServerName $SourceDbServerName -DatabaseName $SourceDbName `
-CopyResourceGroupName $ResourceGroupName `
-CopyServerName $CopyToDbServerName -CopyDatabaseName $tempDatabaseName

$Jump = 0
while($Jump -eq 0)
{
$SQLConnectionSource = GiveMeConnectionSource
$Command = "SELECT name FROM sys.databases WHERE name = '" + $tempDatabaseName + "' AND state=0"
$SQLCommandExiste = New-Object System.Data.SqlClient.SqlCommand($Command, $SQLConnectionSource)
$Reader = $SQLCommandExiste.ExecuteReader();
Write-Host "Checking copying process state is active..."
if($Reader.Read() -eq $True)
{
$Jump=1
}
$Reader.Close()
$SQLConnectionSource.Close()
Start-Sleep -s 5
}

$Jump = 0
while($Jump -eq 0)
{
$SQLConnectionSource = GiveMeConnectionSource
$Command = "SELECT major_resource_id FROM sys.dm_operation_status WHERE major_resource_id = '" + $tempDatabaseName + "' AND operation = 'TERMINATE CONTINUOUS DATABASE COPY' AND state = 2"
$SQLCommandExiste = New-Object System.Data.SqlClient.SqlCommand($Command, $SQLConnectionSource)
$Reader = $SQLCommandExiste.ExecuteReader();
Write-Host "Checking copying process..."
if($Reader.Read() -eq $True)
{
$Jump=1
}
$Reader.Close()
$SQLConnectionSource.Close()
Start-Sleep -s 5
}

$Command = "SELECT d.Name FROM sys.dm_database_copies dc JOIN sys.databases d ON dc.database_id = d.database_id WHERE d.Name = '" + $tempDatabaseName + "'"

$Jump=0
while($Jump -eq 0)
{
$SQLConnectionSource = GiveMeConnectionSource
$SQLCommandExiste = New-Object System.Data.SqlClient.SqlCommand($Command, $SQLConnectionSource)
$Reader = $SQLCommandExiste.ExecuteReader();
Write-Host "Checking that we don't have more copies..."
if($Reader.Read() -eq $false)
{
$Jump=1
}
$Reader.Close()
$SQLConnectionSource.Close()
Start-Sleep -s 5
}
$SQLConnectionSource = GiveMeConnectionSource
$Command = "SELECT name from sys.databases WHERE name = '" + $CopyToDbName + "'"
$SQLCommandExiste = New-Object System.Data.SqlClient.SqlCommand($Command, $SQLConnectionSource)
$Reader = $SQLCommandExiste.ExecuteReader();
Write-Host "Checking if the database exists..."
if($Reader.Read() -eq $True)
{
Write-Host "Deleting the database ..."
Remove-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $CopyToDbServerName -DatabaseName $CopyToDbName
$Reader.Close()
$SQLCommand = "DROP DATABASE [" + $CopyToDbName + "]"
$SQLCommandDrop = New-Object System.Data.SqlClient.SqlCommand($SQLCommand, $SQLConnectionSource)
$Executed = $SQLCommandDrop.ExecuteNonQuery();
Start-Sleep -s 5
}
$Reader.Close()
$SQLConnectionSource.Close()
Write-Host "Change the name..."
Set-AzureSqlDatabase -ServerName $CopyToDbServerName -DatabaseName $tempDatabaseName -NewDatabaseName $CopyToDbName

Enjoy!

Comments (0)

Skip to main content