Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 1 March 2013

Which Database use More Transactions on SQL Server

To know the Which Database use more transactions like insert/update/delete:

Script 1:

WITH trans
AS (SELECT
 DB_NAME(dm_tran_database_transactions.database_id) AS DB
 ,COUNT(*) AS NumberOfTrans
FROM sys.dm_tran_database_transactions
GROUP BY DB_NAME(dm_tran_database_transactions.database_id))
SELECT TOP 10
 trans.DB
 ,trans.NumberOfTrans
FROM
 trans
ORDER BY trans.NumberOfTrans DESC



Script 2:

SELECT TOP 10
 dm_os_performance_counters.object_name
 ,dm_os_performance_counters.counter_name
 ,dm_os_performance_counters.instance_name
 ,dm_os_performance_counters.cntr_value
 ,dm_os_performance_counters.cntr_type
FROM
 sys.dm_os_performance_counters
WHERE
 counter_name = 'Transactions/sec'
 AND dm_os_performance_counters.instance_name <> N'_Total'
ORDER BY
 dm_os_performance_counters.cntr_value DESC 



Script 3:

SELECT transaction_id, DB_NAME(database_id) DatabaseName,
database_transaction_begin_time TransactionBegin,
CASE database_transaction_type
WHEN 1 THEN 'Read/Write'
WHEN 2 THEN 'Read only'
WHEN 3 THEN 'System' END AS TransactionType,
CASE database_transaction_state
WHEN 1 THEN 'Not Initialized'
WHEN 3 THEN 'Transaction No Log'
WHEN 4 THEN 'Transaction with Log'
WHEN 5 THEN 'Transaction Prepared'
WHEN 10 THEN 'Commited'
WHEN 11 THEN 'Rolled Back'
WHEN 12 THEN 'Commited and Log Generated' END AS TransactionState,
database_transaction_log_record_count LogRecordCount,
database_transaction_log_bytes_used LogBytesUsed,
database_transaction_log_bytes_reserved LogBytesReserved
FROM sys.dm_tran_database_transactions
--WHERE transaction_id > 1000