August 6, 2014 at 7:28 am
How do I find what is keeing SSMS Object explorer from giving a list of tables or stored proc's? Even when you filter on what you are looking for? This will clear up in time, so I am guessing that some process is placing a lock .. but when I run the blocking query, below, I get no result.
SELECT DTL.[resource_type] AS [resource type]
, CASE WHEN DTL.[resource_type] IN ('DATABASE','FILE','METADATA')
THEN DTL.[resource_type]
WHEN DTL.[resource_type] = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.[resource_type] IN ('KEY','PAGE','RID')
THEN (SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.[hobt_id] = DTL.[resource_associated_entity_id]
)
ELSE 'Unidentified'
END AS [Parent Object]
, DTL.[request_mode] AS [Lock Type]
, DTL.[request_status] AS [Request Status]
, DOWT.[wait_duration_ms] AS [wait duration ms]
, DOWT.[wait_type] AS [wait type]
, DOWT.[session_id] AS [blocked session id]
, DES_blocked.[login_name] AS [blocked_user]
, SUBSTRING(dest_blocked.text, der.statement_start_offset / 2, (CASE WHEN der.statement_end_offset = -1 THEN DATALENGTH(dest_blocked.text) ELSE der.statement_end_offset END - der.statement_start_offset) / 2) AS [blocked_command]
, DOWT.[blocking_session_id] AS [blocking session id]
, DES_blocking.[login_name] AS [blocking user]
, DEST_blocking.[text] AS [blocking command]
, DOWT.resource_description AS [blocking resource detail]
FROM sys.dm_tran_locks DTL
INNER JOIN sys.dm_os_waiting_tasks DOWT
ON DTL.lock_owner_address = DOWT.resource_address
INNER JOIN sys.[dm_exec_requests] DER
ON DOWT.[session_id] = DER.[session_id]
INNER JOIN sys.dm_exec_sessions DES_blocked
ON DOWT.[session_id] = DES_Blocked.[session_id]
INNER JOIN sys.dm_exec_sessions DES_blocking
ON DOWT.[blocking_session_id] = DES_Blocking.[session_id]
INNER JOIN sys.dm_exec_connections DEC
ON DTL.[request_session_id] = DEC.[most_recent_session_id]
CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST_Blocking
CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_Blocked
WHERE DTL.[resource_database_id] = DB_ID()
August 11, 2014 at 11:05 am
Looks like a "create table" inside an uncommitted transaction would do it. You might shop around for a better blocking script.
edit: reran your script, I'm a bit undecided on this one now, seems like I'm getting varying results here on my test installation, on one occasion the script even blocked.
another edit:
This is pretty cool, I did a create table inside a transaction (without committing), then ran the following in two steps, I ran the first query, and took the value from the column "blocking_session_id" (which in this case is 56) and poked it into the second query as the criteria for which spid's last query to view.
Took these from http://www.mssqltips.com/sqlservertip/2732/different-techniques-to-identify-blocking-in-sql-server/
and
http://blog.sqlauthority.com/2009/07/19/sql-server-get-last-running-query-based-on-spid/
USE [master]
GO
SELECT session_id
,blocking_session_id
,wait_time
,wait_type
,last_wait_type
,wait_resource
,transaction_isolation_level
,lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
-- above query returned '56' as the 'blocking session id'.
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 56
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
August 14, 2014 at 7:10 am
patrickmcginnis59 10839 (8/11/2014)
Looks like a "create table" inside an uncommitted transaction would do it. You might shop around for a better blocking script.edit: reran your script, I'm a bit undecided on this one now, seems like I'm getting varying results here on my test installation, on one occasion the script even blocked.
another edit:
This is pretty cool, I did a create table inside a transaction (without committing), then ran the following in two steps, I ran the first query, and took the value from the column "blocking_session_id" (which in this case is 56) and poked it into the second query as the criteria for which spid's last query to view.
Took these from http://www.mssqltips.com/sqlservertip/2732/different-techniques-to-identify-blocking-in-sql-server/
and
http://blog.sqlauthority.com/2009/07/19/sql-server-get-last-running-query-based-on-spid/
USE [master]
GO
SELECT session_id
,blocking_session_id
,wait_time
,wait_type
,last_wait_type
,wait_resource
,transaction_isolation_level
,lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
-- above query returned '56' as the 'blocking session id'.
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 56
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
This part of the SQL errors out..
WHERE blocking_session_id <> 0
Msg 4145, Level 15, State 1, Line 10
An expression of non-boolean type specified in a context where a condition is expected, near ';'.
August 14, 2014 at 7:45 am
dwilliscp (8/14/2014)
patrickmcginnis59 10839 (8/11/2014)
Looks like a "create table" inside an uncommitted transaction would do it. You might shop around for a better blocking script.edit: reran your script, I'm a bit undecided on this one now, seems like I'm getting varying results here on my test installation, on one occasion the script even blocked.
another edit:
This is pretty cool, I did a create table inside a transaction (without committing), then ran the following in two steps, I ran the first query, and took the value from the column "blocking_session_id" (which in this case is 56) and poked it into the second query as the criteria for which spid's last query to view.
Took these from http://www.mssqltips.com/sqlservertip/2732/different-techniques-to-identify-blocking-in-sql-server/
and
http://blog.sqlauthority.com/2009/07/19/sql-server-get-last-running-query-based-on-spid/
USE [master]
GO
SELECT session_id
,blocking_session_id
,wait_time
,wait_type
,last_wait_type
,wait_resource
,transaction_isolation_level
,lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
-- above query returned '56' as the 'blocking session id'.
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 56
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
This part of the SQL errors out..
WHERE blocking_session_id <> 0
Msg 4145, Level 15, State 1, Line 10
An expression of non-boolean type specified in a context where a condition is expected, near ';'.
Sorry, I wasn't able to duplicate the error even when copying from the posted text back into my ssms query window. If you look at your error message in detail, note the part that says "near ';' ", and couple that with the fact that there isn't a semicolon in the posted text means something else might be in your query window (unless you put it in there to terminate the line, perfectly valid).
I did this on 2008r2.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply