SQL 2008 table locked - can't work out why

Question

I have two databases on one SQL 2008 server. Database 1 seems to be causing a lock on a table on database 2. There no queries are running on database 1 that should affect database 2.

Is this normal behaviour?

When I view the running queries with this command

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time/1000 [seconds]
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

it tells me as much, and says that the command on database 2 is suspended.

I'm at a bit of a loss. What sort of things should I look at to work out why the table in database 2 is locked?

Answer 1

Queries running are irrelevant - the lock can be from a query that DID run and the connection / transaction is still valid (i.e. open transaction, not commited / rolled back), in which case the lock stays in place.

You basically have to identiy:

• The connection that locks the table.
• THe command chain run there within the connection.

Locks originate from operations the db does - so unless you got a low level critical error (VERY unlikely with an error like that) something has caused the lock to be generated.