Sql-server – What causes disk space growth from Informatica load onto SQL Server

auto-growthdisk-spaceinformaticashrinksql server

We've been dealing with huge disk growth on SQL Server from Informatica. After the load, the database grows to 2.4TB. After the database shrink, it goes to 1.05TB. What could likely cause this to happen? What settings can we check in Informatica and/or SQL Server for our next run to troubleshoot this or do a guess/check?

EDIT:

There are two ways to move data using an Informatica mapping. Using SQL Overrides (running straight SQL) using the SQL Transformation type or using the built in data streams with Informatica's out of the box functionality. In this case, we are using data streams. When data streams are used, straight SQL is still used, but Informatica creates the SQL code behind the scenes. We are loading in 1,000,000 record increments. I thought perhaps Informatica might be tellinig SQL Server to allocate disk space as it loads, but I'm not even sure what command(s) to look for if it did.

Best Answer

Check some of the following:

-Free space of DB in prod before backup. You might just have lots of free space in prod pre-reserved. It used to be a best practice before IFI was introduced. You'll still want to pre-allocate.

-Transaction Log size post recovery but pre-shrink by running DBCC SQLPERF (LOGSPACE) both times. Maybe the transaction log doesn't have open transactions post restore and you are now checkpoint/shrinking it. This would mean you might not be taking prod tlog backups and your recovery model is FULL.

-List the sizes for all of your tables in order of size post recovery and then against after shrink, then compare.