Determining Space used by Filegroups

[Nidhi]

Hello Experts,

I am using SQL Server 2000 and have created two filegroups for my database.
I want to monitor the space usage of my database, so I use the procedure sp_spaceused.
The problem is that sp_spaceused reports space usage for the entire database. If one of my filegroups were to run out of space while there was still plenty of space in the other filegroup, sp_spaceused would probably not be able to detect that.
Is there a way to monitor the space used per filegroup?

Thanks in Advance,
Nidhi

[laloooji]

dbcc showfilestats( n )

n is the fileid which you can get from
select fileid,name from sysfiles

Hope this helps..

[nosepicker]

Here is a query that simulates sp_spaceused, modified to group by groupid (you can look up the filegroup names in sysfilegroups):

SELECT
groupid,
SUM(CASE WHEN indid < 2 THEN [rows] END) AS [rows],
SUM(reserved) * 8 AS reserved,
SUM(dpages) * 8 AS data,
SUM(used - dpages) * 8 AS index_size,
SUM(reserved - used) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY groupid
ORDER BY groupid