The below query will identify all the open / long running transactions and blocking SPID’s from the database to the virtual center server. Long running transactions and blocking SPID’s can prevent access to VC and even cause access to VC inventory extremely slow.
If you find yourself in a situation where the VC service is in started state and still you are unable to access virtual center then follow the below steps. You might receive error messages similar to “The server took too long to respond.. request timed out”. Check all open connection and kill which is not critical and has been running for a longer duration.
I am assuming you have access to your SQL database or touch base with your SQL DBA for assistance. This is how they would identify the culprit.
SELECT spid ,status ,loginame = SUBSTRING(loginame, 1, 12),hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(char(3), blocked) ,open_tran
,dbname = SUBSTRING(DB_NAME(dbid),1,10)
,cmd ,waittype ,waittime ,last_batch
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
In my example (attached screenshot), I did not have any blocking culprit to showcase. 🙂
SELECT blocked FROM master.dbo.sysprocesses where dbid=db_id(‘VCDB’)
To kill the identified blocking you would need to issue command
kill <SPID number>