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.