Can I move tempdb?

Is there a way to relocate tempdb to another drive?Read this article:
http://support.microsoft.com/defaul...kb;en-us;224071
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"aerosnoop" <anonymous@.somewhere.net> wrote in message
news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
Is there a way to relocate tempdb to another drive?|||You can move tempdb files with ALTER DATABASE ... MODIFY FILE, specifying
the desired file locations. Delete the old files after restarting SQL
Server. For example:
ALTER DATABASE tempdb
MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
ALTER DATABASE tempdb
MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
Hope this helps.
Dan Guzman
SQL Server MVP
"aerosnoop" <anonymous@.somewhere.net> wrote in message
news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Is there a way to relocate tempdb to another drive?|||There is an error in the script I posted. The logical name of the second
ALTER DATABASE should be 'templog'.
ALTER DATABASE tempdb
MODIFY FILE(NAME='templog', FILENAME='G:\LogFiles\templog.ldf')
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can move tempdb files with ALTER DATABASE ... MODIFY FILE, specifying
> the desired file locations. Delete the old files after restarting SQL
> Server. For example:
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aerosnoop" <anonymous@.somewhere.net> wrote in message
> news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
>|||Thankyou.

> Read this article:
> http://support.microsoft.com/defaul...kb;en-us;224071|||Does the location of the model db also need to be altered for the changes to
remain after restarting SQL? Or perhaps a job configured to run at startup
so that other create database statements aren't affected would be more
appropriate if the poster wants the change persisted. I'm actually not sure
whether or not the change would be lost after restart - perhaps SQL has some
way of remembering location of tempdb files?
Regards,
Greg Linwood
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can move tempdb files with ALTER DATABASE ... MODIFY FILE, specifying
> the desired file locations. Delete the old files after restarting SQL
> Server. For example:
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aerosnoop" <anonymous@.somewhere.net> wrote in message
> news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
>|||The physical file names are properties of the database that are not derived
from the model database. The location of the primary data file for each
database are stored in master.dbo.sysdatabases. The primary datafile for
each database contains the system tables, amongst them sysfiles, which
contains the locations of the log file(s) and the secondary data file(s). So
that's how the location of the tempdb files is remembered by SQL Server.
Jacco Schalkwijk
SQL Server MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23z%23r06WdEHA.2408@.tk2msftngp13.phx.gbl...
> Does the location of the model db also need to be altered for the changes
> to
> remain after restarting SQL? Or perhaps a job configured to run at
> startup
> so that other create database statements aren't affected would be more
> appropriate if the poster wants the change persisted. I'm actually not
> sure
> whether or not the change would be lost after restart - perhaps SQL has
> some
> way of remembering location of tempdb files?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
>|||Tempdb file name changes take affect after SQL Server is restarted and are
permanent. The file locations are stored in the master database sysaltfiles
table and the primary file path is also stored in sysdatabases. If only
tempdb needs to be moved, no other changes need to be made. The KB article
reference posted by details procedures for moving other database files.
Model isn't used to determine database file locations. The default location
for new database data and log files is stored in the registry and the EM GUI
can be used to change these. However, tempdb is a special case because the
file locations are known at startup and those are used if new tempdb files
need to be created.
Hope this helps.
Dan Guzman
SQL Server MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23z%23r06WdEHA.2408@.tk2msftngp13.phx.gbl...
> Does the location of the model db also need to be altered for the changes
to
> remain after restarting SQL? Or perhaps a job configured to run at
startup
> so that other create database statements aren't affected would be more
> appropriate if the poster wants the change persisted. I'm actually not
sure
> whether or not the change would be lost after restart - perhaps SQL has
some
> way of remembering location of tempdb files?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
specifying[vbcol=seagreen]
>|||ahh, thx!
Regards,
Greg Linwood
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OUFytLXdEHA.1048@.tk2msftngp13.phx.gbl...
> Tempdb file name changes take affect after SQL Server is restarted and are
> permanent. The file locations are stored in the master database
sysaltfiles
> table and the primary file path is also stored in sysdatabases. If only
> tempdb needs to be moved, no other changes need to be made. The KB
article
> reference posted by details procedures for moving other database files.
> Model isn't used to determine database file locations. The default
location
> for new database data and log files is stored in the registry and the EM
GUI
> can be used to change these. However, tempdb is a special case because
the
> file locations are known at startup and those are used if new tempdb files
> need to be created.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:%23z%23r06WdEHA.2408@.tk2msftngp13.phx.gbl...
changes[vbcol=seagreen]
> to
> startup
> sure
> some
> specifying
>