Setting Environment variables
Now you may well ask what on earth windows server environment variables have to do with SQL Server, and it's a fair question and only relevant in certain circumstances.
I'm currently running a series of replay profiler traces on a server, I need these to run in 24 hour periods, I don't want to run them across the network and I have a separate lun especially for such purposes.
The problem is that for one reason or another the C: drive partition is very small, this means that the temporary files created by profiler, not the actual trace files I'm writing to disk, become very large and by default they will get written to the C: drive because that's where all the environment variables are initially set. I personally find that the default behaviour of so many applications which write to your profile a real pain in the backside both on servers and workstations, but that's another matter.
There are two sets of variables which have to be changed to avoid the issue with temp files when running prolonged profiler traces and other processes which generate temporary files.
The first set which need to be changed are the user variables, these are local to the profile and have to be edited again if a profile is deleted.
( I suspect changing this for the default profile will achieve this but I don't know how you achieve this )
There is also a second set which must be changed and these are the system variables for TEMP and TMP.
Now you could do this through the GUI but ad-hoc changes on a production server this way does not provide a repeatable process, what we need is a script!
Thankfully microsoft have provided us with SETX
To change a users variables the command line SETX command should be used
e.g. SETX TEMP T:\Temp and SETX TMP T:\Temp
To change the system variables the following keys need to be changed
HKEY_LOCAL_MACINE/System/ControlSet001/SessionManager/Environment/
Keys TEMP and TMP set to the required location.
This affects all users and only needs to be done once
This can be achieved by appending a /m to the SETX command. NB. Make sure the location folder for Temp has been created first.
NB. I don't believe SETX is available by default in workstation o/s
To return the Environment Variables and their values type SET in a cmd session, below is a typical output from windows server 2008
Microsoft Windows [Version 6.0.6001]
Copyright (c) 2006 Microsoft Corporation. All rights reserved.
C:\Users\GrumpyOldDBA>cd\
C:\>set
ALLUSERSPROFILE=C:\ProgramData
APPDATA=C:\Users\GrumpyOldDBA\AppData\Roaming
CLIENTNAME=MyWorkStation
CommonProgramFiles=C:\Program Files\Common Files
CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files
COMPUTERNAME=MITHIAN
ComSpec=C:\Windows\system32\cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Users\GrumpyOldDBA
LOCALAPPDATA=C:\Users\GrumpyOldDBA\AppData\Local
LOGONSERVER=\\DC-10
NUMBER_OF_PROCESSORS=16
OS=Windows_NT
Path=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32
\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Dell\SysMgt\RAC5;C:\Program File
s (x86)\Dell\SysMgt\oma\bin;C:\Program Files (x86)\NetIQ\AppManager\bin;D:\Progr
am Files (x86)\Microsoft SQL Server\100\Tools\Binn\;D:\Program Files\Microsoft S
QL Server\100\Tools\Binn\;D:\Program Files (x86)\Microsoft SQL Server\100\Tools\
Binn\VSShell\Common7\IDE\;D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Bi
nn\;D:\Program Files\Microsoft SQL Server\100\DTS\Binn\
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
PROCESSOR_ARCHITECTURE=AMD64
PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 15 Stepping 11, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=0f0b
ProgramData=C:\ProgramData
ProgramFiles=C:\Program Files
ProgramFiles(x86)=C:\Program Files (x86)
PROMPT=$P$G
PUBLIC=C:\Users\Public
SESSIONNAME=RDP-Tcp#0
SystemDrive=C:
SystemRoot=C:\Windows
TEMP=C:\Users\GrumpyOldDBA\AppData\Local\Temp\3
TMP=C:\Users\GrumpyOldDBA\AppData\Local\Temp\3
USERDNSDOMAIN=UK-Berkshire.GOD.LOCAL
USERDOMAIN=UK-Berkshire
USERNAME=GrumpyOldDBA
USERPROFILE=C:\Users\GrumpyOldDBA
windir=C:\Windows
C:\>