Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Data Warehousing / December 2004

Tip: Looking for answers? Try searching our database.

DBCC SHRINKFILE Taking Forever & GROWING Data File Instead of Shrinking It

Thread view: 
Enable EMail Alerts  Start New Thread
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.