In addition, if you use the sp_spaceused() database function, it only looks at space at the database level. If you use more than one file group per database it doesn’t work (most people do so that they can separate data from indexes).
sp_spaceused and filegroups
According to a vendor of monitoring products, sp_spaceused does not work properly if there is more than one filegroup:
Is this true I need to carefully monitor space utilization on SQL Server 2000 deployed on laptops. (By the way, if you know of any great scripts or source code for monitoring, please let me know.)
sp_spaceused and filegroups
Bren Besser
This sounds like a false, unsubstantiated claim by the vendor. If they have particular concerns, they should be very explicit.
You can look at the source of sp_spaceused to see how the space usage is computed and base you monitoring code on that.
You can also use the following code to create/experiment with multiple filegroups and space used:
CREATE DATABASE MyDB
ON PRIMARY
( NAME='MyDB_Primary',
FILENAME=
'c:\MyDB_Prm.mdf',
SIZE=4MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
FILEGROUP MyDB_FG1
( NAME = 'MyDB_FG1_Dat1',
FILENAME =
'c:\MyDB_FG1_1.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
( NAME = 'MyDB_FG1_Dat2',
FILENAME =
'c:\MyDB_FG1_2.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
FILEGROUP MyDB_FG2
( NAME = 'MyDB_FG2_Dat1',
FILENAME =
'c:\MyDB_FG2_1.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
( NAME = 'MyDB_FG2_Dat2',
FILENAME =
'c:\MyDB_FG2_2.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
LOG ON
( NAME='MyDB_log',
FILENAME =
'c:\MyDB.ldf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB);
GO
ALTER DATABASE MyDB
MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO
-- Create a table in the user-defined filegroup.
USE MyDB;
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )
ON MyDB_FG1;
GO
EXEC sp_spaceused @updateusage = N'TRUE'
GO
Regards,
Boris.