July 16, 2015 at 2:51 am
Hi ,
I am using following sql to extract locking information in database. It only work on current selected database, how can I tune to work on all databases and not only currently selected?
SELECT DISTINCT
ES.login_name AS LoginName,
L.request_session_id AS BlockedBy_SPID,
DATEDIFF(second,At.Transaction_begin_time, GETDATE()) AS Duration_Sec,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
ST.text AS BlockingSql
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
ORDER BY L.request_session_id
July 16, 2015 at 2:52 am
Using something like sp_MS_foreachDB or a custom written cursor to loop over all databases. sys.partitions and sys.objects are specific to the current DB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2015 at 3:03 am
GilaMonster (7/16/2015)
Using something like sp_MS_foreachDB or a custom written cursor to loop over all databases. sys.partitions and sys.objects are specific to the current DB.
Thank you Shaw. But sp_MS_foreachDB will return multiple result sets as per database. I want one result set.
I have tried following sql as well. It gives locking information in all dbs, but if lock is by adhoc sql, it don't populate dbname and tableName. If lock is by stored proc, it return dbname but not tableName.
I need to have dbName and TableName as well.
SELECT
s.login_name,
w.session_id,
w.wait_duration_ms,
w.blocking_session_id,
r.command,
t.text AS SqlText,
DB_NAME(t.dbid) AS DatabaseName,
OBJECT_NAME(t.objectid) AS TableName,
s.program_name,
w.wait_type
FROM sys.dm_os_waiting_tasks w
INNER JOIN sys.dm_exec_sessions s
ON w.session_id = s.session_id
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1
July 16, 2015 at 3:10 am
Using something like sp_MS_foreachDB or a custom written cursor to loop over all databases, inserting into a temp table. Then select from a temp table at the end.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply