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.

 

Bringing a Witness Into an existing SQL Mirror Pair

I have a mirrored SQL 2005 server environment configured for high availability, fail-over mode. Here are the versions of SQL running on them:

SQL Versions:
Primary:  SQL 2005 Standard
Mirror:  SQL 2005 Standard
Witness:  SQL 2005 Express

For reasons not related to SQL I had to rebuild the Witness server.  When I attempted to bring the Witness server back into the mirror via the SQL mirror management tool I got the following error message:



The ALTER DATABASE command could not be sent to the remote server instance 'TCP://SQLWitness.domain.local:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (Microsoft SQL Server, Error: 1456)

I looked at all of the default settings and everything looked good.  I could telnet to port 5022 on both the Primary and the Mirror server but NOT the Witness server. This is an issue.  Port 5022 should be available on the Witness for this to work.  (Yes, I verified that there was no firewall blockage).


When I ran "SELECT name, port FROM sys.tcp_endpoints" against the Witness server I received the following.  Notice that there is no mention of it's role as a witness.  This is a problem.

Resolution:

I ran the following against the Witness server to recreate the endpoint:

Use Master
GO
CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 5022 )
    FOR DATABASE_MIRRORING (ROLE=WITNESS);
GO


Now when I run "SELECT name, port FROM sys.tcp_endpoints" I see the following:




Now this time I went into the SQL Server Mirror Manager I was able to add the Witness server with no problems.



Details:
http://msdn.microsoft.com/en-us/library/ms190456%28v=sql.90%29.aspx