The analysis of the AppAssure Agent behavior reveals the following:
Every time the SQL metadata is collected (once a minute), AppAssure opens a few processes on the database engine, using the Master & Model databases and leaves them in a sleeping state.
Since these connections timeout after a while and are closed by the database engine (or are allowed to be reused), the number of concurrent processes is limited and, with the exception of some extreme situations their number does not impede on the database activity.
The issue of concern is the process attached to the model system database as it locks it and therefore the creation of new databases is blocked. Due to the fact that the metadata is collected every minute, the lock on the system model database is always present.
The work around is killing the process which is blocking the model system database.
For exemplification, a powershell script has been prepared. This powershell script takes as parameter the path to the SQL script to be executed and needs to be run on the SQL server with the issue. The account under which the powershell script is running needs to be a sysadmin for the local database engine. Please note that better solutions may be found although the logic stays the same.
Basically the scripts does the following::
a. Identifies the SPID of the process blocking the system model database – if any.
b. Kills the SPID locking the model database
c. Runs the SQL script you need to run (the path needs to be provided as a parameter or by modifying the powershell script)
The script is fully documented. Help information can be generated by running
get-help .\unlockmodeldb.ps1 -full
The script code is shown below between horizontal lines and attached to the KB.
________________________________________________________________________________
<#
.Synopsis
Work around 'blocked database creation' due to the AppAssure agent service
.Description
This script identifies the SPID blocking the SQL system model database, kills it and runs the desired SQL script, thus allowing creating new SQL databases without bouncing the AppAssureAgent service.
.Parameter sqlscriptpath
Default is c:\temp\script.sql; Points to the location of the script to be executed.
.Example
UnlockModelDb.ps1 -sqlscriptpath c:\scripts\myscript.sql
#>
#sql script path
param ($sqlscriptpath="c:\temp\script.sql")
#prepare for execution
$location = get-location
if(!(get-module -Name sqlps)){write-host "importing sql powershellmodule"; import-module sqlps}
$kill=$true
#get the spid(s) to kill
$sqlsleepers = Invoke-Sqlcmd -Query "EXEC sp_who"| where {$_.dbname -like "model" } | sort-object -property spid
if($sqlsleepers){$sqlsleepers | ft -AutoSize}
else {Write-Host "No processes returned... Moving forward"; $kill=$false }
if($kill){
if($sqlseepers){Write-Host "Found:";$sqlsleepers | FT spid,status,loginame,dbname,hostname}else{Write-Host "No SPIDs locking model.db found"}
#kill spid(s)
foreach ($sqlproc in $sqlsleepers){
Write-Host "Attempting to terminate:";
$sqlproc | FT spid,loginame,dbname -AutoSize;
invoke-sqlcmd -query "KILL $($sqlproc.spid)"};
#list results
Write-Host "Getting the list of remaining processes..."
$list = Invoke-Sqlcmd -Query "EXEC sp_who"| where {$_.dbname -like "model" } | sort-object -property spid
if($list){$list | ft -AutoSize}
else {Write-Host "No processes remaining"}
Set-Location $location
}
#run sql script
if(test-path -Path $sqlscriptpath){
$scriptresult = Invoke-Sqlcmd -InputFile $sqlscriptpath
}else {Write-Host "Sql Script not found"}
$scriptresult
____________________________________________________________________________________