Solved

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

Posted on 2009-07-13
10
206 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

19 Experts available now in Live!

Get 1:1 Help Now