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

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'

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
seems like the db growth of your database is 10 percent
DTGuAuthor Commented:
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...

Aneesh RetnakaranDatabase AdministratorCommented:
Did you check the log and data file size on each steps
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Aneesh RetnakaranDatabase AdministratorCommented:
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.
DTGuAuthor Commented:
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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DTGuAuthor Commented:
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
DTGuAuthor Commented:
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...
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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.