I had a question from a fellow DBA about how to list every file per SQL Server filegroup , and report on the size in MB. This will give them a quick way of checking which file could be creating a capacity issue. Quite often , alerting is set up to check the whole drive , but won't check every file. So some granular report can identify the file causing a capacity issue --Return file , size in MB , filegroup SELECT sdf.name AS [File_Name], sdf.size/128 AS [Size_MB], fg.name AS [FileGroup_Name], sdf.physical_name as [File_physical_name] FROM sys.database_files sdf INNER JOIN sys.filegroups...
Read more →