Data file has been dropped but sql server wont let go

Question

Data file has been dropped but sql server wont let go

Viewing 11 posts - 16 through 25 (of 25 total)

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

bopeavy SSCertifiable Points: 5869 More actions · Answer 1

Isn't that truely a disadvantage that you can not drop a file without restarting sql server? i really do not care what db it goes to, if it has more than one and you should be able to drop delete or what ever without having to restart any service. Microsoft has a good habit about changing things why not changes to benefit the users?

:cool:

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

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

This is specific and unique to tempDB and likely has to do with what uses tempDB and how it's used.

To determine that a TempDB data file can be dropped during normal operation, SQL would have to ensure that no one is using that file. The only practical way to ensure that no one is using a tempDB file would be to completely quiesce the entire instance. That means no running transactions at all, no running statements, no queries whatsoever. Essentially it would have to stop and then block every single user connection on the server.

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

Perry Whittle SSC Guru Points: 234024 More actions · Answer 3

Perry Whittle (6/8/2011)
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)

ok, calm down. My guess is the file wasnt empty, so you will need to restart the SQL Server service

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

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

bopeavy SSCertifiable Points: 5869 More actions · Answer 4

GilaMonster
This is specific and unique to tempDB and likely has to do with what uses tempDB and how it's used.
To determine that a TempDB data file can be dropped during normal operation, SQL would have to ensure that no one is using that file. The only practical way to ensure that no one is using a tempDB file would be to completely quiesce the entire instance. That means no running transactions at all, no running statements, no queries whatsoever. Essentially it would have to stop and then block every single user connection on the server.

Yes, I agree, but you still have to admit this is a pain to DBA's!

:cool:

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

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

Seriously, how often do you have to remove tempDB files?

For me (admittedly I'm not a production DBA, though I work a lot with them) it's 0 times in 6 years.

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

GilaMonster
Seriously, how often do you have to remove tempDB files?
For me (admittedly I'm not a production DBA, though I work a lot with them) it's 0 times in 6 years.

That would depend on who your going behind correct? In the moment not many, but I am dealing with a production environment that does not allow for any or much down time. And this Kind of thing is a pain when I have to get a time slot to fix someone's mistake!

:cool:

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

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

Is your environment clustered? Mirrored? If you have minimal downtime allowance it surely has one of those.

If so, failover. That's maybe a minute or two at most downtime.

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

Perry Whittle SSC Guru Points: 234024 More actions · Answer 8

GilaMonster (6/8/2011)
it's 0 times in 6 years.

wow, as often as that Gail 🙂

bopeavy (6/8/2011)
And this Kind of thing is a pain when I have to get a time slot to fix someone's mistake!

nobody's perfect, it takes around 20-30 seconds at most in my experience to restart the SQL Server service. Most applications possibly wouldnt even notice the service restart if you were strategic in picking your timings. Do you not have maintenance windows?

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

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

bopeavy SSCertifiable Points: 5869 More actions · Answer 9

Perry
GilaMonster (6/8/2011)
--------------------------------------------------------------------------------
it's 0 times in 6 years.
wow, as often as that Gail
bopeavy (6/8/2011)
--------------------------------------------------------------------------------
And this Kind of thing is a pain when I have to get a time slot to fix someone's mistake!
nobody's perfect, it takes around 20-30 seconds at most in my experience to restart the SQL Server service. Most applications possibly wouldnt even notice the service restart if you were strategic in picking your timings. Do you not have maintenance windows?

I know it does not take that much time! but I was planning on having Cake and Ice Cream this evening not working! LOL!!:-D

:cool:

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

GSquared SSC Guru Points: 260824 More actions · Answer 10

bopeavy (6/8/2011)
GilaMonster
This is specific and unique to tempDB and likely has to do with what uses tempDB and how it's used.
To determine that a TempDB data file can be dropped during normal operation, SQL would have to ensure that no one is using that file. The only practical way to ensure that no one is using a tempDB file would be to completely quiesce the entire instance. That means no running transactions at all, no running statements, no queries whatsoever. Essentially it would have to stop and then block every single user connection on the server.
Yes, I agree, but you still have to admit this is a pain to DBA's!

I've been a DBA for almost 11 years, and I've not run into this particular pain yet.

I can see that it would be nice in certain circumstances, but I would prioritize it quite low as something for Microsoft to fix. They have bigger issues to address first.

- 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 11

GSquared
bopeavy (6/8/2011)
--------------------------------------------------------------------------------
GilaMonster
This is specific and unique to tempDB and likely has to do with what uses tempDB and how it's used.
To determine that a TempDB data file can be dropped during normal operation, SQL would have to ensure that no one is using that file. The only practical way to ensure that no one is using a tempDB file would be to completely quiesce the entire instance. That means no running transactions at all, no running statements, no queries whatsoever. Essentially it would have to stop and then block every single user connection on the server.
Yes, I agree, but you still have to admit this is a pain to DBA's!
I've been a DBA for almost 11 years, and I've not run into this particular pain yet.
I can see that it would be nice in certain circumstances, but I would prioritize it quite low as something for Microsoft to fix. They have bigger issues to address first.

Did you see the post before yours LOL! It is not Oh a real pain speaking but more of an inconvenance!:hehe:

select ' @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'

select ' @@ @@@@@@@@@@@ @@@@ @@@@@@@@@@@@@@'

select ' @@ @@@@@@@@@@@ @@@@@@@@@@@ @@@@ @@@@@@@@@@@@@@'

select ' @@ @@@@ @@@@ @@@@@'

select ' @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@''

:cool:

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