Microsoft – SQL Server – Database Snapshot – File Metrics

Microsoft – SQL Server – Database Snapshot – File Metrics



SELECT
         DB_NAME(tblDatabase.database_id) as [databaseName]
       , DB_NAME(tblDatabase.source_database_id) as sourceDatabase
       , convert(varchar(20), tblDatabase.create_date, 101) as createDate
       , tblDatabasefile.filename
       , tblDatabasefile.fileid
       , (tblFileStat.size_on_disk_bytes / 1024 / 1024) as sizeInMB
       , (tblFileStat.num_of_bytes_written / 1024 / 1024) as bytesWrittenInMB
       , (tblFileStat.io_stall / 1E6) as IOStallWaitTimeInSec
       , (tblFileStat.num_of_writes) as NumberofWrites

FROM sys.databases tblDatabase

       inner join sys.dm_io_virtual_file_stats(null, null) tblFileStat

          on tblDatabase.database_id = tblFileStat.database_id

      inner join master.dbo.sysaltfiles tblDatabasefile

          on tblFileStat.database_id = tblDatabasefile.dbid
          and tblFileStat.file_id = tblDatabasefile.fileid

where tblDatabase.source_database_id is not null

order by

      tblFileStat.[size_on_disk_bytes] desc

References:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s