How to monitor transaction activity between the databases?
Recently I have been through interesting forum post on SQL Server performance website that a user asking how to monitor transaction activity between the databases without using a third party tool.
Until SQL 2000 version it is not that easy to get more information on what you want, but with SQL 2005 by using DMVs you could such information that will help you to determine I/O activity by database. So here to go further details to determine I/O usage by Database:
--get I/O statistics by database
select db_name(m.database_id) as database_name,
sum(fs.num_of_reads + fs.num_of_writes) as total_io,
sum(fs.num_of_reads) as num_of_reads,
sum(fs.num_of_bytes_read) as num_of_bytes_read,
sum(fs.num_of_writes) as num_of_writes,
sum(fs.num_of_bytes_written) as num_of_bytes_written
from sys.dm_io_virtual_file_stats(NULL, NULL) fs
join sys.master_files m on fs.database_id = m.database_id and fs.file_id = m.file_id
group by db_name(m.database_id)
order by sum(fs.num_of_reads + fs.num_of_writes) desc
Additionally you can also run another TSQL as follows to get size of that datafile on the disk:
SELECT DB_NAME(vf.database_id), sum(size_on_disk_bytes) / (1024*1024.0) MB_Used
FROM sys.dm_io_virtual_file_stats(null, null) vf
join sys.master_files m on vf.database_id = m.database_id and vf.file_id = m.file_id
group by DB_NAME(vf.database_id)
order by MB_Used desc
[TSQL by Greg Larsen]
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.
