Yesterday I wrote about the space issue on the drive which had tempdb on it. The old backup was subsequently deleted from the drive. But the size of tempdb remained the same.
Obviously there would be an open transaction running on tempdb. So the good old tricks were put into use.
Yes, there was an open transaction (spid 60) and it was executing the below query on tempdb.
EXEC sp_MSdependencies null, null, 528893
sp_MSdependencies is one of the undocumented stored procedures. This executed when we click on “View Dependencies” in SSMS. In this case this stored procedure had a parameter of 528893, that is the object id of some object. To find out which was that object, this query was executed against tempdb.
Result? NULL!. So the query which was not referring to an object in tempdb was running on tempdb? Not possible. Next step, one more DBA favourite command.
This showed that one spid 129 was blocking spid 60 (the one running on tempdb). SPID 129 was executing a query like this and it was being executed against a user database.
select * from items(nolock) where itemname = 52619
Now it was clear that spid 60 was executing sp_MSdependencies on “items” table. It was confirmed by executing this query.
The output of the above query was 528893 and this was one of the parameters of sp_MSdependencies. But the spid 129 was executing a query with NOLOCK hint. The hint name is confusing at times. Many would assume that the query in which nolock hint is used, will not obtain any locks and it will not lock any other processes. The definition of nolock hint on MSDN is
Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data
It clearly states that the exclusive locks from other processes will not hamper the execution of this script. However it does not mean that this process will not block other processes.
Hence, in my case a query with nolock hint blocked the other query. The question arises that how two queries get blocked while competing for a resource on a user database, can leave an open transaction on tempdb?
The answer lies in the content of sp_MSdependencies. The below query gave the script inside the stored procedure.
The output of this command justified the open transaction on tempdb. sp_MSdependencies while executing creates several temporary tables, populates data in them before giving out the actual output. Since the stored procedure execution was blocked by the nolock hint query, the transaction was never complete. Hence the temporary tables continued to be open on tempdb.
Ok, nice lengthy post. What was done next? Only two options ask the end user to stop the query or restart the services. Since it was midnight, the end user had gone to sleep with transaction open and laptop locked.
Finally the transaction was killed and they lived happily ever after 😉