0

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.

Improve this question
7
  • Reindexing can do this.
    – stacylaray
    Commented Mar 24, 2015 at 20:49
  • 1
    Why don't you just keep the data file large so that you don't have to constantly grow/shrink it?
    – Aaron Bertrand
    Commented Mar 24, 2015 at 20:53
  • 1
    Can you describe the data loading process? What is the db recovery model? Are you taking tlog backups ?
    – Kin Shah
    Commented Mar 24, 2015 at 20:54
  • @Aaron, we are almost hitting our disk space limit.
    – JustBeingHelpful
    Commented Mar 24, 2015 at 21:09
  • 1
    If you are on that kind of precipice for disk space, then aren't you just delaying the inevitable? Surely your data is going to continue to grow, at some point you're going to have to invest in more space...
    – Aaron Bertrand
    Commented Mar 24, 2015 at 21:12

2 Answers 2

Reset to default
0

I had the numbers incorrect. The shrink was done and the size was 0.3TB. So SQL Server had 6 times the allocated storage, not 2 times the allocated storage. I resolved this by creating a target definition (by importing the table schema in Informatica) after I removed the indices on the target table. SQL Server was allocating the disk space, even though the indices were disabled. Probably because Informatica told it to. So now Informatica just loads data, and doesn't even care if there are indices even though there are. Rather than disabling them pre ETL and enabling them post ETL, we now just drop the indices in pre ETL and then create them post ETL.

Improve this answer
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.