Very Computer

by **Ulysses B. Brown Jr** » Mon, 27 Aug 2001 00:52:45

In SQL Server Books Online under subject: Optimizing tempdb Performance it
states on the last bullet - "Use filegroups to place the tempdb database on
disks different from those used by user databases."

However whenever I try to use filegroups for tempdb I receive the following
error in Query Analyzer - "Server: Msg 1826, Level 16, State 4, Line 1
User-defined filegroups are not allowed on 'tempdb'."

I am running SQL Server 2000. I am executing the following query to
implement filegroups on tempdb -

USE master
GO
ALTER DATABASE tempdb
ADD FILEGROUP tempdb_data_fg1
GO
ALTER DATABASE tempdb
ADD FILE
( NAME = tempdbdata2,
FILENAME = 'C:\MSSQL\Data\TempDB\tempdbdata2.ndf',
SIZE = 10MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB),
( NAME = tempdbdata3,
FILENAME = 'C:\MSSQL\Data\TempDB\tempdbdata3.ndf',
SIZE = 10MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP tempdb_data_fg1
GO

This is the same syntax used in Books Online but it doesn't work for me.
Any ideas on how to make this work?

--
Ulysses B. Brown Jr.

by **Umachandar Jayachandra** » Mon, 27 Aug 2001 04:31:56

Looks like an error in BOL. I will send this off to BOL. What you could
do is move the files for tempdb into different volumes / drives. It is as
good as using a filegroup in this case since we will only have temporary
objects here anyway.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

Very Computer

Optimizing tempdb Performance. Use of filegroups with tempdb.

Optimizing tempdb Performance. Use of filegroups with tempdb.

Optimizing tempdb Performance. Use of filegroups with tempdb.