question

askmlx121 avatar image
askmlx121 asked

Which database use more transactions on the server?

Hi, Which database use more Transaction(INSERT/UPDATE/DELETE) on Server from the Server Restart or initial point? Can I list the top 10 databases using a script in SQL SERVER?
transaction
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
eghetto avatar image
eghetto answered
SELECT DB_NAME(dm_tran_database_transactions.database_id) AS DB ,COUNT(*) AS NumberOfTran FROM sys.dm_tran_database_transactions GROUP BY DB_NAME(dm_tran_database_transactions.database_id) or for TOP 10: 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
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
ORDER BY NumberOfTrans DESC ???
3 Likes 3 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
@agoaskmlx121 - this code only counts active transactions, what are you trying to achieve?
2 Likes 2 ·
eghetto avatar image eghetto commented ·
In this case the following dmv might be helpful to get an **indication** which database produces the most activity on the server: 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
2 Likes 2 ·
eghetto avatar image eghetto commented ·
of course! don't forget ORDER BY! fixed my code - thanks!
1 Like 1 ·
askmlx121 avatar image askmlx121 commented ·
Hi thanking you for you Fatherjack. It gave the result of current tranactions only. May i get the status of database from initial which database are use more transactions?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.