September 15, 2015 at 1:03 pm
Does anyone know of any documentation that supports the placement of Tempdb files on the root of a drive, i.e T: instead of T:\tempdb. I am positive this is not a best practice, but when challenged could not find any documentation that would support that view.
September 15, 2015 at 1:19 pm
I misread your question as moving off of the Os drive, insteadof what you asked;;;
so should tempdb.mdf be T:\tempdb.mdf vs T:\SQLData\tempdb.mdf ?
i'd say folders make things more organized, so from an administrative perspective, id say place in folders.
i cannot think of any reason performance wise it would not work either way.
here's my first link i found from what i consider a trusted resource:
http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-2-after-the-install/
BEST PRACTICE: MOVE TEMPDB TO ITS OWN DRIVE.By default, the TempDB files are put on the same drive as the SQL Server binaries. Even if the user chooses a custom install, TempDB still goes on the same drive as the other data files, and that’s not a good idea either. Instead, the TempDB data files should be on their own dedicated drive.
Fix this by first moving TempDB to its own drive. In this example, I put the data file on the T drive and the log file on the L drive. (Be aware that the directory paths must already exist.)
Lowell
September 15, 2015 at 1:24 pm
Your post does not answer the issue. The question is not to put them on their own drive but rather should the MDF and LDF reside at the root of the drive rather than in a sub folder.
September 15, 2015 at 1:57 pm
Windows protects the roots of drives more than folders, so you're more likely to have permissions problem (which with TempDB will result in the instance not starting). Put them in folders.
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 15, 2015 at 2:56 pm
I ran into something like this as well a while back. The SAN vendor, Netapp did not support .mdf or .ldf files on the root of a mounted drive. I recall it was somehow related to their snapshot manager.
September 16, 2015 at 5:18 am
manguml (9/15/2015)
Does anyone know of any documentation that supports the placement of Tempdb files on the root of a drive, i.e T: instead of T:\tempdb. I am positive this is not a best practice, but when challenged could not find any documentation that would support that view.
Are you using mounted volumes?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 16, 2015 at 6:15 am
I don't know of any documentation on how you build your folder structure, but purely for organization and permission reasons, I would put them in folders.
October 30, 2015 at 12:11 pm
It's better to put that in a standard folder. Make folder structure same across all your instances for better administration.
December 29, 2022 at 6:51 pm
Comment Updated
My original comment had said that the one exception to the "don't use the root" rule was the ephemeral D drive for Azure VMs.
However, having experimented, you shouldn't put SQL on the root of the D drive, as that does have permissions issues / interferes with the pagefile; see attached. Instead, to make use of the D drive you do need a dedicated subfolder, with accompanying script to recreate that folder on startup / before SQL is kicked off. Such a script can be found here: https://blog.idera.com/database-tools/configuring-tempdb-on-azure-iaas-for-sql-server.
Note: the attached is for SQL 2012; I've not tested with other versions / this was required for a legacy app being migrated to Azure.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply