Re: DBCC SHRINKFILE Taking Forever & GROWING Data File Instead of Shrinking It
From: AnthonyThomas (Anthony.Thomas_at_CommerceBank.com)
Date: 12/13/04
- Next message: Andy Willis: "Scheduled Jobs Failing"
- Previous message: Anthony M. Davis: "Re: DBCC SHRINKFILE Taking Forever & GROWING Data File Instead of Shrinking It"
- In reply to: Anthony M. Davis: "Re: DBCC SHRINKFILE Taking Forever & GROWING Data File Instead of Shrinking It"
- Next in thread: Aaron [SQL Server MVP]: "Re: DBCC SHRINKFILE Taking Forever & GROWING Data File Instead of Shrinking It"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 12 Dec 2004 23:18:34 -0600
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
-- "Anthony M. Davis" <tony@fnwarranty.com> wrote in message news:OLVsLcK4EHA.3708@TK2MSFTNGP14.phx.gbl... That was very informative, Andrew. Thanks a great deal for going into such depth. -Tony "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:Ocxf%23MJ4EHA.2316@TK2MSFTNGP15.phx.gbl... > 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. > > -- > > Andrew J. Kelly SQL MVP > > > "Anthony M. Davis" <tony@fnwarranty.com> wrote in message > news:uZe7$SG4EHA.2608@TK2MSFTNGP10.phx.gbl... >> 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 >> >> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >> news:%23RrGd6F4EHA.1524@TK2MSFTNGP09.phx.gbl... >>> 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 >>> >>> >>> -- >>> Andrew J. Kelly SQL MVP >>> >>> >>> "Anthony M. Davis" <tony@fnwarranty.com> wrote in message >>> news:eF9TnSF4EHA.2876@TK2MSFTNGP12.phx.gbl... >>>> 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 >>>> >>> >>> >> >> > >
- Next message: Andy Willis: "Scheduled Jobs Failing"
- Previous message: Anthony M. Davis: "Re: DBCC SHRINKFILE Taking Forever & GROWING Data File Instead of Shrinking It"
- In reply to: Anthony M. Davis: "Re: DBCC SHRINKFILE Taking Forever & GROWING Data File Instead of Shrinking It"
- Next in thread: Aaron [SQL Server MVP]: "Re: DBCC SHRINKFILE Taking Forever & GROWING Data File Instead of Shrinking It"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
