March Madness – SQL Server System Tables – sysaltfiles

How often have you wanted to know the size of your database files? And I don’t just mean one database, I am talking about all your database files. Or how about knowing if any of your databases are set to autogrow (or not grow at all)? Or how about a way to track the size of your databases over time?

Well fear not, you can get all of this done by using the sysaltfiles system table. As with the earlier posts in this series, please note that this system table will be removed from a future version of SQL Server.

What is this table for?

The BOL definition is, well, interesting: “Under special circumstances, contains rows corresponding to the files in a database.” I have no idea what special circumstances they are referring to, leave me a comment if you know. Otherwise I am going with this as my definition: “This table will contain one row for each database file.”

One thing to note is that this is NOT the same table as sysfiles. The difference is in the scope. The sysaltfiles is scoped at the instance level, the sysfiles table is scoped at the database level. Warrants mentioning.

Why should you care?

I would use this table to get back some basic information about my database files and store the details nightly:

SELECT *
FROM master.dbo.sysaltfiles

That way I could track growth over time, or I could periodically run a report to verify that my database files were all set to autogrow. Another use I had was that I wanted the logical and physical file names to match in some way (i.e., logical name is db_data, physical name would be db_data.mdf) and I could use these details to ensure the names were as expected.

(Why would I be so OCD about that? Because when a member of the server team calls you to say that there is a database file named ‘foo’ that has grown out of control, you want to to easily match up the name of ‘foo’ to the ‘foo’ database with0ut having to take the extra steps, if possible).

Where else is this information?

Remember how I said this system table will be removed from a future version of SQL Server? What you want to be using instead is the sys.master_files catalog view:

SELECT *
FROM sys.master_files

And, yes, there also exists a similar catalog for just the current database:

SELECT *
FROM sys.database_files

I like the new catalog views much better as it is easier for me to calculate the actual growth for the database file without having to resort to some bitwise operations. So, I could use something similar to the following:

SELECT name,(size*8.0)/1024.0 as [SizeMb],
 CASE
 WHEN is_percent_growth = 1 THEN ((size*8.0)/1024.0)*(growth/100.0)
 WHEN is_percent_growth = 0 THEN (growth)*8.0/1024.0
 ELSE 0
 END as [WillGrowByinMb]
FROM sys.master_files

This would help to identify databases that have the potential to grow by a rather large amount. And if I was feeling rather frisky then I could also calculate the amount of free space left on disk to see if I had enough to hold an autogrow event for the three largest potential growths.

Believe me, it is always better to avert issues than fighting fires after they happen.