#Connect to SQL and run QUERY
$SQLServer = “”
$SQLDBName = “”
$SQLUsername = “”
$SQLPassword = “”
$OuputFolder = “C:”
#To get information about sessions that are blocking and being blocked
$SqlQuery = “SELECT current_timestamp as [CURRENT_TIMESTAMP]
, DB_NAME(dtl.resource_database_id) AS database_name
, req.session_id AS blocked_sessionID
, ses.program_name blocked_programName
, ses.host_name blocked_hostname
, ses.login_name blocked_login
, CASE ses.transaction_isolation_level
WHEN 1 THEN ‘ReadUncomitted’
WHEN 2 THEN ‘ReadCommitted’
WHEN 3 THEN ‘Repeatable’
WHEN 4 THEN ‘Serializable’
WHEN 5 THEN ‘Snapshot’
END blocked_isolation_level
, REPLACE(REPLACE(sqltext.TEXT, CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) AS blocked_last_query
, req.status AS [blocked_status]
, req.command AS blocked_command
, req.cpu_time AS blocked_cpuTime
, req.total_elapsed_time AS blocked_totalElapsedTime
, blocked_tran.transaction_id blocked_transaction_id
, osw.blocking_session_id AS blocker_SessionID
, blocker_ses.program_name blocker_programName
, blocker_ses.host_name blocker_hostName
, blocker_ses.login_name blocker_login
, CASE blocker_ses.transaction_isolation_level
WHEN 1 THEN ‘ReadUncomitted’
WHEN 2 THEN ‘ReadCommitted’
WHEN 3 THEN ‘Repeatable’
WHEN 4 THEN ‘Serializable’
WHEN 5 THEN ‘Snapshot’
END blocker_isolation_level
, REPLACE(REPLACE(iif(blocker_sqltext.TEXT is NULL,blocker_sqltext2.event_info,blocker_sqltext.TEXT), CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) AS blocker_last_query
, blocker_req.status AS [blocker_status]
, blocker_req.command AS blocker_command
, blocker_req.cpu_time AS blocker_cpuTime
, blocker_req.total_elapsed_time AS blocker_totalElapsedTime
, blocker_proc.lastwaittype blocker_last_waittype
, blocker_proc.last_batch blocker_last_batch
, blocker_proc.open_tran blocker_open_tran
, blocker_tran.transaction_id blocker_transaction_id
, blocker_proc.cmd blocker_command
, dtl.request_mode AS lockRequestMode
, dtl.resource_type AS lockResourceType
, dtl.resource_subtype AS lockResourceSubType
, osw.wait_type AS taskWaitType
, osw.resource_description AS taskResourceDescription
, osw.wait_duration_ms
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions ses on ses.session_id = req.session_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext
INNER JOIN sys.dm_tran_locks dtl on dtl.request_session_id = req.session_id
INNER JOIN sys.dm_os_waiting_tasks osw on osw.session_id = req.session_id
LEFT JOIN sys.dm_tran_session_transactions blocked_tran on blocked_tran.session_id =req.session_id
INNER JOIN dbo.sysprocesses blocker_proc on osw.blocking_session_id = blocker_proc.spid
LEFT JOIN sys.dm_exec_requests blocker_req on blocker_req.session_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_tran_session_transactions blocker_tran on blocker_tran.session_id =osw.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(blocker_req.sql_handle) AS blocker_sqltext
OUTER APPLY sys.dm_exec_input_buffer(osw.blocking_session_id,0) as blocker_sqltext2;”
#To get the locks that are being held by the sessions that are blocking and being blocked
$SqlQuery2 = “
select DB_NAME(locks.resource_database_id) AS database_name
, locks.request_session_id
, locks.resource_type, locks.resource_subtype
, locks.resource_description
, locks.resource_associated_entity_id
, locks.resource_lock_partition
, locks.request_mode
, locks.request_type
, locks.request_status
, locks.request_reference_count
, locks.request_lifetime
, locks.request_exec_context_id
, locks.request_request_id
, locks.request_owner_type
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_os_waiting_tasks osw on osw.session_id = req.session_id
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)
order by locks.request_session_id;”
#Dummy query to test if queries are running successfully, in case the previous ones do not return data
$SqlQuery3 = “select CURRENT_TIMESTAMP as timestamp, @@SERVERNAME as server_name, DB_NAME() as database_name, @@SPID as session_id;”
#Connect to SQL Server
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server = $SQLServer; Database = $SQLDBName; User ID = $SQLUsername; Password = $SQLPassword”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
#Create the objects that will be used to run the queries
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
#Run 1st query
$DataSet = New-Object System.Data.DataSet
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)
#Output RESULTS of 1st query to CSV
$DataSet.Tables[0] | Export-Csv -Delimiter ‘;’ -Path “$OuputFolderlock_waits_$(get-date -f yyyy-MM-dd-HH.mm.ss).csv” -NoTypeInformation
#Run 2nd query
$DataSet = New-Object System.Data.DataSet
$SqlCmd.CommandText = $SqlQuery2
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)
#Output RESULTS of 2nd query to CSV
$DataSet.Tables[0] | Export-Csv -Delimiter ‘;’ -Path “$OuputFolderlock_list_$(get-date -f yyyy-MM-dd-HH.mm.ss).csv” -NoTypeInformation
#Run 3rd query
$DataSet = New-Object System.Data.DataSet
$SqlCmd.CommandText = $SqlQuery3
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)
#Output RESULTS of 3rd query to CSV
$DataSet.Tables[0] | Export-Csv -Delimiter ‘;’ -Path “$OuputFoldertest_connection_$(get-date -f yyyy-MM-dd-HH.mm.ss).csv” -NoTypeInformation
#Close the connection
$SqlConnection.Close()
|
Recent Comments