ColumA
asked on
Moving tempdb to another disk
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
What is the easiest way to move?
Thx
ColumA
All mechanisms are relativly easy, it depends what you are more familair / comfortable with : EM / isql/...
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)
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)
ASKER
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
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
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).
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).
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Sorry Hilaire. Thought the last comment was from Dan. So ignore my performance-question.
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
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
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.
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.
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).
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).
ASKER
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.
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.
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.
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).
ASKER
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?
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?
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
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
Sorry: And move your TEMPB from the C: drive to a RAID 1 array as suggested by danblake
@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...
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...
"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...
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...
ASKER
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 :-)
@ 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 :-)
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 :-)
ASKER
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 (https://www.experts-exchange.com/questions/20952334/Points-for-danblake.html). Otherwise let me know and I will post a further question with 50points.
Thanks to everbody
ColumA
Thanks to everbody
ColumA
http://support.microsoft.com/default.aspx?scid=kb;en-us;187824
(SQL 7 +):
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k