none
SSIS package Fails fromTempDB Growth

    Question

  • I've had an SSIS package that failed about two weeks ago.  Nothing had changed about the package at all.  The SQL is within the package verses calling a SP.   The pkg originally ran in under two minutes and ran fine for months every two weeks.  Three weeks ago it failed with a growing TempDB during execution that after an hour or so failed.

    I changed one formatting thing in the SQL and recompiled the pkg and ran it and it worked great.  This past weekend the SAME installed pkg failed with the TempDB error.  It's almost as it i need to do a WITH RECOMIPLE directive but since it's not a Stored procedure I can't.  Is there someway for the SSIS pkg to run the SQL for a pkg in such a way as to avoid this TempDB error from happening?

    Monday, October 14, 2013 8:35 PM

Answers

  • Hi,

    First you should investigate which query is responsible for tempdb growth. You can use following dmv's to do that.

    1. sys.dm_db_file_space_usage - version store, user or internal
    2. sys.dm_db_session_space_usage - user or internal store
    3. sys.dm_db_task_space_usage - user or internal store and statement bloating the tempdb

    With this dmv's you will be able to determine wich query(ies) are responsible for tempdb growth and what type of objects are being used the most (worktables for query execution (internal) or temp tables and variables).

    More info: http://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    Tuesday, October 15, 2013 8:29 AM

All replies