Why TempDB is growing while SSIS job is running

One of my client raised an issue that their production tempdb is growing when SSIS package runs. This put me into confuse because generally SSIS not using tempdbs of SQL instances at all. Tempdb holds the intermediate data when SQL Server is doing tasks like:

• DBCC CHECKDB or DBCC CHECKTABLE;
• Common Table Expressions (CTE);
• ORDER BY, GROUP BY, UNION Queries;
• Index rebuild or creation.

Only one task was matched to my scenario that Index rebuild and creation. Yes finally found that the reason was not SSIS and the reason was destination tables have so many indexes.

The following answers/post were helped me to find the reason.

https://ask.sqlservercentral.com/questions/46812/does-ssis-use-tempdb.html

I am trying to understand the use of tempdb by SSIS to see if we would benefit from having a server dedicated solely to running SSIS packages. Are there situations were SSIS would need to use a tempdb apart from the tempdb located on servers defined by package connections. That is to say, if my SSIS machine is Server A, and it is performing an ETL from Server B to Server A, are there situations where the SSIS package would need to use the tempdb on Server A?

As you wrote if you have Server A - SSIS and then Server B and Server C - SQL Instances and moving data from B to C using SSIS, then SSIS will not use a tempdb on the Server A if there is also a SQL instance installed.

Generally SSIS does not utilize a tempdbs of SQL instances at all. Only if SSIS executes some SQL statements against the SQL instance then depending on the nature of the statement the tempdb can be utilized to accomplish that statement.

On the other side even SSIS does not utilize temp db directly, it can utilize a space on the Server A if it runs out of memory. SSIS uses a temp files for it's internal purposes. If have a Data Flow task in the package and take a look on the properties of the Data Flow Task, you will find there a BufferTempStoragePath and BLOBTempStoragePath, where you can specify the path where the temp files will be stored in case of hi memory pressure. The same relates to the Cached Lookups or Cache Transformation. Whenever the amount of data doesn't fit the process memory, it will be stored into temp files.

When you do not specify those properties, then a default temp location of an account under which the SSIS package is being executed will be used. Therefore it is important to set the temp location correctly for the accounts in case you have say a small system drive and big data drive as the default temp location for accounts is on the system drive.

https://social.msdn.microsoft.com/forums/sqlserver/en-US/f149300e-d357-40da-bb11-a1c0f1e82f6b/ssis-package-causes-tempdb-to-run-out-of-space
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not allocate space for object 'dbo.SORT temporary run storage: '...' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."

The destination is sql 2000. The package does not create any large temp tables.

apart from setting MaxInsertCommitSize property on the destination in Data Flow, is there anything else I could do? Where do I find this MaxInsertCommitSize option?

There may be several reasons for running out of space in TEMP db. One could be that the table you are loading has indexes; then the DB engine will use the TEMP DB to perform sort operations.

I would use the equivalent of a SQL Profiler to monitor the activity in the target DB and find out what exactly is happening. Also, what are the space allocation setting in the TEMP Db?The bottom line is that this issue looks more ralated to the destiantion DB engine than to SSIS

Cheers!
Uma