Solved

Why does SQL Express (2005) Expand when told to Compact?

Posted on 2009-07-13
10
205 Views
Last Modified: 2012-05-07
1. Starting with a SQL Express database which is 3.46 GB.
2. Delete .3 GB of data
3. 1st compact is performed.    Database size goes down. Shrinks by .15GB  (From 3.46GB to 3.31 GB.)
3. 2nd compact is performed     Database size goes up.   Expands by .33GB  (From 3.31GB to 3.64 GB.)
4. 3rd compact is performed     Database size goes down. Shrinks by .42GB  (From 3.64GB to 3.32 GB.)
5. Import 641 KB of data.       Database size goes up.   Expands by .32GB  (From 3.32GB to 3.64 GB.)

Included is the SQL code used to shrink the database and the log file


exec master.dbo.sp_dboption <<DB _NAME>>,'trunc. log on chkpt.','true'

  DBCC SHRINKDATABASE(<<DB_NAME>>,2)

  DBCC SHRINKFILE(<<MDF_FILE_NAME>>)

  DBCC SHRINKFILE(<<LOG_FILE_NAME>>)

Open in new window

0
Comment
Question by:DTGu
  • 4
  • 3
  • 3
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
seems like the db growth of your database is 10 percent
0
 

Author Comment

by:DTGu
Comment Utility
I agree.  But the question is why did it expand when given the command to shrink?  I would expect it to either keep getting smaller in ever decreasing amounts.... ie. 2% of 2% of 2%...  Then at some point it can't (or would not) shrink any more.

So once again... Why does it shrink, expand, shrink, expand...

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
Did you check the log and data file size on each steps
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
0
 
LVL 7

Expert Comment

by:wilje
Comment Utility
Where are you looking at the size?  If you are looking at the properties of the database where you see total size - that is combined size of both data and log files.
You could see that much difference just in the size of the transaction log growing when you run the shrinkfile on the database.
Also realize that by shrinking the data file (shrinkdatabase and shrinkfile(data-file), you have completely fragmented the indexes in your database.  You are going to need to reindex all indexes and that operation is going to cause your database to grow - probably right to the limit for Express at 4GB.
 
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:DTGu
Comment Utility
Let me look into the log file to see if I can find anything there... this was reported by another department on an older build.  It's one of those cases where QA is pushing the envelope on a **really** big database.

In regards to the fragmented indexes... I understand the reasoning and understand why the srhrinkdatase and/or shrinkfile would fragment the indexes and how the fragmentation would cause the DB size to grow.  The problem is this database does not have any indexes.  The database essentially stores test data as it comes from an instrument.  There are not any fancy reports. Just large amounts of raw data.
0
 
LVL 7

Accepted Solution

by:
wilje earned 250 total points
Comment Utility
No indexes?  Not even a clustered index for the table?  So, every table is a heap and you probably have lots of allocated space that isn't being used for each table because of the way heaps work.
I would bet anything that the size differences are due to the log file growing.  When you run a shrinkdatabase - both the data file and the log file are going to shrink.  Then, you run a shrinkfile on the data file, it doesn't change - but the transaction log grows to record the activity of the shrink operation.  Then, you perform a shrinkfile on the log and the total size goes back down.
Sounds like you are running into the max size for a database in SQL Server Express.  If so, my recommendation is going to be to build a clustered index on your tables.  That will cause the table to be rebuilt - which will recover additional space.  You can drop the clustered index after that has been done.
0
 

Author Comment

by:DTGu
Comment Utility
My apologies for the sudden lack of communication...

I had a higher priority interrupt, and plan to return to this question tomorrow (7/21)

I plan to check the sizes of the .mdf and .ldf files to see if it is in fact just the .ldf misbehaving or both.  I also plan to see what is logged in the .ldf

Thanks to all for your patience and help.

  - DTGu
0
 

Author Closing Comment

by:DTGu
Comment Utility
Thanks for the insights on how heaps behave, as well as the advice about the clustered index.

I work in an FDA regulated environment, so to add and drop the clustered index although a good idea is going to take a couple of miracles, an act of congress, plus some sacrifices to the Deities...
0
 
LVL 7

Expert Comment

by:wilje
Comment Utility
Ah, yes - FDA certified devices can be a real pain.  We had one system where we knew exactly what had to be done to fix it, but - because of the same issues had to actually unplug the machine and send it to the vendor to be fixed.
I feel your pain...
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now