?
Solved

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

Posted on 2009-07-13
10
Medium Priority
?
212 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 1000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 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