XML FeedsI 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.
- 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.
- 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.
- 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:
- 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.
- 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.
Jes Schultz Borland is a Consultant with Brent Ozar PLF, and a Microsoft SQL Server MVP. She holds an AAS - Programmer/Analyst degree, and has worked with SQL Server since 2007, focusing on Reporting Services and day-to-day administration. She is an active member of PASS, Director of Communication for MADPASS, founder of Tech on Tap, and a LessThanDot.com blogger. She's a frequent presenter at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef. 
Jes and Bob,
Also check out this post
I recently needed to write a script to find out the space used and compare if we're reaching a limit in case the Express version is used.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.