Background process can block restore of database

We can use database snapshots for the development of new databases or fine tunning complex upgrade scripts. All active connections must be closed before the database can be restored from a database snapshot. Closing of active connections manually via Activity Monitor isn’t an option that can be easily automated so we can use this simple batch to close all active connections in the given database before restore:

USE [master]
GO

DECLARE @Sql VARCHAR(1000)
SET @Sql = ''    
SELECT  @Sql = @Sql + 'kill ' + CONVERT(NVARCHAR(3), [spid]) + '; '
FROM    [master].[dbo].[sysprocesses]
WHERE   DB_NAME([dbid]) = 'MY_DATABASE'  AND spid > 50
EXECUTE(@Sql)
GO

Most of the time it’s working perfectly but for some specific databases and only at some point in time we can see this exception to be fired:

The question is: Is there an active system SPID in our database? Let’s check it in the Activity Monitor or with the query below:

SELECT 
	[spid], [kpid], [cmd],  [blocked], [waittype], [waittime], [lastwaittype], [waitresource], [dbid], [uid], [cpu],
	[physical_io], [memusage], [login_time], [last_batch]
FROM    [master].[dbo].[sysprocesses] 
WHERE   DB_NAME([dbid]) = 'MY_DATABASE' 
GO

There is really one active system SPID running and it looks like to do some serious work based on CPU and physical_io columns. Where is this coming from?

Most probably our database has Change Tracking feature active on it and this system SPID is a background task to maintain the history cleanup. We can check for it:

SELECT * FROM sys.[change_tracking_databases]

If we see one row listed there for our database then most probably the system SPID belongs to the Change Tracking cleanup job which is activated on our database and is blocking restore from the snapshot. You can read more about this in the documentation. A similar situation we can expect for the Change Data Capture feature.

When Change Tracking doesn’t need to be active on our development database then the best option is to switch it off before snapshot creation:

DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL = @SQL + 'ALTER TABLE ' + [s].[name] + '.' + [t].[name] + ' Disable Change_tracking;' + CHAR(10)
FROM [sys].[change_tracking_tables] [ct]
	JOIN [sys].[tables] [t] ON [ct].[object_id]= [t].[object_id] 
	JOIN [sys].[schemas] [s] ON [t].[schema_id]= [s].[schema_id];

EXEC sp_executesql @SQL;
GO

ALTER DATABASE [MY_DATABASE] SET CHANGE_TRACKING = OFF
GO

If Change Tracking needs to be active we can use the undocumented stored procedure sp_flush_commit_table_on_demand. There is more info about it at sirsql.net.