Detach TEMPDB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Detach TEMPDB

Author

Topic

Jheds
Starting Member

17 Posts

Posted - 2006-12-27 : 08:56:34

Hi friends,
the tempdb cannot be detached . so how will we detach this database?
even when trying the sp_detach_db script it gives me an error that cant detach master and tempdb.
what can we do to detach the tempdb?

what will be the steps?

thanks & regards
Jheds

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-27 : 09:08:41

why are you trying to detach tempdb?

________________________________________________
I am a man, I can change, if I have to, I guess.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-27 : 09:15:05

System databases can't be detached. I'm assuming you're trying to relocate the physical files.

http://support.microsoft.com/kb/224071

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2006-12-27 : 09:17:29

Hi,

At times u may need to shift Tempdb to seperate drive or RAID.

U must refer this link : http://support.microsoft.com/default.aspx/kb/224071

this is how.......

use tempdb
go
sp_helpfile -- TO get the current file path
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')

Madhu

Jheds
Starting Member

17 Posts

Posted - 2006-12-27 : 10:10:45

Thank you so much Madhu & Joe for your valuable time and efforts.
This worked perfectly.

Take Care

Jheds

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources