Moving that DataStore, is there still someone attached?

While migrating a Citrix XenApp datastore isn't difficult (see How to move or migrate data store) can can rather quickly be scripted across all your farm servers, one question always remains... did I miss one?

If you try to take your original DB and perform the SQL Management Studio action "Take Offline" and all you get is a progress wheel that, well, never progresses, you may have one or more connections still active.

So how do you check? If you open a new query in SQL Management Studio prior to trying to take the DB offline you type and execute the command "sp_who" - the results of this will show all connections to the SQL Server including the source, user, and database. If your server is dedicated to your SQL infrastructure you can quickly parse this list to see what servers may have been missed.

Alternatives if you have a shared environment could be the query below that will tell you the number of connections active to each DB:

SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName, nt_domain as NT_Domain, nt_username as NT_UserName, hostname as HostName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, hostname, loginame,nt_domain,nt_username order by NumberOfConnections DESC

Finally, you could combine the two options and build a temporary DB to see only the connections open to your data store. 

There are a number of samples on the web for this method, one such is listed below. Just change the DB name 'master' to the DB name of your Citrix XenApp DataStore. 

DECLARE @temp TABLE(spid int , ecid int, status varchar(50),
                     loginname varchar(50),   
                     hostname varchar(50),
blk varchar(50), dbname varchar(50), cmd varchar(50), request_id int) 
INSERT INTO @temp  
EXEC sp_who
SELECT * FROM @temp WHERE dbname = 'master'