June 23, 2010 at 4:35 am
sorry to start another thread on autogrowth but i need a quick answer (as usual)...
i found some databases (not configured by me!) that were set to autogrowth 1MB, one db in particular is now over 32GB (!!!). I assume that it has just been autogrowing from probably the default initial size of 10MB over time.
my question is on the file fragmentation. with autogrowth, does SQL 2008 make any attempt to add the new growth to the last end of file? or does it just randomly add a 1MB chunk to disk where it is logically connected to the last chunk? i can't find any info on this, but from my old SQL 6.5 and 2000 days i seem to remember that it just put the next segment randomly on disk, which would lead to severe fragmentation. of course with SANs, RAIDs, and multiple platters, it's not actually one contiguous file and spread out anyways, but i would assume that defining a single 32GB file would be better than a 10MB file autogrowing by 1MB.
i'm also looking for answers on how to best cleanup this 32GB db. my solution was to backup the db, delete the db, defrag the disk(s), recreate the db with a large size (32GB+ adequate free space for growth), then restore the database, THEN do a full index rebuild job to defrag the clustered indexes and tables. Is that right?
many thanks in advance
June 23, 2010 at 6:34 am
As far as I know the burden is on the Operating System and not on the database engine. When SQL Server needs more space and datafile is set to autogrow SQL Server will ask the Operating System to provision a new extent - like it will happen to allocate a word or excel document 😀
I think the process you described to defrag affected database is sound. It takes care of SQL Server physical space - new database sized at 32 Gig in this case - as well as database objects - rebuild clustered indexes, which will reorg base tables.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 23, 2010 at 10:24 am
Yep, it really is down to the OS.
In general file fragmentation shouldn't be that big a deal. Have you eliminated all other issues on the server that could cause performance headaches and now just need a challenge? There are cases where fragmentation is a major problem, but those are relatively rare. I'd spend more time on the queries, index structure and table structure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 24, 2010 at 6:38 am
thanks guys.
this is just one thing in a long laundry list of things to check on a development server that is going live in a few weeks. the idea of it going from 10MB to 32GB one megabyte at a time doesn't seem ideal. i can't imagine not cleaning it up and letting it go as is.
FTR, i didn't set this up or have anything to do with the server until now...:hehe:
June 24, 2010 at 9:05 am
The autogrow might cause file fragmentation. To see the current status of the database files you could use contig.exe
http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx
contig.exe can also be used to defragment a single file. That is probably the fastest and easiest way to get rid of file fragmentation. No need to backup and restore the database.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply