moving Tempdb.mdf for speed

moving Tempdb.mdf for speed

Post by Mike » Fri, 26 Apr 2002 01:43:14



Is it possible to relocate tempdb.mdf file to another
partition on the drive. I have only 400MB of space
available on my c:\ drive, while the D:\partition has 20GB
of space. The limit on the drive seems to be slowing down
the performance on data retrieval.
SQL Server: 7.0
Win 2000 Server
Mem: 256MB
Computer: 900MHZ
 
 
 

moving Tempdb.mdf for speed

Post by Dinesh T » Fri, 26 Apr 2002 02:15:26


Mike,

This might help..
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q304692

Another partition is not goin to shoo away the I/O problems.To gain
additional performance, you may need to separate tempdb onto its own
seperate drive/controller -  RAID 1 or 10 depending on the space required.

fyi only....Tempdb gets recreated with every restart of sql service.

Dinesh.
--
---
SQL Server FAQ at
www.tkdinesh.com

Quote:> Is it possible to relocate tempdb.mdf file to another
> partition on the drive. I have only 400MB of space
> available on my c:\ drive, while the D:\partition has 20GB
> of space. The limit on the drive seems to be slowing down
> the performance on data retrieval.
> SQL Server: 7.0
> Win 2000 Server
> Mem: 256MB
> Computer: 900MHZ


 
 
 

moving Tempdb.mdf for speed

Post by erwin.vdbo.. » Thu, 02 May 2002 19:19:55


In the case of the Tempdb, I shouldn't use RAID 1 (mirror).
You'd better use RAID 0 or Raid 10.
Since the TempDb is often a bottleneck and fault tolerance is
irrelevant to TempDb, the fastest and cheapest solution is to use RAID
0.



Quote:>Mike,

>This might help..
>http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071
>http://support.microsoft.com/default.aspx?scid=kb;en-us;Q304692

>Another partition is not goin to shoo away the I/O problems.To gain
>additional performance, you may need to separate tempdb onto its own
>seperate drive/controller -  RAID 1 or 10 depending on the space required.

>fyi only....Tempdb gets recreated with every restart of sql service.

>Dinesh.

 
 
 

1. Tempdb.mdf and other *.mdf files??

Hi,

I am new to SQL 7.0, and I am going to an in residence 70-028 course
next week.

Occasionally I have to restart the Server service in order to recreate
the tempdb.mdf file, for it is causing my C: drive to fill.

Can I permanently move/point the tempdb.mdf (and other files like
master.mdf) file to my logical drive which has 100 gig of free space.

My C: drive is typically sized to handle the system/program file only (2
gig in size).

I am sure that I will get all the answers that I need next week, but I
am flying by the seat of my pants this week.

Any options would be appreciated.

Cheers,
BKD..

2. Validate a date ? - Illustra Web Blade

3. Tempdb.mdf / other mdf files ????

4. De-framentation of segments

5. moving tempdb and starting db when tempdb is lost

6. Beginner Question.

7. clone database from 1 mdf file to multiple .mdf files (.mdf and .ndf)

8. Date Scripting Problem

9. TempDB.mdf growth in SQL Server 2000

10. Tempdb.mdf over 2GB??

11. How did tempdb.mdf become Read-only?

12. Big size tempdb.mdf

13. move MSDE mdf file to SQL 7