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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy