Microsoft SQL Server 2008 R2 Unleashed- P116:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1094 CHAPTER 34 Data Structures Indexes and Performance TABLE The sysfiles Table Column Name Description file_id A file identification number that is unique within each database file_guid GUID for the file type File type 0 rows that is data files 1 log 2 FILESTREAM 4 Full-text catalogs prior to SQL Server 2008 type_desc Description of the file type ROWS LOG FILESTREAM FULLTEXT data_space_id 0 represents a log file values 0 represent the ID of the filegroup the data file belongs to name The logical name of the file filename The physical name of the file including path state File state 0 OFFLINE 1 RESTORING 2 RECOVERING 3 RECOVERY_PENDING 4 SUSPECT 6 OFFLINE 7 DEFUNCT state_desc Description of the file state OFFLINE RESTORING RECOVERING RECOVERY_PENDING SUSPECT OFFLINE DEFUNCT size Current size of the file in 8KB pages max_size Maximum file size in 8KB pages growth File growth setting 0 fixed 0 autogrow in units of 8KB pages or by percentage if is_percent_growth is set to 1 is_media_read_only 1 file is on read-only media is_read_only 1 file is marked read-only is_sparse 1 file is a sparse file is_percent_growth 1 growth of file value is percentage SQL Server uses the file location information visible in the catalog view most of the time. However the Database Engine uses the file location information stored in the primary file to initialize the file location entries in the master database when attaching a database using the CREATE DATABASE statement with either the FOR ATTACH or FOR ATTACH_REBUILD_LOG options. Every database can have three types of files Primary data file Secondary data files Log files In addition in SQL Server 2008 databases can also have FILESTREAM data files and fulltext data files. Download from Database Files and Filegroups 1095 Primary Data File Every database has only one primary database file. The location of the primary database file is stored in the master database visible via the filename column in the
