Solved

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

Posted on 2009-07-13
10
207 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
ID: 24845081
seems like the db growth of your database is 10 percent
0
 

Author Comment

by:DTGu
ID: 24845113
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
ID: 24845158
Did you check the log and data file size on each steps
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24845173
0
 
LVL 7

Expert Comment

by:wilje
ID: 24845986
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
 

Author Comment

by:DTGu
ID: 24854496
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
ID: 24854542
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
ID: 24900335
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
ID: 31603077
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
ID: 24931741
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Anyway to make these 2 SQL statements into one? 13 39
Find data in a column which is not in  a date format 29 30
Access 2016 - query 23 61
error in my cursor 5 33
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Backup & Restore 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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

810 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