Post reply

Migrating TempDB

  • June 5, 2006 at 12:48 pm

    #190816

    All

    The install for SQL Server 2005 puts my tempDB into the programFiles directory on the C: Drive. 

    My C: drive is very small, and I would like to migrate the database to or split it across two disks.  

    When I go into the database properties to alter the database on to the secondary drive, I get an error that says the TempDB cannot be altered. 

    Anyone have a way to migrate the TEMP DB without going back to re-install my server?

    Eric Peteson

  • June 5, 2006 at 2:14 pm

    #641820

    Execute something like the following and then restart the service

    use

    master

    go

    Alter

    database tempdb modify file (name = tempdev, filename = 'd:\MSSQL\Data\tempdb.mdf')

    go

    Alter

    database tempdb modify file (name = templog, filename = 'd:\MSSQL\Data\templog.ldf')

    go

    Once its restarted check the location of the files and then you can delete the old ones on c:

     

    hth

    David

  • June 5, 2006 at 2:48 pm

    #641826

    Thanks for the reply.

    The above commands were executed.   The database was shutdown and restarted. 

    The problem is that the Server dosent restart with the error message: The server has started and stoped.  Trying it a second time I get the message "cannot open a connection to the SQL Server"

    I also coppied the tempdb files to the new directory.  That didnt help. 

    Note: I am using a test server, so I can restore the backup image. 

    Any other suggestions?

    Eric Peterson     

  • June 5, 2006 at 4:24 pm

    #641839

    Tempdb should be recreated each time you start the server so you should have had to move the files but you could try opening a command prompt and navigating to

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

    then sqlservr.exe -f

    which should attempt to start it in a minimal configuration

    This should allow a single connection run the following

    SELECT name, AS CurrentLocation, state_desc

    FROM sys.master_files

    WHERE database_id = DB_ID(N'tempdb');

    to check the location it expects to find the tempdb files then check that the directory does indeed exists and that the account that sqlserver is running under has enough rights to create the tempdb files in this location.

     

    hth

     

    David

  • June 6, 2006 at 8:04 am

    #642007

    Try running this after you issue your ALTER statement.. and see where SQL thinks TempDB is..

     

    use

    tempdb

    go

    sp_helpfile

    go

     

    hope this helps...

    Mark

  • June 6, 2006 at 2:05 pm

    #642129

    After I change the database file location the system returns the new location.

    then reboot.....

    and I cannot get back in to the SQL Server

    I am wondering if anyone has actually changed the location of the TempDB and this is an undocumented feature of SQL Server 2005?

     

  • June 6, 2006 at 4:39 pm

    #642156

    To be honest it sounds like a permissions problem i have changed the location of my tempdb using exactly the

    same procedure on several occasions on live and developmenet servers in both sql server 2000 & 2005

     

    The prodecure described is also in BOL

     

    David

  • June 6, 2006 at 4:42 pm

    #642160

    Thanks

     

    It is good to know that we can change the file location.   We are using a Virtual Machine for the testing, and there is always a problem or two with the configuration or permissions.   Will put it on a real machine, and test again...

    Thanks

    Eric Peterson

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply