Large MDF post SQL 2016 Upgrade

neil_g · 22 Jul 2016 at 09:49

We have 2 SQL servers which replicate data from an IBM AS400, these SQL servers are then used in round robin for reporting and automation rather than putting load on the IBM box.

They were both Win 2008R2 with SQL 2008 R2 Enterprise however we have taken on a program of upgrading to newer Standard SQL (for licencing reasons).

So after switching off the round robin we started upgrading one of the boxes, we did the following:

*Backed up the SQL databases
*Uninstalled SQL 08 Enterprise
*Installed SQL 12 Standard
*Restored SQL databases

All worked as expected. However the requirement for some of the reporting services options within SQL 16 then came up, so we:

*Upgraded Windows to 2012R2 to support SQL 16
*In place upgrade to SQL16 Standard

Now again this was all running as expected. However the primary database has grown massively. Comparing the 2 SQL boxes which should have the same data, the 2016 box MDF file is double the size (and consequently maxed the disk space).

I'm not sure what's going on, I've cleared a few Gb from the drive and tried the DBCC Shrink commands and it hasn't touched the size.

Confused!

zeegy · 22 Jul 2016 at 14:16

neil_g said:
We have 2 SQL servers which replicate data from an IBM AS400, these SQL servers are then used in round robin for reporting and automation rather than putting load on the IBM box.

They were both Win 2008R2 with SQL 2008 R2 Enterprise however we have taken on a program of upgrading to newer Standard SQL (for licencing reasons).

So after switching off the round robin we started upgrading one of the boxes, we did the following:

*Backed up the SQL databases
*Uninstalled SQL 08 Enterprise
*Installed SQL 12 Standard
*Restored SQL databases

All worked as expected. However the requirement for some of the reporting services options within SQL 16 then came up, so we:

*Upgraded Windows to 2012R2 to support SQL 16
*In place upgrade to SQL16 Standard

Now again this was all running as expected. However the primary database has grown massively. Comparing the 2 SQL boxes which should have the same data, the 2016 box MDF file is double the size (and consequently maxed the disk space).

I'm not sure what's going on, I've cleared a few Gb from the drive and tried the DBCC Shrink commands and it hasn't touched the size.

Confused!

How much of the db is actually used? is auto shrinking enabled?

Additionally, what are the sizes like on the log file end? Are you using a simple or a full recovery model?

neil_g · 22 Jul 2016 at 16:08

seems to be 98% used. auto shrink is true.

the ldf is tiny in comparison and the recovery model is full.

neil_g · 26 Jul 2016 at 09:19

any ideas? im stumped at the moment.

LFletcher · 26 Jul 2016 at 15:00

Do you know if you were using database compression?
I don't believe it's a feature in the standard edition, but was in the enterprise edition.

neil_g · 26 Jul 2016 at 15:12

nope, no compression set on the original install

LFletcher · 26 Jul 2016 at 15:34

Longer shot. Do your tables have indexes on them or are they heaps?

neil_g · 26 Jul 2016 at 16:14

there are clustered indexes

e: the indexes are ridiculously fragmented, might rebuild some and then retry the shrinks.

Ev0 · 26 Jul 2016 at 22:43

Showed this to the wife in jest as she's a SQL sysadmin, first thing she said was don't use auto shrink as it fragments your indexes (well she said it does something naughty to your indexes but this is a family friendly forum

)

LFletcher · 27 Jul 2016 at 09:39

I'd second removing Auto Shrink from the database.

Have you tried comparing the tables in both databases to see where the additional space is being used?

Borrowed this code from the net;

Code:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

Run against both your databases and compare the differences.

JustBen · 27 Jul 2016 at 10:04

have a look at this : https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html for indexes.

We've started implementing it and it's so far doing a cracking job.

neil_g · 27 Jul 2016 at 13:19

Ev0 said:
Showed this to the wife in jest as she's a SQL sysadmin, first thing she said was don't use auto shrink as it fragments your indexes (well she said it does something naughty to your indexes but this is a family friendly forum )

thanks, its set to FALSE on the 2008 box. got a feeling its TRUE on the new one. will change it.

LFletcher said:
I'd second removing Auto Shrink from the database.

Have you tried comparing the tables in both databases to see where the additional space is being used?

Borrowed this code from the net;

Run against both your databases and compare the differences.

its the unused space that seems much higher. wonder if its the fragmentation causing it?

always4lora said:
have a look at this : https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html for indexes.

We've started implementing it and it's so far doing a cracking job.

thanks will take a look.

LFletcher · 27 Jul 2016 at 15:03

If you're seeing unused space for each of the tables, then yes if your fragmentation is high, rebuilding the indexes on those tables is likely to sort out those issues.

This article also recommends using Ola's script to maintain your indexes if you don't already have something, https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

Out of interest did you run a DBCC CheckDB after the restore and any of the other bits mentioned in this article (more as a best practice than anything else), http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/