Data file has been dropped but sql server wont let go

Question

Data file has been dropped but sql server wont let go

bopeavy

SSCertifiable

Points: 5869
More actions
June 8, 2011 at 6:46 am

#241830

Hey here is a question for you is there a way to have sql server let go of a data file thats been dropped WITHOUT RESTARTING THE SEVRER?
:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'm not sure what you mean by "let go of".

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

bopeavy SSCertifiable Points: 5869 More actions · Answer 2

I have a data file to a database that has been dropped. But it still excist on the file system and can not be deleted because it is still open by sql server?

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 3

if sqlserver is running and the database is open, you cannot delete the file on the operating system.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

bopeavy SSCertifiable Points: 5869 More actions · Answer 4

There no other way a back door into sql sevrer to have the system let go of a particular file?

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

User database or TempDB?

Can you explain the situation you're seeing?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bopeavy SSCertifiable Points: 5869 More actions · Answer 6

Yes, the server had to many tempdb files some where dropped etc.. etc.. then my space was getting a little low and I looked the temp files are still there and being used but any queries I do say they do not excist. There has to be away to eliminate the files without having to restart the server.

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

what command did you use to drop the files?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

That's very likely to require a restart of the SQL Service (not a server restart)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bopeavy SSCertifiable Points: 5869 More actions · Answer 9

How bout this:

sp_attach_single_file_db @dbname= 'databasename', @physname= 'C:\Users\Documents\Databases\databasefile.mdf'

to reattach the file then empty it then detach the file and delete?

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

You can't attach or detach TempDB.

Additionally, that attach is to attach the primary file of a database. You can't just attach files to existing databases. Detach detaches the entire database, not a single file (hence why it can'ty be used on TempDB)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bopeavy SSCertifiable Points: 5869 More actions · Answer 11

Yes, but I could do it as a user db, but it does not matter even the OS says that it does not excist yet I see it in the file folder and the size of it. It's almost like it is a ghost file that I can not get rid of.

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Perry Whittle SSC Guru Points: 234013 More actions · Answer 12

Perry Whittle (6/8/2011)
what command did you use to drop the files?

:Whistling:

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

bopeavy SSCertifiable Points: 5869 More actions · Answer 13

I used the interface it was easier at the moment GUI>pickfile>removeButton

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Perry Whittle SSC Guru Points: 234013 More actions · Answer 14

Although you have removed the file it cannot be truly dropped until you restart the database, as this is tempdb you must restart the SQL server services (no reboot required)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