June 14, 2009 at 3:46 pm
talltop (6/14/2009)
As you traverse your DBA career, you will run into situations that are not always by the book.
Heh... understood. And I understand the premise that you walked into a situation that already existed. But, I've found a dandy way to make situations that are almost always "by the book"... write the book. 😉 I've never been whacked for showing folks a better way.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2009 at 7:24 am
talltop (6/12/2009)
The problem states that the manager did not want the files moved to another directory and the prior DBA honored that. However, I would have done the same as you would of, moved the files. However, that was not the problem at hand. Also, I tested your 2005 version on my directory before I detached some files and then after as well. The list I get back is identical in both cases listing all db files in the directory detached or not, which does not solve the problem. If there are not detached files in the directory, then no rows should be returned. If there are detached files, then only detached files should be returned in the list. Hold on strike that, I got it working now...There are 2 things I like about your solution though. No Cursors and it is short!!!! Good job!!!!! You also took advantage of the EXCEPT like I did. Better performnce if you are dealing with a lot of files...check out the xplans on the EXCEPT..Also I forgot to mention, this was a TESTBED server for ALL of development and they were pretty much out of space, not a production server which explains the drives and directories...
Not sure how you'd end up with all files, detached or not. I tested it on two different servers, and it did exactly what it's supposed to in both cases. It just listed the ones that weren't attached.
Makes sense on the dev server thing. My desktop machine is the same way, all files in the default directory. (For my local copy of Dev Edition, I mean.)
- 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
June 15, 2009 at 10:54 am
I agree with Jeff's first response ...
Been in that same situation and did almoist the exact same thing, just used a few more words ...
That manager was not long for his position since he had really dropped the ball on this occasion and so many others in the past leading up to such a situation ~
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 15, 2009 at 11:30 am
I've worked for incompetent managers in the past as well and they usually end doing themselves in. They don't need my help. However, tellling off a manager or advising someone else to do that is never a good idea in my opiniion. Particularly in today's economy. :crazy: There are other diplomatic ways to get your point across...
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 15, 2009 at 11:41 am
talltop (6/15/2009)
I've worked for incompetent managers in the past as well and they usually end doing themselves in. They don't need my help. However, tellling off a manager or advising someone else to do that is never a good idea in my opiniion. Particularly in today's economy. :crazy: There are other diplomatic ways to get your point across...
Fine... I'll reword it....
Advise the manager of the potential of making an extremely grave error if his/her process is followed and kindly suggest that the alternate method should be used and what the advantages are.
When the manager refuses, do it his/her way and if anything goes wrong, be prepared for that manager to blame you because, as you say, "particularly in today's economy", the manager wil not take the hit for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2009 at 11:43 am
talltop (6/15/2009)
I've worked for incompetent managers in the past as well and they usually end doing themselves in. They don't need my help. However, tellling off a manager or advising someone else to do that is never a good idea in my opiniion. Particularly in today's economy. :crazy: There are other diplomatic ways to get your point across...
There have been a couple of times in my life when I'd really rather live on the streets and dumpster-dive for food than continue working with certain people. Beyond that kind of circumstance, I have to agree that telling people off isn't as useful as communicating. There's a big difference.
- 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
June 15, 2009 at 12:07 pm
Agreed, and that is exactly my point. There is no reason to tell off a manager because he wanted detached files left in a active db directory on a development box. Not a big deal at all:ermm:, and as we have found there is now a quick easy way to identify them anyway...:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 15, 2009 at 12:23 pm
But, on the flip side, there is also no reason that it could be pointed out that it would be beneficial to move those detached databases to another directory. If they need to be restored, it would simply be a matter of moving them back and then attaching them. If moved to a subdirectory of the database directory, then this can be accomplished very quickly.
Perhaps the problem was more in how the adivce was provided rather than the advice itself. But as I see it, Jeff did restate it in another way.
Please don't take the advice given as how it should always be done. You do need to use your own judjement.
June 15, 2009 at 12:57 pm
The prior DBA did make that suggestion, but the manager wanted the files left in place and since it is a development box anyway he chose not to go to war over it. In the long run, it is just not that big of a deal, and as we have found there is a quick and easy way to identify and move them anyway...:-P
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 15, 2009 at 1:30 pm
In that case, I recommend keeping a log of databases that are detached and when it was done.
These methods will find a detached file, but they don't take into account when it was detached.
You have some that were detached six months ago that are ready to be deleted, but what if someone detaches a database in order to copy it to another server, right before you run the script? Or detached it this morning in order to copy it, and plans on attaching it again later this afternoon?
Definitely use the list generated by the script, but also check the last modified date and all that, before deleting anything.
- 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
June 15, 2009 at 1:41 pm
Yep, now that we have the list, getting the last modifed date of the detached files is a piece of cake. Going forward, all detached database files will be moved to another subdirectory and no one will be authorized to detach/copy/move a database except the DBA. Again, not a big deal guys. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 15, 2009 at 1:43 pm
Lynn Pettis (6/15/2009)
But, on the flip side, there is also no reason that it could be pointed out that it would be beneficial to move those detached databases to another directory. If they need to be restored, it would simply be a matter of moving them back and then attaching them. If moved to a subdirectory of the database directory, then this can be accomplished very quickly.Perhaps the problem was more in how the adivce was provided rather than the advice itself. But as I see it, Jeff did restate it in another way.
Please don't take the advice given as how it should always be done. You do need to use your own judjement.
BWAA-HAA!!!! I could be wrong, but I believe that's the first time you eluded to it being a development server!
Still, data is data. If you lose it and it was important (you normally don't know until you lose it), it is you who will suffer... not the manager who gave you the task. Be careful my friend.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2009 at 2:32 pm
No it isn't... I mentioned it back on the first page of this thread. No data was lost..I am not sure where you are getting that from. the files were simply detached six months ago and left in place, and that decison to detach them was made by the manager since they were no longer going to be used and thus down-sized and the DBA honored his request not to remove/move them at that time...The files have not been removed at this time. I think you are making more a big deal out this than it warrants..my friend...Just my opinion...
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 15, 2009 at 2:55 pm
Also I forgot to mention, this was a TESTBED server for ALL of development
Yep... you're correct. I totally missed it. Anyway, if you think I'm making a big deal out of things, that's ok... at least I have your attention. 😉 Good luck.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2009 at 3:06 pm
talltop (6/15/2009)
Yep, now that we have the list, getting the last modifed date of the detached files is a piece of cake. Going forward, all detached database files will be moved to another subdirectory and no one will be authorized to detach/copy/move a database except the DBA. Again, not a big deal guys. 😀
Excellent. Sounds like you have it well under control.
- 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
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply