Post reply

Moving tempdb

  • March 28, 2008 at 5:10 am

    #180436

    Hi all,

    I was wondering if someone could help me, i'm trying to move the tempdb on a server in SS2K5. I have gone into the config manager and edited the startup parameters, i have added in the -c -m -T3608. The start up parameters now look like this.

    "-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf; -c -m -T3608"

    I've stopped and restarted the service and tried to dettach the tempdb, but it wont let me, it keeps saying "System databases master, model, msdb, and tempdb cannot be detached.". Which appearss to be like the changes to the startup parameters have had no effect.

    What am i doing wrong here?

    Thanks,

    Nic

  • March 28, 2008 at 5:18 am

    #795082

    Sorry, i realise i can move tempdb by using the alter database code, it's the msdb i want to move using the above method.

  • March 28, 2008 at 6:00 am

    #795100

    You move the systemdatabases except the master and resource databases wit the following statements:

    Copy Code

    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path/os_file_name' )

    Stop the instance of SQL Server or shut down the system to perform maintenance. For more information, see Stopping Services.

    Move the file or files to the new location.

    Restart the instance of SQL Server or the server. For more information, see Starting and Restarting Services.

    Verify the file change by running the following query.

    SELECT name, physical_name AS CurrentLocation, state_desc

    FROM sys.master_files

    WHERE database_id = DB_ID(N' ');

    If the msdb database is moved and the instance of SQL Server is configured for Database Mail, complete these additional steps.

    Verify that Service Broker is enabled for the msdb database by running the following query.

    SELECT is_broker_enabled

    FROM sys.databases

    WHERE name = N'msdb';For more information about enabling Service Broker, see ALTER DATABASE (Transact-SQL).

    Verify that Database Mail is working by sending a test mail. For more information, see Troubleshooting Database Mail.

    You can read more on the following link:

    http://technet.microsoft.com/en-us/library/ms345408.aspx

  • March 28, 2008 at 6:06 am

    #795102

    Thanks for that, that makes sense, however why do does the Microsoft article http://support.microsoft.com/kb/224071 do it using a detach and reattach, is this just another method?

  • March 28, 2008 at 9:05 am

    #795252

    To move tempdb data files, you don't need to physically move the data files. Here is a straight procedure for the task:

    1. Identify a suitable time to do it.

    2. Run below statement to change the location of a data file one by one.

    ALTER DATABASE tempdb

    MODIFY FILE (NAME = tempdev, FILENAME = ' \tempdb.mdf')

    3. Restart the SQL Server Service.

    4. Verify the tempdb data file(s) in the new location.

  • March 28, 2008 at 9:19 am

    #795261

    Refer this link, http://sql-articles.com/index.php?page=articles/msysdb.htm for moving system databases in Sql Server 2005. It also has screenshots which will certainly help you. For moving tempdb you need to use alter database and then restart Sql Server for the new path to take effect. For model and msdb you need to detach the db move it to new location and then attach it. For master you need to stop Sql Services and change the path in startup parameter to the new path of master database files !

    [font="Verdana"]- Deepak[/font]

  • March 28, 2008 at 9:23 am

    #795266

    Thanks Deepak, and everyone else who posted.

    So I am right in saying for msdb and model i can either move via an alter statement or by detaching the database.

    Thanks again, i'm off to go and try things out on my sand pit machine.

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

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