This is every DBA’s favorite game. Figuring out what DMV contains the data you want. It turns out there are two places that database file size info is maintained. Each database has
sys.database_fileswhich has information for that database. The master database also has
sys.master_files, which contains information for every database.
sys.master_filesseems like it would be the obvious choice: everything in one view in master is going to be easier to query than hitting a view in a bunch of different databases. Alas, there’s a minor snag. For tempdb,
sys.master_fileshas the initial file size, but not the current file size. This is valuable information, but doesn’t answer the use cases we set out above. I want to know the current file sizes. Thankfully,
sys.database_fileshas the correct current file sizes for tempdb, so we can use that.
sys.database_filesseems like it’s going to be the right move for us then. Alas, this isn’t quite perfect either. With Log Shipping, and Availability Group secondaries, if you’ve moved data files to a different location,
sys.database_fileswill contain the location of the files on the primary database. Thankfully,
sys.master_fileshas the correct local file locations for user databases, so we can use that.
Ugh, so it looks like the answer is “both”… we’ll need to use
sys.database_filesfor tempdb, and
sys.master_filesfor everything else.
Click through for the script, including Andy’s critical reflection on how Past Andy has failed him.