Solved

Moving tempdb to another disk

Posted on 2004-04-13
23
1,357 Views
Last Modified: 2012-05-05
I read in a book, that it is not recommended to leave tempdb on disk C:, when the free-space isn't that much as tempdb might get big while working. They recommend to move tempdb to the biggest disk available, but don't explain how to do. I run SQL on a server and would like to move tempdb from C: to my Raid5-HD E:

What is the easiest way to move?

Thx
ColumA
0
Comment
Question by:ColumA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 3
  • +2
23 Comments
 
LVL 13

Expert Comment

by:danblake
ID: 10811664
0
 
LVL 13

Expert Comment

by:danblake
ID: 10811668
All mechanisms are relativly easy, it depends what you are more familair / comfortable with : EM / isql/...
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10811694
I'm not a real DBA, so you might get better advices from other experts
To move datafiles,
I'd use a backup and restore using the MOVE option

As for the tempdb itself, IMHO
- making a backup of this db doesn't make much sense since it is rebuilt from scratch each time SQL Server restarts
- I wouldn't move the tempdb to a RAID 5
by definition, the tempdb is supposed to hold temporary data, and is highly stressed in terms of disks IO, but its content is not worth the overhead of a real backup. Storing it on a RAID 5 might decrease performance (slower disk IO), and I'm not sure it will be better for your actual data, that should be kept secure by the transactions (COMMIT / ROLLBACK)
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

Author Comment

by:ColumA
ID: 10811741
Hi Hilaire

Interesting point regarding performance. But in the book (official MS-edition), the say that it may happen that data can not be saved because the tempdb is overloaded due to a to small disk space. I run 13GB free space on C:, while having 63GB on my RAID5. I actually run an Access.mdb with a size of 1.5GB, increasing 1GB a year. This is the reason why I am now looking for SQL. The system has to calculate 25'000 parameters every morning, so the tempdb might get big. I have no idea. What do you think.

danblake, thx for your link. I will take use of it when I get cleared to move the tempdb.

ColumA
0
 
LVL 13

Expert Comment

by:danblake
ID: 10811818
Yes, it looks about time to migrate from Access-> Sql server.

If you can put tempdb on a seperate partition (from everything else), there are good perf tuning technique tips from: http://www.sql-server-performance.com.  (You can always create multiple files for tempdb to allow multiple drives to be used if disk space is an issue).
0
 

Author Comment

by:ColumA
ID: 10811889
What do you recommend? Going for C: at the start and if getting problems, create multiple files for tempdb? Can this split been done afterward as well?
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 50 total points
ID: 10811912
Moving from C: to another (at least logical) disk will ensure that the auto-growing tempdb won't compete whith the windows OS swapping on the same disk for virtual memory, which could result in desastrous performances
0
 

Author Comment

by:ColumA
ID: 10812002
If I understand you right, you recommend to move tempdb to the RAID5, so I do not get in conflict with Windows OS, because both using diskspace while running the db. You don't see the a huge decrease in performance as the point of Hilaire was?

Do you know if Access2004 has a better upsizing-assistent? I now run 2002 and think of creating a whole new db as the upsizing is knows as not that good.
0
 

Author Comment

by:ColumA
ID: 10812089
Sorry Hilaire. Thought the last comment was from Dan. So ignore my performance-question.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10812130
Hi ColumnA, sorry for not developping further my last post but I had a meeting.
If you can partition your disk and create a new logical drive dedicated to tempdb, with, say, 5Go, this should be more than enough to handle at least two years of data based on growth rate, and leave enough space for the OS to swap if need be.
Of course that would be better to get a new physical disk with fast access time and high RPM.

I tend to think this would be better than putting the tempdb on the raid5

But, again, I'm not a real dba, roughly an experienced programmer with DB-related skills.

Danblake, what do you think ?

Hilaire




0
 
LVL 13

Expert Comment

by:danblake
ID: 10812266
Back now...
This database is going to start small... (I should imaging about 10 GB -- tops for 3 years?)

(We have a R5 | 63 GB -- Drive D, C - 18 GB (Total space) -- at a guess from the above posts).

