$DataSet.Tables | Export-Csv -Delimiter ' ' -Path "$OuputFolder\test_connection_$(get-date -f yyyy-MM-dd-HH.mm.ss).csv" -NoTypeInformation $DataSet.Tables | Export-Csv -Delimiter ' ' -Path "$OuputFolder\lock_list_$(get-date -f yyyy-MM-dd-HH.mm.ss).csv" -NoTypeInformation $DataSet.Tables | Export-Csv -Delimiter ' ' -Path "$OuputFolder\lock_waits_$(get-date -f yyyy-MM-dd-HH.mm.ss).csv" -NoTypeInformation #Create the objects that will be used to run the queries $SqlConnection.ConnectionString = "Server = $SQLServer Database = $SQLDBName User ID = $SQLUsername Password = $SQLPassword" $SqlQuer圓 = "select CURRENT_TIMESTAMP as timestamp, as server_name, DB_NAME() as database_name, as session_id " #Dummy query to test if queries are running successfully, in case the previous ones do not return data INNER JOIN sys.dm_tran_locks locks on osw.blocking_session_id = locks.request_session_id or (osw.session_id = locks.request_session_id and osw.blocking_session_id is not null) , locks.resource_type, locks.resource_subtype Select DB_NAME(locks.resource_database_id) AS database_name #To get the locks that are being held by the sessions that are blocking and being blocked OUTER APPLY sys.dm_exec_input_buffer(osw.blocking_session_id,0) as blocker_sqltext2 " OUTER APPLY sys.dm_exec_sql_text(blocker_req.sql_handle) AS blocker_sqltext ![]() LEFT JOIN sys.dm_tran_session_transactions blocker_tran on blocker_ssion_id =osw.blocking_session_id ![]() LEFT JOIN sys.dm_exec_sessions blocker_ses on blocker_ses.session_id = osw.blocking_session_id 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: 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |