SELECT session_id, command, blocking_session_id, wait_type, wait_time, wait_resource, t.TEXT
FROM sys.dm_exec_requests
CROSS apply sys.dm_exec_sql_text(sql_handle) AS t
WHERE session_id > 50
AND blocking_session_id > 0
UNION
SELECT session_id, '', '', '', '', '', t.TEXT
FROM sys.dm_exec_connections
CROSS apply sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE session_id IN (SELECT blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0)
SELECT TBL.name AS ObjName
,STAT.row_count AS StatRowCount
,STAT.used_page_count * 8 AS UsedSizeKB
,STAT.reserved_page_count * 8 AS RevervedSizeKB
FROM tempdb.sys.partitions AS PART
INNER JOIN tempdb.sys.dm_db_partition_stats AS STAT
ON PART.partition_id = STAT.partition_id
AND PART.partition_number = STAT.partition_number
INNER JOIN tempdb.sys.tables AS TBL
ON STAT.object_id = TBL.object_id
ORDER BY TBL.name;
Transaction Property
1-Atomicity
2- Consistency
3- Isolation
4- Durable
Transaction Modes-
1- AutoCommit Transaction
2- Implicit
SET IMPLICIT_TRANSACTIONS ON
3- explicit
BEGIN TRAN
ROLLBACK TRANSACTION
COMMIT TRAN
