check what table are lock by application

Dear All,

Sometimes the application cannot write record into SQL Server, but the source code of this application cannot be seen. Therefore, I wanna verify the MSSQL status. I have to check what table are locked by application. I wanna know the following information.

locked_table, locked_by_application, locked_status

Can I write some t-sql to get the information? I tried using Profiler, but it generates some useless information. Could you give me some suggestions?

Alex

Lock granularity is much wider than only a tables. That's for the first. Second is that SQL Server doesn't require any information about application and situation, when application doesn't provide it, is common. And, for the third, I would recommend you to solve your locking and blocking problems rather than prevent connections to get blocked.|||

In my opinion Profiler would be the best choice for You. The only thing what You have to do is to establish the appropriate configuration in outgoing parameters.

|||

Take a look at: How to monitor SQL Server 2000 blocking http://support.microsoft.com/default.aspx?scid=kb;en-us;q271509

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

thank SQL_Menace

I will try it

Alex

|||Hi Alex
here is a solution that microsoft does not recommend because it depends on the system objects
create table #Locks (SPID smallint , DBID smallint ,objID int ,indID smallint , Type nchar(4),resource nchar(16), mode nvarchar(8), status varchar(8))
insert into #locks execute sp_lock
select distinct object_name (objID) , Hostname , program_name,nt_userName , loginame , object_name (objID), mode
from master..sysprocesses s inner join #locks l on s.spid = l.spid
drop table #locks

find the values and the meanings of mode in the MSDN
HTH