This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view sysaltfiles returns one row for every file associated with a database.  Each row contains details about each of the files such as the name and location of the file, and the database that the file is associated with.  Along with these details, the growth rates and details about maximum size are stored.

The primary replacement catalog view for sysaltfiles is sys.master_files; which also returns one row per file per database on the SQL Server instance.  Besides some naming differences between some of the columns, there are a number of new columns in the catalog view and the contents of the status column is unpacked.

Decomposing Internal Columns

There are two columns sysaltfiles that contain information that is marked as being for internal use.  The columns are perf and status.  To the best of my knowledge, there isn’t any translation to the purpose of the column perf that is publically known.  The status column, though, contains bit values that are used to describe various properties of the database files.

Known Bit Values

The following bit values are known.  They are included to assist in transitioning old applications and use cases to sys.master_files.

• 0×1: Indicates whether the file is the default device, this is unused since SQL Server 2000.  Default to 0.
• 0×2: Indicates whether the file is a disk device.  All files currently disk devices and value defaults to 1.
• 0×10: Determines whether the media is read only.  This information is now found in sys.master_files.is_media_read_only.
• 0×40: Indicates whether the files is a transaction log file, this is replaced by the columns sys.master_files.type and sys.master_files.type_desc; which now includes whether the file is DATA, LOG, FILESTREAM, or FULLTEXT.
• 0×80: Indicates whether the file has been written to since last backup.  A replacement for this flag has not been identified.
• 0×1000: Indicates whether a file is marked as read only.  This information is now found in sys.master_files.is_read_only
• 0×4000: Indicates whether the device was created implicitly by CREATE DATABASE.  Devices are no longer used and the value defaults to 0.
• 0×8000: Indicates whether the device was created during database creation.

Devices are no longer used and the value defaults to 0.

• 0×100000: Indicates whether the growth is in percentage versus not pages
• 0×10000000: Indicates whether the state of the file is offline; currently this information is found in sys.master_files.state and sys.master_files.state_desc.
• 0×20000000: Indicates whether the file contains sparse data.  This is now found in sys.master_files.is_sparse.

Unknown Bit Values

As with many bit value collections in catalog views there are often many that are unpublished, or undiscovered.  Based on the values that have been identified so far, the bit values below are unknown.  If you know any of these values, please leave a comment below.

0×4, 0×8, 0×20, 0×100, 0×200, 0×400, 0×800, 0×2000, 0×10000, 0×20000, 0×40000, 0×80000, 0×200000, 0×400000, 0×800000, 0×1000000, 0×2000000, 0×4000000, 0×8000000, 0×40000000, 0×80000000

Query Via sysaltfiles

When querying sysaltfiles, the typical query would look something similar to that in Listing 1.  This query returns all of the columns from the compatibility view and unpacks the status column.

Listing 1 – Query for sysaltfiles

SELECT fileid
,groupid
,size
,maxsize
,growth
,status
,perf
,dbid
,name
,filename
,CONVERT(INT,status & 0x1) / 1 [Default device (unused in SQL Server 2000)]
,CONVERT(INT,status & 0x2) / 2 [Disk file]
,CONVERT(INT,status & 0x10) / 16 is_media_read_only
,CONVERT(INT,status & 0x40) / 64 is_transaction_log
,CONVERT(INT,status & 0x80) / 128 is_file_written_to_since_backup
,CONVERT(INT,status & 0x1000) / 4096 is_read_only
,CONVERT(INT,status & 0x4000) / 16384 device_created_implicitly_by_create_database
,CONVERT(INT,status & 0x8000) / 32768 device_created_during_database_creation
,CONVERT(INT,status & 0x100000) / 1048576 is_growth_percentage
,CONVERT(INT,status & 0x10000000) / 268435456 is_sparse
,CONVERT(INT,status & 0x20000000) / 536870912 is_offline
FROM sysaltfiles

Additional DMO Information

Using the compatibility view over the sys.master_files misses out on some new key information available in the new DMOs.  A couple good examples are found in the columns type_desc and state_desc.  In the compatibility view, there is only a single type or state identified, while SQL Server currently has multiple values for each, as shown in the table below.

There are also a number of columns relating to the current log sequence numbers (LSN) that relate to the state of the database and the current state of the data.  These can be helpful for determining when backups occurred and when data has changed; which can assist in data recovery and validation.

Query via DMOs

To get the same information from sysaltfiles, plus the additional columns discussed in the previous section, the query in Listing 2 can be used.

Listing 2 – Query for sys.master_files

SELECT file_id
,data_space_id
,size
,max_size
,growth
,NULL AS status
,0 AS perf
,database_id
,name
,physical_name
,type
,type_desc
,state
,state_desc
,is_media_read_only
,is_read_only
,is_sparse
,is_percent_growth
FROM sys.master_files

Summary

Based on the information available in sys.master_files, you can easily upgrade from using sysaltfiles without a loss of important information.

Resources

• SQL Server: Understanding SYSFILES/SYSALTFILES status
• sys.sysaltfiles

Related posts:

1. Lost in Translation – Deprecated System Tables – Introduction
2. Find Tables and Columns by Data Type
3. Find Tables with Forwarded Records