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 sysfiles returns one row for every file that is associated with a database.  The data returned is scoped at the database level.

The catalog view sys.database_files is the replacement for sysfiles.  Like sysfiles, sys.database_files returns one row for every file that is associated to a database.

Status Column

The compatibility view contains a single status column.  According to Books Online, there are only three properties exposed through the status column.  A little research, though, indicates there are more than those stored within the column.  The values that have been uncovered so far are:

• 0×1:Default device (no longer used)
• 0×2:On disk file
• 0×10:Indicates if media is read only
• 0×40:Indicates if file is the transaction log
• 0×80:Indicates if file has been written to since last backup
• 0×1000:Indicates if file is read only
• 0×4000:Indicates whether device was create implicitly by CREATE DATABASE
• 0×8000:Indicates whether device created during CREATE DATABASE
• 0×100000:Indicates whether the growth rate is a percentage
• 0×10000000:Indicates if the file contains sparse data
• 0×20000000: Indicate if the file is offline

Query Via sysfiles

When querying the data from sysfiles, there are a few things that need to be taken into account.  First, there are a number of values stuffed into the status column.  These values need to be retireved to understand all of the properties for the files.  Second, while there are a number of values, not all of them are in use any longer and refer more to constructs that are no longer a part of SQL Server today, such as media devices for databases.  Along with that, some methods of identifying the state of a file, such as device_created_implicitly_by_create_database, are vague and may not be well suited as a flagged item.  The last item is the perf column, this column is reserved for internal use and appears as a 0 in all examples found. The query to retrieve the information from sysfiles can be found in Listing 1.

--Listing 1 – Query for sysfiles
SELECT fileid
,groupid
,size
,maxsize
,growth
,name
,filename
,CONVERT(INT,status & 0x10) / 16 is_media_read_only
,CONVERT(INT,status & 0x1000) / 4096 is_read_only
,CONVERT(INT,status & 0x100000) / 1048576 is_percent_growth
,CONVERT(INT,status & 0x10000000) / 268435456 is_sparse
,CONVERT(INT,status & 0x40) / 64 is_transaction_log
,CONVERT(INT,status & 0x20000000) / 536870912 is_offline
,CONVERT(INT,status & 0x1) / 1 default_device
,CONVERT(INT,status & 0x2) / 2 disk_file
,CONVERT(INT,status & 0x80) / 128 is_file_written_to_since_backup
,CONVERT(INT,status & 0x4000) / 16384 device_created_implicitly_by_create_database
,CONVERT(INT,status & 0x8000) / 32768 device_created_during_database_creation
,status
,perf
FROM sysfiles

Query via sys.database_files

Since the catalog view view sys.database_files presents the previously mentioned status columns directly in the view, the query for this information is quite simple.  As shown in the query in Listing 2, most of the columns from sysfiles are present in the query. There are some exceptions to this though.  First, the perf, media, and device details, which are obsolete, are no longer represented.  Second, instead of using flag to represent things like device created during CREATE DATABASE, there are columns that indicate the LSN of when these events occurred.  In total there are twelve LSN events tracked that can provide the information previously flagged but with greater granularity.

--Listing 2 – Query for sys.database_files
SELECT file_id
,file_guid
,data_space_id as groupid
,size
,max_size
,growth
,name
,physical_name
,is_media_read_only
,is_read_only
,is_percent_growth
,is_sparse
,type
,type_desc
,state
,state_desc
,is_name_reserved
,create_lsn
,drop_lsn
,read_only_lsn
,read_write_lsn
,differential_base_lsn
,differential_base_guid
,differential_base_time
,redo_start_lsn
,redo_start_fork_guid
,redo_target_lsn
,redo_target_fork_guid
,backup_lsn
FROM sys.database_files

Summary

In this post, we discussed the use of sys.database_files instead of sysfiles.  While the information presented is quite simple, there are some nuances with some of the flags and ways in which SQL Server has changed how the files are organized.  But with anything using sysfiles with SQL Server 2012, the obsolete properties shouldn’t be an issue.  After reading all of this, do you see any reason to continue using sysfiles?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Follow me on Twitter at StrateSQL.

Original article: Lost in Translation – Deprecated System Tables – sysfiles

©2012 Strate SQL. All Rights Reserved.

Related posts:

1. Lost in Translation – Deprecated System Tables – sysdevices
2. Lost in Translation – Deprecated System Tables – sysaltfiles
3. Lost in Translation – Deprecated System Tables – syscurconfigs