Optimizing tempdb Performance. Use of filegroups with tempdb.

Optimizing tempdb Performance. Use of filegroups with tempdb.

Post 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.

 
 
 

Optimizing tempdb Performance. Use of filegroups with tempdb.

Post 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. )

 
 
 

1. Optimizing tempdb Performance, Using filegroups with tempdb

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.

2. Character Equality

3. Performance/TempDB Config Issue: Sorting Queries and TempDB

4. Unable to install SQL Server 7 Std. Downloaded from MSDN

5. TempDB Default filegroup full

6. IDAPI Error $2109

7. TempDB and filegroups

8. Changing MESSAGE when redefining buttons PROMPT

9. filegroup/ log/ tempdb placement

10. moving tempdb and starting db when tempdb is lost

11. shrinking tempdb without tempdb existing