• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 696
  • Last Modified:

MDF Files growing huge.

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
ESP_TECH
Asked:
ESP_TECH
1 Solution
 
David ToddSenior DBACommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi ESP_TECH,

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

Aneesh
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
ESP_TECHAuthor Commented:
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
 
David ToddSenior DBACommented:
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
 
David ToddSenior DBACommented:
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
 
ESP_TECHAuthor Commented:
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
 
ESP_TECHAuthor Commented:
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
 
David ToddSenior DBACommented:
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
 
ESP_TECHAuthor Commented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now