September 19, 2008 at 7:37 am
Hello,
I have read that it is best to move tempdb to a separate physical drive from the user data files. In the current configuration for the server in question, though, I have only a D drive (main data) and an e drive (a backup drive) with somewhat limited time for further drive layout changes. Both drives are using the SAN, so I'm not even sure how to confirm that D and E are actually separate physical drives. But here is my scenario:
Is it better to move the tempdb to the E drive even though that drive is identified as a drive for backup files? Or does the possible logistical risk of having an active db file on a drive slated for backups outweigh the performance gain from moving the tempdb database there? (Someone might unwittingly think the tempdb is just a backup and may delete it accidentally.)
I am leaning toward not moving the tempdb to the backup drive. If I do leave it on the D drive with the other db files, can I gain some performance by creating multiple data files for tempdb, one for each CPU? If so, that might be the best compromise available to me.
Thanks for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 19, 2008 at 7:44 am
what you are seeing exposed as "drives" are not actually different "drives" on the SAN; you may want to talk to your storage guy.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 19, 2008 at 2:54 pm
Best choice would be for the net admin to create a new partition on the SAN, and put all TempDB files there.
Next best choice...that's tough. If your performance is OK then leave it alone.
You can try a "this week on D / next week on E" and gauge performance metrics.
At the very least, size tempdb to be large enough so it doesn't expand often. fragmentation will bring even a fast server to its knees.
As for # of files, 1 Data file per processor is a good idea. (maximize parallel processing) 1 Log file total should be ok.
September 19, 2008 at 3:09 pm
Thanks so much for the advice.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 19, 2008 at 3:26 pm
webrunner (9/19/2008)
(Someone might unwittingly think the tempdb is just a backup and may delete it accidentally.)
If SQL's running, the files can't be deleted as SQL will have them open exclusively. If SQL's not running and the files are deleted, they should get recreated automatically when the server restarts
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2008 at 3:32 pm
GilaMonster (9/19/2008)
webrunner (9/19/2008)
(Someone might unwittingly think the tempdb is just a backup and may delete it accidentally.)If SQL's running, the files can't be deleted as SQL will have them open exclusively. If SQL's not running and the files are deleted, they should get recreated automatically when the server restarts
Thanks!
Does SQL retain the size and multiple file (e.g., 1 per CPU) settings when it recreates tempdb on startup, or is there a script that needs to be run to set these properties every time the server is restarted?
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 19, 2008 at 4:52 pm
it retains the settings from those you set via the alter database command
---------------------------------------------------------------------
September 22, 2008 at 7:14 am
george sibbald (9/19/2008)
it retains the settings from those you set via the alter database command
Excellent, thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 17, 2009 at 8:52 am
Hi
I am istalling SQL and I have separet hard drives for the Logs
and Data and the Temp DB.
I am installing SQL 2005 standard edition, how ever during the istallation process I did not get the path selection.
so I pressume every thing installed on the D:\ drive where I pointed it to.
But I want to saparate it to the respected drives.
Durring the installation there is no step where it asked me where do I need to locate these databases.
Please if you can advise how to move all Databases to their dedicated drives.
Thanks
Regards
March 17, 2009 at 9:04 am
Tebeho, forum etiquette is you should start your own thread when you have a somewhat unrelated question.
However, if you have not added your user databases yet or gone live you have the option of an uninstall\reinstall.
to seperate out your files and place your system databases where you want them:
in the install process where the features you want to install is listed select the 'advanced' option. select database engine and you can specify where you want to place the executables (via the browse button). then expand the database engine and choose data files and you can choose where you want to place the system databases.
If you want to move your system databases:
http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx
---------------------------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply