Table Locking Informations????
-
Hello friends!!
Can any body please tell me... is there any way to find which table is locked currently....suppose my two or three jobs are running and at that same time will I check which table is currently locked...????
Sp_Who2..... gives me database..but i want perticularly Tables only........
Regards,
Papillon
-
In Query Analyzer, enter code to reveal query causing the blocking lock:
DBCC INPUTBUFFER (<spid>
other option is to use the Profiler, begin a trace, duplicate the activity that causes the blocking lock, identify the SPID of the blocking lock, and then stop the trace. Next, look up the SPID in the trace, viewing all of the activity that occurred up to the blocking lock occurring.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com -
Hi shashank,
The information about Locks, Blocking Bottleneck are are stored in few system table sysprocesses,syslocks and syslockinfo in master database
if u query syslockinfo u will see a field rsc_objid this field has the object id means table id or view id .If a lock or blocking is there then only u can see the object id else u will not.
if u want to know the object name then join it with sysobject table and u will get the table name.
u can join it by id field in sysobject table.
it will be better to create a view and filter it.
HTH
from
Killer
-
Hi Raj!!!
I got ur point thats nice
USE MASTER
select sysobjects.id from syslockinfo inner join sysobjects
on syslockinfo.rsc_objid = sysobjects.id
here i got sysobjects.id ...but that is different in different databases.....
so i took this ID and run command like
use test
select NAME, ID from sysobjects where id = "which i got from above query"
I got the correct result
Is there any way to combine both queries????
one more thing is,,,,, my database name is public-emp_t and when i am referencing this name i am getting error like
Line 1: Incorrect syntax near '-'.
How to over-come this error.....
Regards,
Papillon
-
1) To combine the queries, you need to use the db name to fully qualify the table names. You can assign aliases to each table to make typing easier. So, you might try something like this:
select
so.id,
t_so.*
from master.dbo.syslockinfo as sli -- assign 'sli' as the alias
inner join master.dbo.sysobjects as so -- assign 'so'
on sli.rsc_objid = so.id
inner join test.dbo.sysobjects as t_so
on so.id = t_so.id
2) Using a hyphen in a table name requires that you enclose the tablename in [brackets].
Hope that helps.
-Mike
-
Hi ,
If u want to use the query for diffrent database use have to give the database name in brackets.
If u want to run the query for remote server then create a linked server and again use brackets to refrence the server ipaddress or name.
HTH
from
Killer
-
Hi!!!
Thanks for all of your valuable time...i got that one
Regards,
Papillon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply