detach & move tempdb?
Hello Experts:
I incorrectly installed SQL server on my C partition instead of my data partition. Now of course when my hourly DTS's run I quickly eat up the available space on C. I'm trying to move the tempdb database over to the data partition but it gives me this error:
System databases master, model, msdb, and tempdb cannot be detached.
I'm using microsofts example to move the db:
use master
go
sp_detach_db 'tempdb'
go
So is this just impossible to do? Am I stuck flat out reinstalling SQL? Help!
I incorrectly installed SQL server on my C partition instead of my data partition. Now of course when my hourly DTS's run I quickly eat up the available space on C. I'm trying to move the tempdb database over to the data partition but it gives me this error:
System databases master, model, msdb, and tempdb cannot be detached.
I'm using microsofts example to move the db:
use master
go
sp_detach_db 'tempdb'
go
So is this just impossible to do? Am I stuck flat out reinstalling SQL? Help!
Aneesh
There is no way to detach the tempdb...
ASKER
ARGH! I can't believe I did this. The tempdb is the only one that outgrows the partition too...
Thanks.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
[ http://support.microsoft.com/default.aspx?scid=kb;en-us;224071 ]
In SQL Server 2000 and SQL Server 2005, system databases cannot be detached using the sp_detach_db stored procedure. Running sp_detach_db 'msdb' will not succeed and you will receive the following message:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the MSDB database on SQL Server 2000, follow these steps: 1. In SQL Server Enterprise Manager, right-click the server name and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add a new parameter as "-T3608" (without the quotation marks).
After you add trace flag 3608, follow these steps: 1. Stop, and then restart SQL Server.
2. Make sure that the SQL Server Agent service is not currently running.
3. Detach the msdb database as follows:use master
go
sp_detach_db 'msdb'
go
4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).
5. Remove the -T3608 trace flag from the startup parameters box in Enterprise Manager.
6. Stop and then restart SQL Server.
7. Reattach the msdb database as follows:use master
go
sp_attach_db 'msdb','E:\Mssql8\Data\msd
go
Note If you try to reattach the msdb database by starting SQL Server together with trace flag -T3608, you receive the following error:
Server: Msg 615, Level 21, State 1, Line 1
Could not find database table ID 3, name 'model'.
In SQL Server 2000 and SQL Server 2005, system databases cannot be detached using the sp_detach_db stored procedure. Running sp_detach_db 'msdb' will not succeed and you will receive the following message:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the MSDB database on SQL Server 2000, follow these steps: 1. In SQL Server Enterprise Manager, right-click the server name and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add a new parameter as "-T3608" (without the quotation marks).
After you add trace flag 3608, follow these steps: 1. Stop, and then restart SQL Server.
2. Make sure that the SQL Server Agent service is not currently running.
3. Detach the msdb database as follows:use master
go
sp_detach_db 'msdb'
go
4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).
5. Remove the -T3608 trace flag from the startup parameters box in Enterprise Manager.
6. Stop and then restart SQL Server.
7. Reattach the msdb database as follows:use master
go
sp_attach_db 'msdb','E:\Mssql8\Data\msd
go
Note If you try to reattach the msdb database by starting SQL Server together with trace flag -T3608, you receive the following error:
Server: Msg 615, Level 21, State 1, Line 1
Could not find database table ID 3, name 'model'.
ASKER
Ok trying the alter database commands.
ASKER
Yep, the alter database worked perfectly. Wish I had read that about an hour ago. :)
Thanks for your help!
Thanks for your help!
One correction here - if you are in Single user mode (-c -m -T3608) you CAN drop the tempdb database by accident - MSSQL 2005 (at least) does NOT stop you from doing so.
You need a backup of the Master database to recover from that mis-hap.
You need a backup of the Master database to recover from that mis-hap.