Drive C needs to be a R1 (Move to 2 drive partition) to support a OS failure within the raid array/structure (if its not I would consider changing this if possible..).
R5 partition for data (3 Drives).

What do you recommend?
Going for C: at the start and if getting problems, create multiple files for tempdb?
Yes, This is going to be a small app to start with, if you are only running sql-server on the system the swap-file issue should not be a problem as the NT/2000 swap file is hardly used.  Its better to split the data from the tempdb, tempdb needs to be about 25% of your largest database on the system.  Splitting the drive in two as hillaire suggested is a good idea to keep the tempdb seperated from the os partition allowing a distinct area which can allow tempdb growth without fragmentation.  If you cannot split the hdd in two, then assign a large enough portion for tempdb to ensure that fragmentation does not become an issue on the HDD system.

Can this split been done afterward as well?
Yes, and moved based on perf monitoring results.

If I understand you right, you recommend to move tempdb to the RAID5, so I do not get in conflict with Windows OS, because both using diskspace while running the db. You don't see the a huge decrease in performance as the point of Hilaire was?
No, there is a larger performance hit if reading/writing data + tempdb to the same drives (especially if you are performing large opps or the system is busy, its always better to spread data/perf across many RAID systems/drives if possible).

Do you know if Access2004 has a better upsizing-assistent? (Sorry, I havn't looked at Access2004 -- yet...I believe the only real things that Access -> SQL Server conversions can handle are views, tables... the VBA code/etc will need to be converted to either VB\VB.Net and this does not easily change, you could run a half-way step by moving the data to sql-server and link to sql-server from access while you convert the rest of the application to any form of web-enabled system (CF, VB.Net, C#,...))

This is such a small system we should not overly worry about Hdds / etc and should run fine on a Serial ATA system (with RAID controller card -- to take away CPU cycle need for I/O -- similar to SCSI implementations with a RAID card).

It will become more important as your database > 18 GB +... or you need exceptionally blistering performance.
With this size system, we are trying to balance budget costings/performance.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10812380
If you really are concerned, reserve space on Drive C for tempdb with autogrow enabled, and place a second file on D (with a maximum limit of 0KB or lowest setting with no growth enabled) and if C is ever getting close to <15%, allow the file on drive D to be automatically extended.  It is possible to do this with a perf-monitor alert/osql and bit of t-sql.

Tempdb Optimizations :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_5j8l.asp

Disk counter required: Free Megabytes on C:
--> If < % of drive then fire off auto job to tempdb to limit size of file on Drive C for tempdb, and allow autogrow on drive D for tempdb.


If sql-server is running other apps such as Exchange then the Windows Swapfile will play a more important part of the system (or even IIS on the same machine).
0
 

Author Comment

by:ColumA
ID: 10812766
SQLserver is in the package of MS Small Business Server. I run the whole package on C:, while I have D: with 32GB free space. I guess, C: & D: are on one physical disk. All the company & user shared files are on E:. I have now created the SQL-DB on E: as well. Shall I put tempdb on D:. Does this solve all issues? On this occasion, I would like to thank both of you and grant 50 points for each.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10812828
D -- yes, it solves most of the issues (if you are not using D for anything major).

I would recomend looking infrastructure (and investing) if perf becomes an issue on this system.

Sharing Exchange/SQL Server/IIS/File Services on the same box will give you the biggest perf problem and where tempdb is academic in this scenario.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10812845
On this occasion, I would like to thank both of you and grant 50 points for each. -- Just post a seperate Q, with a link in this one so we can find the points and mark for one or other of us (It seems to be the most commonly way of distributing points to multiple parties).
0
 

Author Comment

by:ColumA
ID: 10812913
Sharing Exchange/SQL Server/IIS/File Services on the same box will give you the biggest perf problem

I think this can't be splitted. Has been delivered this way. Just invested last yr in a whole new infrastructure.

Summary:
Put tempdb on D: (there are only some driver-files) & put the Database on E:

Does this solve most of the issue at start?
0
 
LVL 1

Expert Comment

by:hot2use
ID: 10812963
Just my 2 cents:

Our tempdb grew to 50 GB in 2 days ! ! !  It all depends on the type of queries that you are running. A 'SELECT xyz FROM a ORDER BY xyz' will be dumped in the tempdb. A 'SELECT INTO...' is always dumped into the TEMPDB. There are certain queries and DML that automatically have the TEMPDB growing. If you have multiple cursors and users doing the same queries, then it can take ages before the TEMPDB can be shrunk again. This is because the tablespace needed by the new queries and or DML is always added to the end of the datafile for the TEMPDB.

One of the worst is if you use a 'CREATE TABLE #TEMPTABLE ... ' because this creates a table
 '#temptable________ [truncated for readability] ____________000000007FE5'
of sorts in the TEMPDB. And if you use this temporary table to store MegaBytes of data and this occurs for every user, then chances are your TEMPDB could explode as it did with our application.

You can use a query 'SELECT name FROM sysobjects WHERE name LIKE '#TEMPTABLE%' to find these tables in the TEMPDB.

e.g.
First temp table created because of 'CREATE TABLE #TEMPTABLE...' in a TRIGGER or SPROC.
NAME: #temptable__________ [truncated for readability] ___________000000007FE5

Second table created, because new user is doing the same.
NAME: #temptable__________ [truncated for readability] ___________000000007FE6

First table would no longer be used but is not automatically deleted (truncated) because it is not at the end of the file. This can happen by design and there's not much you can do.

My Solution:
Have a Job that gets triggered when the TEMPDB is nearly full and in that job execute the DBCC SHRINKDB and DBCC SHRINKFILE commands for the TEMPDB. You can also set this job to run automatically every day at 6am. This way you'll (nearly) always have a small TEMPDB and you won't have to be restarting the service every day.

Cheers hot2use
0
 
LVL 1

Expert Comment

by:hot2use
ID: 10812989
Sorry: And move your TEMPB from the C: drive to a RAID 1 array as suggested by danblake
0
 
LVL 13

Expert Comment

by:danblake
ID: 10813079
@hot2use --

My Solution:
Have a Job that gets triggered when the TEMPDB is nearly full and in that job execute the DBCC SHRINKDB and DBCC SHRINKFILE commands for the TEMPDB. You can also set this job to run automatically every day at 6am. This way you'll (nearly) always have a small TEMPDB and you won't have to be restarting the service every day.

Doesn't this result in a lot of disk shrinkage/autogrows which ultimatly slow down the system ?
Prehaps you should find a big enough partition for tempdb...
0
 
LVL 34

Expert Comment

by:arbert
ID: 10813138
"SQLserver is in the package of MS Small Business Server"

The biggest problem you're going to find with this is not enough Memory  (make sure you put a cap on SQL Server).  Also, if you happen to be running Exchange out of the box, make sure you get the exchange store on different disks from SQL...
0
 

Author Comment

by:ColumA
ID: 10813168
I just talked to the company that have installed the hardware. We do have RAID1 (2*60GB-HD) for the OS and a SCSI-Raid5 (3*36GB) for the data. So I put tempdb on D: and the data at E: and will take care that I do not create to much temp-tables. I would be happy if I stay already at this stage where I can think about creating tables :-)
0
 
LVL 1

Expert Comment

by:hot2use
ID: 10813463
@ ColumA

It isn't just creating tables, but creating temporary tables (hence the # sign). This means the tables are only temporarily available for the user / cursor. This can happen after the database has been designed. These temporary tables can be created in TRIGGERS, SPROCS, etc. It's something you have to research when you notice that the TEMPDB is exploding.

@ danblake
You don't have to shrink to zero. You can shrink to 2GB and 50% free tablespace for example :-)
0
 

Author Comment

by:ColumA
ID: 10813581
Thx for this point hot2use. If nobody insist anymore that tempdb on D: and data on E: won't be a good solution, I will award the points for Hilaire and to danblake (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20952334.html). Otherwise let me know and I will post a further question with 50points.

Thanks to everbody
ColumA
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question