Re: Can I move tempdb?

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 08:49:52 -0500

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...
> > 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?
> >
> >
>
>

Relevant Pages

  • Re: Checkpointing Not Happening in Simple Recovery Model
    ... You cannot set the recovery model in tempdb. ... Columnist, SQL Server Professional ... We would then have to issue an alter database ...
    (microsoft.public.sqlserver.server)
  • Re: db library error ?
    ... Tibor Karaszi, SQL Server MVP ... > the following error message apeared: ... > The log file for database 'tempdb' is full. ...
    (microsoft.public.sqlserver.server)
  • Re: Error Granting DB / Role Access
    ... Jasper Smith (SQL Server MVP) ... > the database or object owner (within tempdb) even though they have DBO ...
    (microsoft.public.sqlserver.security)
  • Re: Computed columns in temp tables
    ... create the function in tempdb whenever SQL Server is started. ... will not just disappear from tempdb once you have created it, ... >> Now using another database u can call this function during temp table ... >>> How can I create a function in tempdb while inside a stored procedure? ...
    (microsoft.public.sqlserver.datamining)
  • Re: Computed columns in temp tables
    ... create the function in tempdb whenever SQL Server is started. ... will not just disappear from tempdb once you have created it, ... >> Now using another database u can call this function during temp table ... >>> How can I create a function in tempdb while inside a stored procedure? ...
    (microsoft.public.sqlserver.server)