SQL MDF Growth

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.

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.

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)
begin
    select @name = min(name) from #tables where name > @name
    select @sql = 'exec ' + @SourceDB + '..sp_executesql N''insert #SpaceUsed exec sp_spaceused ' + @name + ''''
    exec (@sql)
end
select * from #SpaceUsed
drop table #tables
drop table #SpaceUsed

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:

• Database file sizes and space used
• Table sizes - row counts and actual size (say, using output from Atlantis Interactive's Data Space Anlyzer http://www.atlantis-interactive.co.uk/products/dataspaceanalyser/default.aspx) as well as free space allocated to the table but not used.

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?

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?