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.
3 Comments
Jes and Bob,
That’s awesome, thanks! It’s always the newer stuff that I miss. Being caught between a Dev (SSIS 90% of the time) and a DBA(the other 50% of the time :), I can’t spend enough time on either to learn all I need to know to be effective. It’s folks like you that keep me up to date, thanks again!
David T
Also check out this post Find Out What Percent Of The Log Is Being Used For Each Database In SQL Server 2005 and 2008
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.
Here is this script
DECLARE @MaxSize INT, @MaxSizeDesc varchar(10)
SELECT @MaxSize = CASE WHEN SERVERPROPERTY(‘ProductVersion’) > ‘10.5’ THEN 10 * 1024 * 1024 — 10GB for SQL Server 2008 R2 Express
ELSE 4 * 1024 * 1024 END,
@MaxSizeDesc = CASE WHEN SERVERPROPERTY(‘ProductVersion’) > ‘10.5’ THEN ’10 GB’
ELSE ‘4 GB’ END
SELECT SUM(SIZE*8) AS SizeKB, @MaxSize AS MaxSize, @MaxSizeDesc as MaxSizeDesc
FROM sys.database_files
WHERE type = 0