How to identify VC database’s long running transaction and /or blocking SPID’s in SQL server using a query.

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

FROM master.dbo.sysprocesses

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. :)

The above query would look for blocking from the entire database, if you want to run the query against a specific database then use the below query. In this example the database name  is “VCDB”.

 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>

1780 Total Views 2 Views Today

Leave a Reply