SQL MDF Growth

Hi, I have a database that resided on a Windows Server 2003 box running SQL 2005 Server and was 15 GB for the longest time (about 12 years). I did a few things, including an upgrade to the application that uses the DB, changed the SQL compatability level from 80 to 90, changed recovery mode from full to simple. Now my database is growing uncontrollably. My mdf file is growing at a rate of 15gb a week and is now up to 233gb. I have no idea what to do. I have run a dbcc checkdb on it and it comes back with no errors. "CHECKDB found 0 allocation errors and 0 consistency errors in database." Is there another way to tell if something is corrupt? I have checked the logs and find everything clean. TO make things worse, i'm now on a new Windows 2008 R2 Server running SQL 2008 R2 x64. I do have a backup from my old SQL 2005 server but it was from when it had already reached 65GB in size and I do not have the original when it was small. I'm worried i'm screwed. Any suggestions on what to check would be appreciated. I did check with the vendor and they do say my configuration is supported and are not willing to help. Thank you
more ▼

asked Nov 08, 2011 at 12:00 AM in Default

Pleasehelp gravatar image

1 1 1 1

Any updates? Have you had a chance to try some of our suggestions?
Nov 09, 2011 at 05:41 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

The changes you made shouldn't cause any sort of uncontrollable growth, but each needs to be considered.

  1. Application Upgrade - What has changed from the previous version? Is there extra logging/activity in the newer version that could be causing the rapid DB growth? Talk to the vendor about this.

  2. Change the compatibility level - This has no effect on DB growth that I know of.

  3. Change recovery model from FULL to SIMPLE. This could be a cause for your problems. What size was the log file before changing the recovery model? It may have been that your log file was huge and the data file small (happens if you have FULL recovery model and don't run log backups). This may only have become apparent after changing to the SIMPLE model.

Additionally, I suggest you take a look at the autogrowth settings of your DB data file(s) and log file(s). If they are on their default settings, you will have 10% growth set. This can cause growth that feels exponential. Change this to a sensible growth value.

You mention you are on 2008R2 but not the edition. Is it Standard of Enterprise Edition?
more ▼

answered Nov 08, 2011 at 12:10 AM

WilliamD gravatar image

25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left

Autogrowth won't actually trigger unless it needs to.

It sounds to me as though it's the application itself that's the most likely culprit. 15GB a week? I've seen similar behaviour when some logging functionality was enabled in an application - this caused a massive increase in data size.

What is the growth pattern? Is it exponential, or is it linear but fast? Does it match with the caseload / transaction load / business load being put through the server?

Something that might help with troubleshooting is to take periodic (say, weekly) snapshots of the following information:

Doing some analysis on that would almost certainly help identify the culprit(s). If you've got massive growth in specific tables, then you might find it useful to run a trace against code that goes against that table.

If you find a table has lots of free space and isn't used, then check to see if there's a clustered index. If there isn't, can you make one?
more ▼

answered Nov 08, 2011 at 02:30 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left

The most likely issue is a change in what is stored.

Determine your largest tables and see what sort of information they contain.

Here is a quick query for that:

SELECT object_name(object_id) AS [Object Name]
, SUM(used_page_count) * 8192. / 1024 / 1024 AS [Pages Used in MB]`
FROM sys.dm_db_partition_stats
GROUP BY object_id
ORDER BY SUM(used_page_count) DESC;
A quick guess from unfortunate experience would be XML files that may have previously been stored on the filesystem are now being stored in the dB.
more ▼

answered Nov 08, 2011 at 05:08 AM

Blackhawk-17 gravatar image

11.9k 28 31 37

(comments are locked)
10|1200 characters needed characters left

You can run given below query and find which table have huge size -

 declare @sql varchar(128)  create table #tables(name varchar(128))
select @sql = 'insert #tables select TABLE_NAME from ' + @SourceDB + '.INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE'''
exec (@sql)

create table #SpaceUsed (name varchar(128), rows varchar(11), reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18))
declare @name varchar(128)
select @name = ''
while exists (select * from #tables where name > @name)
    select @name = min(name) from #tables where name > @name
    select @sql = 'exec ' + @SourceDB + '..sp_executesql N''insert #SpaceUsed exec sp_spaceused ' + @name + ''''
    exec (@sql)
select * from #SpaceUsed
drop table #tables
drop table #SpaceUsed
more ▼

answered Nov 08, 2011 at 04:03 AM

Amardeep gravatar image

1.3k 87 88 89

(comments are locked)
10|1200 characters needed characters left

You can check one of the built-in reports by right-clicking on the dB in SSMS and selecting the Disk Usage one. There is a section titled Data/Log Files Autogrow/Autoshrink Events. Expand that and you will get dates and times and size deltas.

This won't tell you why but at least you can see if there is a pattern of when the growths appear.

It is also conceivable that there used to exist a process that purged stale data that was not migrated. Again, see the application owner or vendor for that.
more ▼

answered Nov 08, 2011 at 05:12 AM

Blackhawk-17 gravatar image

11.9k 28 31 37

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 08, 2011 at 12:00 AM

Seen: 1167 times

Last Updated: Nov 08, 2011 at 12:00 AM