Solved

MDF Files growing huge.

Posted on 2006-07-18
10
655 Views
Last Modified: 2008-03-04
I've tried just about every answer I've found here on E-E with no success.

Here's the statistics:

Under DATA FILES:
SQL 2000 Server Enterprise edition
Autogrowth is set to 10 percent.

Under OPTIONS
Autoshrink is checked.
Auto update statistics is checked.
Auto create statistics is checked.

We have run DBCC SHRINKDATABASE('database', TRUNCATEONLY)
We have run DBCC SHRINKFILE
We have set a Maintenance Plan to shrink and truncate.

We have done all of this to no avail.  The databases continue to grow huge.  We have one that has 41% free and will not relenquish the empty space back to the OS.  We shrunk it originally from 48 GB back down to 4.5 GB last week by exporting the actual data to another DB and today it measures at 8.6 GB.  That is NOT good.  Any help would be greatly appreciated.

0
Comment
Question by:ESP_TECH
10 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 17135034
Hi,

First Off
Change the autogrow from 10% to 10MB.

That will help stop it growing quite so much at once.

Check your maintenance plans and SQL jobs - is there anything that is reogranising the data, or doing a dbcc dbreindex? a dbreindex needs quite a bit of free space.

Is the space in the data file or the log file?

HTH
  David

PS If it is the log file that is growing like no tomorrow, then the quick solution is to detach the database, move/delete/rename the log file, and the attach the database again. A default log file will be created, somewhere around a few MB.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17135218
Hi ESP_TECH,

Is this MDF or Ldf ? if it is mdf, it is your data which is causing this ...

Aneesh
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17135882
I agree with aneeshattingal that it might be the .ldf rather than the .mdf.
if that is the case, you simply need to implement a regular transaction log backup (every 30 minutes for example, or even shorter), or , in case the database is not needed to be restorable up to any point in time, you can change it's recovery mode to simple.

if it's the .mdf, you are probably loading and deleting alot of records, and possibly have alot of "empty" tables around.
try to rebuild those tables ie their clustered indexes, and see if the file shrinks then.
0
 

Author Comment

by:ESP_TECH
ID: 17139819
Hey guys,
I appreciate the help

When I do an sp_spaceused query it shows:
8604472 KB reserved
3012048 KB data
1860472 KB index_size
3731952 KB unused

The LDF file is 1512896 KB in physical size which could account for roughly half of the unused space but that still leaves the MDF as a partial culprit.
The backup is a straight backup, no re-indexing or reorganisation.
Torn page detection is also selected in the Options tab.

I changed the growth to 10 MB per your instructions dtodd and I've set the Recovery Model to simple as we do a nightly backup internally through our SQL program.

It took a week for it to grow this last time we'll see what it does this week.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 17141640
Hi,

The easiest way to see whihc file the free space is in, in Enterprise Manager, select the database - right pane shows the next level of objects - diagrams, tables, views, stored procedures, etc. Then click on View | Taskpad.

Don't forget to change the growth for _all_ the files - including the log files.

Regards
  David
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 35

Expert Comment

by:David Todd
ID: 17141664
Hi,

Another thing to check is what is the fill factor on the server? The database? The tables (or their clustered index)?

If the tables have an unusual fill factor, it could account for some of their extraordinary rate of growth.

Regards
  David

PS Default fill factor is 0, which translates to all the leaf nodes go to 100%, but some of the intermediate nodes have a little free space ...
0
 

Author Comment

by:ESP_TECH
ID: 17141943
dtodd,

The fill factor is using the default (0%).
Looking at the space allocation in the taskpad view (very cool, by the way) I found something interesting.  The space allocated is 7808.31MB.  7115MB of it is Used while 653.31MB is Free.  So if I'm reading this correctly SQL is seeing the empty data as full.  Is it possible that the database is hold NULL rows and columns in my database and if so how would I find it?  That might explain why the autoshrink is not bringing the size down.

The logs shrunk down to 47.62MB (probably from the Simple Recovery Mode).  
0
 

Author Comment

by:ESP_TECH
ID: 17142124
The funny thing is when I run sp_spaceused it show totally different numbers.  It's still showing the unused space at 3957808KB (3.7GB).
0
 
LVL 35

Expert Comment

by:David Todd
ID: 17142147
Hi,

As a suggestion - use Profiler to grab the query that Enterprise Manager is using to create this taskpad view. It might give clues as to how to interprit the sp_spaceused and other stuff ...

Regards
  David
0
 

Author Comment

by:ESP_TECH
ID: 17148127
Changing the logs to simple seems to be the key.  Once that was set I could run a DBCC SHRINKDATABASE(database, 10) then a  DBCC SHRINKDATABASE(database, truncateonly) and bam no more bloat.  One of our DB's MDF file actually shrunk from 19GB to 7.1GB
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

747 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

13 Experts available now in Live!

Get 1:1 Help Now