Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In my environment there is a database Sybase ASE 15, that I need to replace with a Microsoft SQL Server 2000-r2. Many users access this database via some applications, that sometimes "forget" to commit a transaction, then idle indefinitely keeping the table lock. This has a terrible effect: all other applications queue to obtain a lock on the tables affected, and are effectively stuck. In Sybase I use a query that tells me which user is causing the problem; I can then either kill the task, or even go to him/her and find and correct the problem in the application. This is the Sybase query:

select l.spid, SysLogin=s.name,SysObject=o.name, dbname
  from master..syslocks l, master..sysprocesses p, SIAM..sysobjects o, master..syslogins s
 where o.type='U'
   and p.spid=l.spid
   and l.id=o.id
   and p.suid=s.suid

The output produced looks like this:

81  john    authors     maindb
88  mary    authors     maindb
88  mary    books       maindb

Unfortunately, syslocks does not exist in Mssql, at least not in the version I am using. How do I convert the query to work on Mssql? Of course, if there is an alternative way of achieving the same result that would be great.

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

You might get it to work using only system procedure sp_lock. Although it's deprecated and it will be removed in a future version, it still works on SQL 2008 R2 and SQL 2012.

USE master;
GO
EXEC sp_lock;
GO
EXEC sp_lock 53; -- 53 is the spid for some specific user session;
GO

Another way is to use the DMV sys.dm_tran_locks.

Select *
from sys.dm_tran_locks dl
join sys.sysprocesses sp on dl.request_session_id = sp.spid

I think that a more clearer way is to use Adam Machanic's procedure WhoIsActive which has a specific parameter for showing locks for a session.

Use them all and choose whatever suits you best, but the WhoIsActive procedure provides more info than just locks.

share|improve this answer
    
The second one is my favorite –  carlo.borreo Feb 12 '13 at 17:42
    
@carlo.borreo Don't forget to try the WhoIsActive procedure, it will show you some nice info (including wait stats). –  Marian Feb 13 '13 at 7:55
add comment

You're looking for transactions that have been left open. Try "DBCC OPENTRAN('db_name') WITH TABLERESULTS, NO_INFOMSGS" to get the currently open transactions, including when they were started. That would be a good starting point. There are probably some fancier DMV's you could use - but DBCC OPENTRAN works across a whole bunch of different versions (SQL Server 2000+).

share|improve this answer
add comment

Take 2, the newer DMV based solution.

SELECT des.session_id, OBJECT_NAME(resource_associated_entity_id) [Entity], des.login_name, db_name(der.database_id) [database] FROM sys.dm_exec_requests der JOIN sys.dm_exec_sessions des ON des.session_id = der.session_id JOIN sys.dm_tran_locks dtl ON des.session_id = request_session_id WHERE command NOT IN ('BACKUP LOG', 'BACKUP DATABASE', 'DBCC') AND resource_type <> 'DATABASE' AND open_transaction_count > 0 AND start_time > dateadd(minute, -2, getdate())

Lists the data you want for open transactions that have current locks. There is a whole bunch more information you can get out of it, but this is close to what you get in SYBASE. I've excluded non-DML commands and database locks (of which you'll get one per connection).

share|improve this answer
2  
sigh please post ONE answer. Is there any reason your two answers couldn't be combined just like Marian combined his? –  Aaron Bertrand Feb 11 '13 at 14:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.