TEMPDB data/log file location

Aug 8, 2012 at 8:57 PM

I see that by default the installer puts the tempdb files Root Install Directory.

Is this your typical configuration?

Over the years when I attempted to get the OS, datafiles, logfiles and tempdb on separate physical arrays the response from the sysadmin group ranged from laughter to outright refusal. (<scowl> "No, you get one RAID 5 array."

In the past I've either left it on the drive with the Root install or moved the tempdb data/log files to the respective user DB data/log directory.

We have now virtualized all of our SQL servers and learned very quickly that you need to treat the LUNs as though they were normal drives/arrays. ( If we added two LUNs from the same VM drive pool it had the same effect as if they were two partitions sitting on the same physical drive.)  This now puts me in the position where the sysadmin's don't scream if I say I want these multiple drives mapped to different drive pools.  (They don't care since it's not physical disks.)  So,  I will be able to see if having the tempdb on a separate drive will make any noticeable difference.

I want to change the location of the tempdb data and log files in the SPADE install and had some questions...

Could I just add it to the template override setting in the Run-Install.config file?

.
.
.
<SqlVersions>
    <TemplateOverrides>
          <Setting Name="SQLTEMPDBDIR" Value="Physical Path" />
          <Setting Name="SQLTEMPDBLOGDIR" Value="Physical Path" />
   </TemplateOverrides>
  </Version>
 </SqlVersions>
</InstallerConfig>

Or do I need to add this to the configuration_<ver>.ini file:

SQLTEMPDBDIR="" 
SQLTEMPDBLOGDIR=""

If the configuration_<ver>.ini entries are needed what should their values be?
   Blank?
   <Root Directory>\Data?
   $SystemDataDir?
  

Aug 8, 2012 at 10:21 PM

Or maybe just adding this to the Start-SqlSpade.ps1 file in the #Optional values section:

$overrides.Add("SQLTEMPDBDIR", 'T:\mssql\data')
$overrides.Add("SQLTEMPDBLOGDIR", 'T:\mssql\data')

Coordinator
Aug 9, 2012 at 12:59 AM

Thank you for catching that.  For versions 2005-2008R2 the tempdb files are placed in the same location as the other system databases, and I neglected to make the adjustment for 2012 when they enabled the option to assign tempdb seperately.

My intended way of handling this is to either add the elements to the Run-Install.config file (if this is your standard) or adding the options to the $overrides hashtable (if this is a "one-off" build).  The configuration ini files should not be updated as they are intended to be generic, and can be easily overridden using the methods we just discussed.

As an answer to your initial question, our standard is to locate the tempdb file with the rest of the system databases.  The system databases generally don't have sufficient activity to interfere with tempdb. 

I hope this answers your questions, if not I'd be happy to discuss this with you further.

Michael

Coordinator
Aug 24, 2016 at 1:07 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.