SQL Server: Understanding SYSFILES/SYSALTFILES status

SYSFILES and SYSALTFILES describe the data files used by SQL Server databases. They have the same structure, except that SYSFILES refers to data files in the current database, and SYSALTFILES resides in the MASTER database and refers to all data files in the database.

The STATUS column of SYSALTFILES is made up of a series of flags to tell you properties of a data file. The following query checks for the flags. A zero indicates the flag is not set, a non-zero indicates that the flag is set:

select name, 
       status,
       status & 0x1 [Default device (unused in SQL Server 2000)],
       status & 0x2 [Disk file],
       status & 0x40 [Log device],
       status & 0x80 [File has been written to since last backup],
       status & 0x4000 [Device created implicitly by CREATE DATABASE],
       status & 0x8000 [Device created during database creation],
       status & 0x100000 [Growth is in percentage, not pages]
  from master..sysaltfiles

There is more information on this table in Inside SQL Server 2000 by Kalen Delaney from Microsoft Press.