SQL Server Forum / General / Data Warehousing / December 2004
DBCC SHRINKFILE Taking Forever & GROWING Data File Instead of Shrinking It
|
|
Thread rating:  |
Anthony M. Davis - 12 Dec 2004 14:12 GMT Hello all,
I'm getting unexpected behavior when running DBCC SHRINKFILE on my 9 GB data file. First, it's been running in excess of 10.5 hours now and, second, it is ever-so-slowly *growing* the used portion of the file. For instance, when I look at the 'Space allocated' section of the Taskpad view in Enterprise Manager as the shrink operation is running, at one point the data file allocation will be reported as:
6713.5MB (Used) | 2877.62MB (Free).
Then, refreshing the Taskpad view every second, on the subsequent refresh the usage is reported as:
6713.62MB (Used) | 2877.5MB (Free).
Every few seconds, more of the free space seems to get eaten up. However, the total allocated size for the data file is conserved (9591.12MB).
My first thoughts were that maybe the DBCC SHRINKFILE operation tends to grow data before shrinking it, or perhaps does so in an oscillating pattern, however over the 10.5+ hours it has only been consistenly eating up more and more free space (hundreds of megs since the operation started).
In my quest for understanding this phenomenon, I came across the article "FIX: DBCC SHRINKDATABASE or DBCC SHRINKFILE May Expand Database with Text or Image Data" (Q308627), which describes the behavior I'm experiencing. However, it purports that this problem was corrected in SQL Server 7.0 SP4 (I'm using SQL Server 2000).
So I'm still left wondering, what's the deal with DBCC SHRINKFILE? I'm also starting to freak out now because I stopped the DBCC SHRINKFILE operation in Query Analyzer and now a simple query joining two tables on indexed columns is taking excessively long to complete when it used to take a split second. Is it possible that DBCC SHRINKFILE has actually done more harm than good by fragmenting my data and indexes?
Any words of wisdom are much appreciated!
Thanks, Tony
Andrew J. Kelly - 12 Dec 2004 15:23 GMT Anthony,
Why do you want to shrink it in the first place? 2.8GB is not that much free space for a 9GB file that it should be an issue. The database requires lots of free space to do certain operations such as reindexing and such. When you shrink a database of database file it has to move any data at the end of the file towards the beginning of the file. This causes lots of fragmentation in your tables and indexes. When it moves the data there is no controlling how or where it moves the data. This operation is expensive and harmful to performance on lots of counts. This might answer some of your questions. And you can safely cancel the shrink as it will just rollback or finish the batch it happened to be working on at the time. All operations in the shrink are fully logged. This is another issue with shrinking though since it can fill your log file quite easily.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking considerations
 Signature Andrew J. Kelly SQL MVP
