Database Transaction lock information

Transaction lock

View the Transaction lock complete information using the following TSQL code.

select * 
FROM    sys.dm_tran_locks TL
LEFT JOIN sys.partitions SP 
ON SP.hobt_id = TL.resource_associated_entity_id
LEFT JOIN sys.objects O 
ON O.object_id = SP.object_id
LEFT JOIN sys.dm_exec_sessions S 
ON S.session_id = TL.request_session_id
LEFT JOIN sys.dm_tran_session_transactions ST 
ON S.session_id = ST.session_id
LEFT JOIN sys.dm_tran_active_transactions AT 
ON ST.transaction_id = AT.transaction_id
LEFT JOIN sys.dm_exec_connections C 
ON C.session_id = S.session_id
Database Transaction lock information was last modified: October 1st, 2017 by Justin Antony

Find Sql Server Dead Lock information

Dead Lock

Find the SQL Server Dead lock information using the following query.

SELECT *
FROM sys.dm_os_performance_counters
select * FROM
sys.dm_tran_locks T1
join sys.partitions T2
ON T1.resource_associated_entity_id = T2.hobt_id
WHERE   OBJECT_NAME(T2.object_id) = 'TBLNAME'
Find Sql Server Dead Lock information was last modified: October 1st, 2017 by Justin Antony

Passing dynamic date value based on day of the week to SSRS report.

Dynamic date

IN SSRS We can pass the last week Friday and current week Thursday dynamically as input parameter. You can also change the day of the week by changing the DATEADD value in the following query.

 SELECT DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
SELECT DATEADD(DAY, 3, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
Passing dynamic date value based on day of the week to SSRS report. was last modified: October 1st, 2017 by Justin Antony