Solved

Moving tempdb to another disk

Posted on 2004-04-13
23
1,335 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
  • 8
  • 8
  • 3
  • +2
23 Comments
 
LVL 13

Expert Comment

by:danblake
Comment Utility
0
 
LVL 13

Expert Comment

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

Expert Comment

by:Hilaire
Comment Utility
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
 

Author Comment

by:ColumA
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry Hilaire. Thought the last comment was from Dan. So ignore my performance-question.
0
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 13

Expert Comment

by:danblake
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
"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
Comment Utility
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
Comment Utility
@ 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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now