> Hello all, > [quoted text clipped - 38 lines] > Thanks, > Tony Anthony M. Davis - 12 Dec 2004 16:07 GMT Hi Andrew,
Thanks for the info. That does answer some questions. My reason for attempting the shrink was that I was seeing a lot of contiguous free space (a couple large blocks) fragmenting my data file on the file system when I analyzed it with Disk Defragmenter. I was hoping that by performing the shrink, it would defragment the file by moving all used pages to the beginning. (My data partition only has 23 GB capacity and I feared the data would've become increasingly fragmented as SQL Server began to "fill in the gaps", placing data in the free space near the beginning of the partition.) In other words, I was hoping to pack all the data at the beginning of the partition, so it would have sufficient room to grow with minimal fragmentation.
It is now my belief that a better approach would be to copy my largest table to a temporary database, truncate the original table, defrag the file system, then perform DBCC SHRINKFILE, and transfer data back to the original table, as suggested in the aforemention Q308627 article (Workaround b). Would you concur?
Tony
> Anthony, > [quoted text clipped - 57 lines] >> Thanks, >> Tony Andrew J. Kelly - 12 Dec 2004 21:40 GMT Anthony,
The internal fragmentation has nothing to do with the file level fragmentation. They are really two separate things and dealt with in different ways. The disk defrag tool sees where there are gaps in the file but knows nothing of the way the tables and indexes are positioned within. It is always a good idea to start with a contiguous file on disk and if it is fragmented at the OS level you can use something like Speed Disk to remedy this. Just make sure you have a good backup first and it is a good idea to stop sql server before you attempt it. The fragmentation within the file is handled totally by SQL Server. Although there can be a performance difference if you are reading a file that is physically located near the outer edge of the platter vs. the inner edge it is usually more important to ensure the table / index is contiguous first. As such it doesn't matter a whole lot where in the data file the data actually lives as long as you have enough room to place it in a physically contiguous fashion. There are two ways to do this. One is to BCP out all the data, truncate all the tables and then BCP them back in one at a time. Preferably in the order of the clustered index for that table. While this does not guarantee it is completely contiguous it does give the best chance that the data for each table will be close. The other is to rebuild the index with DBREINDEX. In order for this to work properly you need a lot of free space in the data file so it too has the best chance of being contiguous. When you shrink the file sql server moves any data that is near the end of the file to an open spot near the beginning. There is no consideration for keeping the tables contiguous on disk and they will get fragmented in a very bad way. This has NO bearing on what the disk defrag tool sees at the file level.
OK now to answer your original question a little better. Shrinking the file does not compact or change the fill on each of the pages. It simply moves them to another physical location in the file. So when you shrink a file you will most likely end up with just as much data as when you started give or take some. How much usable free space you end up with depends a lot on how much free space there was to begin with, where it was and how fragmented etc. It's not a pretty operation and not exactly predictable down to the Byte level.
Hopefully that answered your questions a little better and gives you some more insight on this process.
 Signature Andrew J. Kelly SQL MVP
> Hi Andrew, > [quoted text clipped - 80 lines] >>> Thanks, >>> Tony Anthony M. Davis - 13 Dec 2004 00:02 GMT That was very informative, Andrew. Thanks a great deal for going into such depth. -Tony
> Anthony, > [quoted text clipped - 121 lines] >>>> Thanks, >>>> Tony AnthonyThomas - 13 Dec 2004 05:18 GMT It does not matter if the OS "sees" file fragmentation of database data and log files. SQL Server will use Scater/Gather I/O at every oportunity and, with multiple concurrent users or multiple databases coresident on disk, the likelihood that any two successive query requests would be contiguous data is highly unlikely.
What is more reasonable would be the internal data and index fragmentation of the doubly-linked lists traversing the pages within the files. Internal fragmentation occurs with internal data inserts and delete operations and periodically need to be reorganized but you use the DBMS to handle that. If you use DBCC REINDEX on the Clustered Index of a table, you will reoganize the index, but since it is the Clustered index, you will automatically reorganize the data and the other indexes, which are dependent on the Clustered index key, automatically.
This operation will help your performance. Two things, if you wish to shrink your data file, only do so after a database reorganization, using index rebuild operations. Moreover, if you are finding that the fragmentation is occuring to frequently and you are having to reorg the database too often, then you should reconsider altering the FILL FACTOR of the indexes. This can give more inter-page free space for new data insertions and will help to keep the fragmentation lower over the same period of time; thus, helping you to keep from reorganizing as often.
Hope this helps.
Sincerely,
Anthony Thomas
That was very informative, Andrew. Thanks a great deal for going into such depth. -Tony
> Anthony, > [quoted text clipped - 121 lines] >>>> Thanks, >>>> Tony Aaron [SQL Server MVP] - 12 Dec 2004 17:10 GMT In addition to Andrew's advice, please read Tibor's article: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Also, please cross-post wisely. Followups set to .server only.
 Signature http://www.aspfaq.com/ (Reverse address to reply.)
> Hello all, > [quoted text clipped - 37 lines] > Thanks, > Tony
|
|
|