SQL Server – File stats using Fn_virtualfilestats

Fn_virtualfilestats() is in-build function in the SQL Server and really useful for understanding the amount of I/O within individual files on your SQL Server system. It returns I/O statistics for database files, including log files.

Syntax

fn_virtualfilestats ( { database_id | NULL } , { file_id | NULL } )

Example 1

File statistical information for a database

select * from :: fn_virtualfilestats (1,1)

File stats using Fn_virtualfilestats

Example 2

File statistical information for all databases and files

select * from :: fn_virtualfilestats (Null,Null)

File stats using Fn_virtualfilestats

Hope this will help you to analyze the file stats.