I was recently working on a new presentation about filegroups (debuting in January! Come see me at the Wisconsin SSUG on January 10, 2012.). I was looking for a query to tie filegroups and files together. I found several blogs about it, but all of them referenced sysfiles.

Why is this a problem? The SQL 2000 virtual table sysfiles, and the corresponding SQL 2005 + compatibility view sys.sysfiles, will be removed in a future version of SQL Server. Our very own Denis Gobo has been writing a series about updating your code to use new syntax.

In that case, what’s the new hotness?

We should now be using the catalog view sys.database_files. Here, I’ll show you this view on SQL 2008R2, using AdventureWorks2008R2.

Here are the columns returned when querying sysfiles.

T-SQL
1
2
3
4
5
6

USE AdventureWorks2008R2;
GO
 
SELECT fileid, groupid, size, maxsize, growth, status, perf, name, filename
FROM sysfiles;
GO 
USE AdventureWorks2008R2;
GO

SELECT fileid, groupid, size, maxsize, growth, status, perf, name, filename
FROM sysfiles;
GO

Moving on, here is the query of sys.sysfiles.

T-SQL
1
2
3
4
5

USE AdventureWorks2008R2;
GO
 
SELECT fileid, groupid, size, maxsize, growth, status, perf, name, filename
FROM sys.sysfiles;
USE AdventureWorks2008R2;
GO

SELECT fileid, groupid, size, maxsize, growth, status, perf, name, filename
FROM sys.sysfiles;

This query’s result is the same as the original virtual table.

Now, the new hotness, sys.database_files.

T-SQL
1
2
3
4
5

USE AdventureWorks2008R2;
GO
 
SELECT *
FROM sys.database_files;
USE AdventureWorks2008R2;
GO

SELECT *
FROM sys.database_files;

There’s an immediate difference here in the number of columns returned. The new view is much more comprehensive than the old table.

What’s new?

sys.database_files includes more than just the data and log files. It will also show FILESTREAM and fulltext files.

The state_desc can tell you if the file is ONLINE, OFFLINE, RESTORING, and so forth.

There are fields to tell you if the file is read-only, if it’s on read-only media, and if it’s a sparse file.

Let’s look at a couple of useful queries using this new view.

A query to determine file size (with a little help from Bob Pusateri (twitter | blog)) is:

T-SQL
1
2
3
4
5
6
7

SELECT fg.data_space_id AS FGID,
   (f.file_id) AS FileCount,
   ROUND(CAST((f.size) AS FLOAT)/128,2) AS Reserved_MB,
   ROUND(CAST((FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,
   ROUND((CAST((f.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB
FROM sys.filegroups fg
    LEFT JOIN sys.database_files f ON f.data_space_id = fg.data_space_id
SELECT fg.data_space_id AS FGID,
   (f.file_id) AS FileCount,
   ROUND(CAST((f.size) AS FLOAT)/128,2) AS Reserved_MB,
   ROUND(CAST((FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,
   ROUND((CAST((f.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB
FROM sys.filegroups fg
	LEFT JOIN sys.database_files f ON f.data_space_id = fg.data_space_id

Another useful query, especially as it related to what I was doing, is to determine what filegroup a file is on.

T-SQL
1
2
3

SELECT FG.name as FilegroupName, F.file_id, F.name as [FileName] 
FROM sys.database_files F 
    INNER JOIN sys.filegroups FG ON FG.data_space_id = F.data_space_id;
SELECT FG.name as FilegroupName, F.file_id, F.name as [FileName] 
FROM sys.database_files F 
	INNER JOIN sys.filegroups FG ON FG.data_space_id = F.data_space_id;

As Microsoft moves away from the old virtual tables and compatibility views, make sure your T-SQL is also up to date. It’s a good idea to review scripts that have been in use for some time, and see if they can be updated.

Related Posts