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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
chigrikCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.