Move MSDB and TempDB Databases

I noticed that my MSDB database for my SQL 2005 server was getting quite large; 2.8GB!  Unfortunately the person that installed this server placed the system databases on the system partition; C:\

I need to get them off that partition and place them on a secondary partition; in this case D:\ drive.



Moving the msdb database

If you want to know where your MSDB and TEMPDB databases are located you can use sp_helpfile


use msdb
    go
    sp_helpfile
    go

use tempdb
    go
    sp_helpfile
    go


To complicate matters for me this is a SQL Mirrored primary server.  Therefore I have to first turn off the Witness server so that it will not fail-over to the Mirror server during the process.

• Turn off Witness server.
• In SQL Server Configuration Manager stop the SQL Server Service.
• In the properties of the SQL Server service add the following to the Startup Parameters: -T3608
• (Do not add the -m and -c switches you see mentioned in the Microsoft TechNet Articles)

• Start the SQL Server Service.  Go into SSMS drop the 'msdb' with the following.  

          use master
          go
          sp_detach_db 'msdb'
          go

• Notice that all SQL services are shut off except for the SQL Server service and Full Text Search service.

• Go back into SQL Server Configuration Manager and remove the Startup Parameters -T3608.  Restart the SQL Server Services.

• Reattach the MSDB databases from their new location. 

• The MS Technet Article has some addtional steps if necessary. 

After you move the msdb database, you may receive the following error message:

Error 229: EXECUTE permission denied on object 'ObjectName', database 'master', owner 'dbo'.

This problem occurs because the ownership chain is broken. The database owners for the msdb database and for the master database are not the same. In this case, the ownership of the msdb database was changed. To work around this problem, run the following Transact-SQL statements. You can do this by using the Osql.exe command prompt utility (SQL Server 7.0 and SQL Server 2000) or the Sqlcmd.exe command prompt utility (SQL Server 2005):

USE MSDB

Go

EXEC sp_changedbowner 'sa'

Go

 

Moving the tempdb database

• Use the ALTER DATABASE statement, specifying the logical file name as follows:

use master
go
Alter database tempdb modify file (name = tempdev, filename ='D:\SQLData\TempDB\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename ='D:\SQLData\TempDB\templog.ldf')
go

You should receive the following messages that confirm the change:

Message 1

File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.

Message 2

File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.

      Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.

      Stop and then restart SQL Server.