March 18, 2011 at 3:33 am
Jim McLeod (3/17/2011)
george sibbald (3/17/2011)
rollback -dbcc shrinkfile with the emptyfile option, followed by alter database command to remove the file.
That would work (and was my first thought too), but I think I prefer Craig's much more intensive (time and I/O) method of emptying the primary filegroup and rebuilding it. Emptyfile can end up with some serious fragmentation issues, depending on how much data was added while multiple files were enabled.
Unfortunately Jim, I've had a similar problem. A number of my file manipulation methods come from a rediculously large LOB system, and since that's the worst case scenario, I tend to continue to use them out of self defense.
I agree though that emptyfile should work. In the two cases I've had to try using it though, it didn't behave as well as I'd hoped, so I'm loathe to recommend it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 18, 2011 at 4:34 am
Craig Farrell (3/18/2011)
alexus8888 (3/18/2011)
We got a 20% improvement in an ETL process by creating the filegroup with 4 files all on the same drive. didn't believe it myself till I saw it.Alexus, I believe I speak for everyone when I say your marketing is highly annoying. Dig yourself a hole, climb into it, and then bury yourself. Please?
I've got a shovel.
---------------------------------------------------------------------
March 18, 2011 at 4:37 am
Craig Farrell (3/17/2011)
Rem70Rem (3/17/2011)
What are the steps to rollback if needed?Generate a new filegroup, move *everything* into the new filegroup. Rebuild the original Primary as desired, move everything back, and remove the temporary filegroup.
IE: You don't, not really, unless you really, really have to.
just a thought, won't you have to use emptyfile to 'rebuild' the primary back to one file?
---------------------------------------------------------------------
March 18, 2011 at 5:02 am
george sibbald (3/18/2011)
Craig Farrell (3/17/2011)
Rem70Rem (3/17/2011)
What are the steps to rollback if needed?Generate a new filegroup, move *everything* into the new filegroup. Rebuild the original Primary as desired, move everything back, and remove the temporary filegroup.
IE: You don't, not really, unless you really, really have to.
just a thought, won't you have to use emptyfile to 'rebuild' the primary back to one file?
No, emptyfile isn't necessary in Craig's suggestion, which was to create a new filegroup and rebuild every object onto that filegroup, effectively leaving the PRIMARY filegroup near-empty. Then re-create the files in the PRIMARY filegroup to be how you want them (one single file of x GB in the case of this rollback). I suppose you *might* need to use EmptyFile here for objects you can't move off PRIMARY, but these will be tiny. Finally, re-build the objects back onto the primary filegroup.
More than a little painful, eh? 🙂 The advantages are that it's a lot more controlled (in terms of knowing the amount of time required to complete, and the size of transaction logging) than an EmptyFile, as you can rebuild your objects as manually as you want. Also, you know that you're laying down your objects in a sequential fashion, so you'll have no fragmentation.
That said, EmptyFile is a lot simpler to do, so if you don't have too much data to empty out, and you can fix the fragmentation later, it's not so bad.
March 30, 2011 at 4:41 pm
just reporting in. Sorry I have not posted before but believe me the issues we have at work at the moment now is not the time to be asking the SAN guys questions not strictly work related or urgent. We also seem to have lost the stats from the testing (bad DBA)
However just spotted a reference to this blog in another thread.........
http://www.sqlskills.com/BLOGS/PAUL/post/Benchmarking-do-multiple-data-files-make-a-difference.aspx
---------------------------------------------------------------------
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply