How to delete a 'system' device


I was getting this error about '...tempdb ... system segment is full...dump transaction..'
so i made a new device calling it tempdata and assigned Tempdb to it.  I did not pay attention
to the fact that it was going on the C drive where space is limited rather than the D drive where
everything else is.  SQL Server will not let me drop this device because it says Tempdb is a system database
Right now i have two devices (tempdev and tempdata) both for Tempdb.  I need only one, tempdev, and i
need to drop tempdata.   How can i recover that space on the C drive without reinstalling SQL Server?
xoxomosAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
chigrikConnect With a Mentor Commented:
I did it several times, so I send as the answer.
You must have enough memory allocated to SQL Server
(shrink tempdb database if you have not enough memory).

1. Create new device with size of 30-40Mb (tempdev for example)
2. Uncheck "Default device" option for master device.
3. Set "Default device" option for tempdev device.
4. From Enterprise manager (or sp_configure) set tempdb to be in RAM (set value to 1)
5. Stop and restart MSSQLServer service.
6. From Enterprise manager (or sp_configure) set tempdb to not be in RAM (set value to 0)
7. Stop and restart MSSQLServer service.
8. Expand tempdb database to the full size of tempdev device.

Please, be attentive, otherwise you should read the following:
"I've put tempdb in ram and now I can't restart my SQL Server."
http://www.ntfaq.com/ntfaq/sql77.html#sql77
0
 
Gustavo Perez BuenrostroCommented:
Have you tried using REMOVE FILE argument of ALTER DATABASE statement?
0
 
xoxomosAuthor Commented:
Nope but i'll try to find out how this very instant
0
 
xoxomosAuthor Commented:
Is that REMOVE FILE argument available in 6.5??.  I don't see it in Books Online
0
 
xoxomosAuthor Commented:
O.K. I believe you, but actually i got lucky.  It let me just shrink the tempdb down to 2mb.  After i shrunk it, it let me just drop the second device from Enterprise manager without complaining about it being a system database.
0
All Courses

From novice to tech pro — start learning today.