Optimizing tempdb Performance, Using filegroups with tempdb

Optimizing tempdb Performance, Using filegroups with tempdb

Post by Ulysses B. Brown Jr » Sun, 19 Aug 2001 20:01:38



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, Using filegroups with tempdb

Post by Jasper Smit » Mon, 20 Aug 2001 08:49:21


It probably means it the other way round.THe basic point
is to put tempdb on its own disk/disks to isolate its IO
activity from your user databases which you can do with
the alter database syntax. RAID 1+0 or RAID 1 would be
best (well RAID 0 probably if you've got some spares to
stick in should the disk(s) fail) as its write intensive
and RAID 1 would have better write performance than say
RAID 5 and still allow parallel reads.
THis is lower down the list to making sure your user
databses data files are on different disks than your user
log files.

Quote:>-----Original Message-----
>In SQL Server Books Online under subject: Optimizing

tempdb Performance it
Quote:>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
Quote:>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.

- Show quoted text -

Quote:>Any ideas on how to make this work?

>--
>Ulysses B. Brown Jr.

>.


 
 
 

1. Optimizing tempdb Performance. Use of 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. Howto? ADO 2.5 SP2, and Foreign Keys etc...

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

4. Insert record before First in Forms 4.5?

5. TempDB and filegroups

6. DROP DATABASE

7. TempDB Default filegroup full

8. WIN 5.0 Report bugs and suggestions

9. filegroup/ log/ tempdb placement

10. moving tempdb and starting db when tempdb is lost

11. shrinking tempdb without tempdb existing