Large MDF post SQL 2016 Upgrade

[neil_g]

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]

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

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

 

[neil_g]

any ideas? im stumped at the moment.

 

[neil_g]

nope, no compression set on the original install

 

[neil_g]

there are clustered indexes

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

 

[neil_g]

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.

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?

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.