March 29, 2011 at 12:20 pm
Is there a way or a script which i can use to monitor the following:
i)i need to find how long is a particular table being locked over period of 4 hours?
ii) need to know how many spids are locking this table?
Can someone please suggest me !!!
March 29, 2011 at 1:16 pm
March 29, 2011 at 1:51 pm
You'll have to setup some kind of polling mechanism if you want to know lock information over a time period.
Alternate query, using the new SQL 2005 objects
/*
sys.dm_tran_locks
http://msdn.microsoft.com/en-ca/library/ms190345%28v=SQL.90%29.aspx
*/
SELECT
spid = Lock.request_session_id,
HostName = Session.host_name,
ProgramName = Session.program_name,
Cmd = Request.Command,
LoginName = Session.login_name,
dbid = Lock.resource_database_id,
DatabaseName = DB_NAME(Lock.resource_database_id),
Type = Lock.resource_type,
LockType = Lock.request_type,
Mode = Lock.request_mode,
Status = request_status,
UserTable.Name
FROM
sys.dm_tran_locks AS Lock
LEFT JOIN
sys.dm_exec_sessions AS Session ON
Session.session_id = Lock.request_session_id
LEFT JOIN
sys.dm_exec_requests AS Request ON
Request.session_id = Session.session_id
LEFT JOIN
sys.tables AS UserTable ON
UserTable.object_id = Lock.resource_associated_entity_id
AND
type = 'U'
WHERE
Lock.resource_type = 'OBJECT'
--AND
--UserTable.Name = 'YourTableName'
March 29, 2011 at 1:51 pm
Thanks. I tried this out , two things:
i) Sometimes i receive this error "Msg 8152, Level 16, State 2, Procedure sp_lock, Line 42
String or binary data would be truncated.
"
ii) how can i restrict this to a particular db like "MyDB" and table like "MyTable"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply