SQL Server Performance Forum – Threads Archive

Determine the size of files

I know you can use DBCC SHOWFILESTATS to get the file_id, group_id, total_extents, and used extents. But I need to automate a procedure for reporting summations of this data. I can’t seem to insert the output of the DBCC command into a temp table and them build my reports off of this. Is there a way to re-create the output of DBCC SHOWFILESTATS from the system tables? In the end, what I’m trying to do, is get a usage report of the combined size of files in each filegroup, a % of used space, and available space in MB. Thanks Jeff Motter

Check this DBJournal article http://www.databasejournal.com/features/mssql/print.php/1467771] to accomplish the task. _________
Satya SKJ

Here is the code for u CREATE TABLE #FILESTAT
(
Fileid INT ,
FileGroup INT ,
TotalExtents INT,
UsedExtents INT,
Name VARCHAR500),
FileName VARCHAR(500)
)
INSERT INTO #FILESTAT
EXEC (‘DBCC SHOWFILESTATS’)
SELECT * FROM #LOGSPACE Rushendra

CREATE TABLE #FILESTAT
(
Fileid INT ,
FileGroup INT ,
TotalExtents INT,
UsedExtents INT,
Name VARCHAR(500),
FileName VARCHAR(500)
)
INSERT INTO #FILESTAT
EXEC (‘DBCC SHOWFILESTATS’)
SELECT * FROM #FILESTAT Rushendra

]]>