I have a Production database and a Development database on two different servers. Both databases are being fed from the same data warehouse on a staging server. There are a series of automated SQL jobs that run and populate tables in a database. The jobs were copied from Dev into Prod so they should also be the same. However, Prod is growing much faster than Dev.
There are approximately 1 billion rows of data in the database. Total data files in Prod are about 123GB larger. Total index files are about 31GB larger. I'm new to this, but I would expect the two databases to be fairly similar in size. If anything I was expect Dev to have some extra 'junk' and potentially be the bigger database.
Any ideas how to find the source of this size difference? I can increase the disk space on Prod if needed, but indicates to me that there may be an issue that needs to be addressed. I'd like to recoup the 153GB if possible.
I'm admittedly a rookie when it comes to this, but I checked the Index folder in each environment and they both appear to have one Index. I checked properties and they look the same as well. Would more Index also cause an increase in size in the data files?
I'm running the IndexOptimize script from Ola Hallengren in both Prod and Dev and hoping that is adequately handling any significant fragmenting. I haven't actually migrated any data from Dev to Prod. We have a staging server that hosts the data. A set of SSIS and stored procedures moves the data from staging to databases on Dev. More SSIS jobs and stores procedures populated the tables in Dev. The SSIS and stored procedures are promoted from Dev to Prod and they run on Prod independently. The Prod jobs access the same staging server as Dev.