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!

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.

neil_g · 26 Jul 2016 at 15:12

nope, no compression set on the original install

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.

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.