LEFT JOIN sys.dm_exec_requests blocker_req on blocker_req.session_id = osw.blocking_session_id INNER JOIN dbo.sysprocesses blocker_proc on osw.blocking_session_id = blocker_proc.spid LEFT JOIN sys.dm_tran_session_transactions blocked_tran on blocked_ssion_id =req.session_id INNER JOIN sys.dm_os_waiting_tasks osw on osw.session_id = req.session_id INNER JOIN sys.dm_tran_locks dtl on dtl.request_session_id = req.session_id INNER JOIN sys.dm_exec_sessions ses on ses.session_id = req.session_idĬROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext , osw.resource_description AS taskResourceDescription , dtl.resource_subtype AS lockResourceSubType , blocker_ansaction_id blocker_transaction_id , blocker_proc.open_tran blocker_open_tran , blocker_proc.last_batch blocker_last_batch , blocker_proc.lastwaittype blocker_last_waittype , blocker_req.total_elapsed_time AS blocker_totalElapsedTime , blocker_req.cpu_time AS blocker_cpuTime , REPLACE(REPLACE(iif(blocker_sqltext.TEXT is NULL,blocker_sqltext2.event_info,blocker_sqltext.TEXT), CHAR(13), ' '), CHAR(10), ' ') AS blocker_last_query , CASE blocker_ses.transaction_isolation_level , blocker_ses.program_name blocker_programName , osw.blocking_session_id AS blocker_SessionID , blocked_ansaction_id blocked_transaction_id , req.total_elapsed_time AS blocked_totalElapsedTime , REPLACE(REPLACE(sqltext.TEXT, CHAR(13), ' '), CHAR(10), ' ') AS blocked_last_query , DB_NAME(dtl.resource_database_id) AS database_name $SqlQuery = "SELECT current_timestamp as #To get information about sessions that are blocking and being blocked It will create CSVs with the result of the queries in the $OutputFolder. Note: It is necessary to set values for the highlighted variables. You can use the PowerShell script below, while attempting to connect, to identify the session that is causing the block: If one of the queries performed by SSMS is blocked, you may face a lock time-out in the connection: After this your query for the drop table/Column should complete.When you connect to the database using SQL Server Management Studio (SSMS), It will perform several queries to gather the information necessary for the user interface. If you deem that query inconsequential, then you can kill it using kill. Once you identify this, you can use dbcc inputbuffer(spid) to find out what query/command it is doing that has taken a lock on this table. Next query to see who the owner of the spid is (this will tell you who owns the conflicting lock on this table). Look at the blocked column and note down the spid number. Once this goes into "executing" stage, open another query window and do this, use master Note down the spid of this query window (it should be visible in the bottom of SSMS, right next to username). I ran sp_who2 and did not see anything in the BlkBy column.ĭrop the table using T-SQL as then the query will not timeout but instead get blocked. WHERE l.resource_associated_entity_id = o.object_id Resource_description, o.object_id, o.name, o.type_desc Request_status, request_mode,request_session_id, Using this query specifically: SELECT resource_type, resource_associated_entity_id, I checked for locks using a query found here: ALTER TABLE VEN DROP COLUMN RI_VEN_Approved I used management studio (which fails quickly) and T-SQL (which takes longer but still fails). But when I try to remove this one column I get the error. I created a table in production and dropped that. I dropped the column in our development database just fine. However this seems impossible and I do not know why. I created some columns in our production database but now I need to drop one.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |