June 24, 2009 at 10:17 am
This may fall into a category of who-cares or why-bother...and I accept that.
I notice that the disk I use for backups (dedicated seperate physical drive) becomes excessively fragmented. I am guessing that SQL initially opens a farily small file and extends as necessary for the backup. I am curious if there is a way inside SQL or outside SQL with some disk characteristic, that would allow me to specify an intial size of the file (pre-allocate) so that the files dont become so fragmented.
Iam currently mitigating the problem each day at end-of-day by copying the file. The copied file is not fragmented (clearly its easier for the OS when it knows at the outset how big the file will be. Then I delete the original fragmented file. This is successful, but not very sexy.
Any ideas?
June 24, 2009 at 12:58 pm
You are correct and I am not aware of anything that would preallocate space but do wonder what your motives are if you have a dedicated disk? You will be shipping them off to tape anyway and I assume these things get done out of hours in any case?
June 24, 2009 at 1:11 pm
It's a philosophical "its the right thing to do" issue for me, that I would like to correct IF there was an appropriate solution. I keep 3-5 days of backups online, and the files end up with a few thousand extents.
There is no other use of this disk...so no performance consequence. The nightly copy/delete resolves the issue...but Id prefer to fix the "problem", rather than fix the "symtpom".
Thanks for your reply!
June 24, 2009 at 1:34 pm
I wish I had time to worry about such things. 🙂 I would just accept it and add value elsewhere, cost vs benefit and all that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply