January 17, 2011 at 10:04 am
I have a very strange problem where a execute SQL task in a SSIS package completely fills up my tempdb database (15GB) by executing a sproc. The weird thing is that when the exec sproc command is run from the query window it uses about 150MB of tempdb space and completes without issue.. I have done searches on the internet to try and find some sort of solution or explaination of the cause but haven't found anything
January 17, 2011 at 12:03 pm
Intriguing
What type of object are you using as your SQL task's datasource? (native?)
What does the SP do?
January 18, 2011 at 2:26 am
The datasource is a OLE DB and basically the sp gathers some data from a couple of table, does some calc and then inserts it into another table in the same database. The strange this is that the ssis package has been running for months and then just suddenly started doing this.
January 18, 2011 at 7:26 am
romanoplescia (1/18/2011)
The datasource is a OLE DB and basically the sp gathers some data from a couple of table, does some calc and then inserts it into another table in the same database. The strange this is that the ssis package has been running for months and then just suddenly started doing this.
If the package has not changed and you start to experience tempdb space problem this is definitly not related to SSIS.
There are many features in MSSQL that use tempdb.. These articles may help you identify what is taking so much space.
http://msdn.microsoft.com/en-us/library/ms345368.aspx
http://msdn.microsoft.com/en-us/library/ms176029.aspx
Let me know as your investigation progresses I may be able to help with more specific questions...
January 18, 2011 at 8:22 am
Thanks for the links above. As I previously mentioned it only seems to fill up the tempdb if the the sp in question is executed from the SSIS package, when executing from the query window it only uses about 150 MB of space.
Some of the stuff that I have been doing to try cure this problem is to remove some of the temporary tables in the sp and create them as physical tables in the db, recompile execution plans and dropped indexes on insert tables. None of this seems to have made any difference. The sp doesn't use any cursors, UDFs and none of the tables have any triggers on them, so I'm a bit stumped :unsure:
January 18, 2011 at 9:34 am
romanoplescia (1/18/2011)
Thanks for the links above. As I previously mentioned it only seems to fill up the tempdb if the the sp in question is executed from the SSIS package, when executing from the query window it only uses about 150 MB of space.Some of the stuff that I have been doing to try cure this problem is to remove some of the temporary tables in the sp and create them as physical tables in the db, recompile execution plans and dropped indexes on insert tables. None of this seems to have made any difference. The sp doesn't use any cursors, UDFs and none of the tables have any triggers on them, so I'm a bit stumped :unsure:
Using the methods described in the 2nd article, are you able to identify which objetcs are taking so much space?
January 18, 2011 at 9:24 pm
romanoplescia (1/17/2011)
I have a very strange problem where a execute SQL task in a SSIS package completely fills up my tempdb database (15GB) by executing a sproc. The weird thing is that when the exec sproc command is run from the query window it uses about 150MB of tempdb space and completes without issue.. I have done searches on the internet to try and find some sort of solution or explaination of the cause but haven't found anything
It sounds like it could be a bad execution plan is cached. Not sure how to clear those from SSIS. You could add a "with recompile" to the sproc you're running and see what happens.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2011 at 4:40 am
Happy days 😀 Problem's been solved.... Tracked it down to a developer in Australia who had added a new column to a select query in the sp. However this shouldn't usually cause a problem but my thinking is that SQL Server called a cached execution plan for the sp which didn't work with the new column. I recompiled the execution plan and restarted the sql instance and it started working.
January 19, 2011 at 7:20 am
romanoplescia (1/19/2011)
Happy days 😀 Problem's been solved.... Tracked it down to a developer in Australia who had added a new column to a select query in the sp. However this shouldn't usually cause a problem but my thinking is that SQL Server called a cached execution plan for the sp which didn't work with the new column. I recompiled the execution plan and restarted the sql instance and it started working.
Great news, thanks for sharing that with us! 😀
January 20, 2011 at 8:10 pm
romanoplescia (1/19/2011)
Happy days 😀 Problem's been solved.... Tracked it down to a developer in Australia who had added a new column to a select query in the sp. However this shouldn't usually cause a problem but my thinking is that SQL Server called a cached execution plan for the sp which didn't work with the new column. I recompiled the execution plan and restarted the sql instance and it started working.
Cool! Thanks for the feedback. You probably didn't need to restart the instance but that's a moot point for now. Glad you got it working.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2011 at 10:02 am
Ok, the problem has returned and recompile doesn't make a difference. Same symptoms as before, runs fine if sp is executed from SSMS but fills the tempdb data file if run from the SSIS package?? I have done a DBCC OPENTRAN ('tempdb') and this is the result returned.
Transaction information for database 'tempdb'.
Oldest active transaction:
SPID (server process ID): 142
UID (user ID) : -1
Name : sort_init
LSN : (11118:25335:290)
Start time : Jan 24 2011 4:12:06:787PM
SID : 0x77908cef5c30194aaa27cd42b0a75bd2
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I can't find any decent results on google or MS/books online re sort_init. Does anyone know why this could be filling up the tempdb data file?
January 25, 2011 at 9:10 am
1) perhaps you can use a plan guide to force a particular plan (i.e. the one that runs fast OUTSIDE of SSIS). the key is the sort - that is what is eating your tempdb. The plan differences are likely due to SET differences between SSMS and SSIS's execution contexts. This can be seen in the query plan cache using DMVs.
2) here is a bit of code to help with tempdb usage issues:
select t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc,t2.sql_handle, t2.statement_start_offset,
t2.statement_end_offset, t2.plan_handle
from (Select session_id, request_id,
sum(internal_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count) as task_dealloc
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1, sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and (t1.request_id = t2.request_id)
and t1.session_id > 50
order by t1.task_alloc DESC
--You can use the sql_handle and plan_handle to get the SQL statement and the query plan as follows
select text from sys.dm_exec_sql_text(put handle here)
-- you can show the query plan too.
select * from sys.dm_exec_query_plan(put handle here)
--
-- identifying top 5 tasks that are largest consumers of space in temmpdb
--
select top 5 *
from sys.dm_db_task_space_usage
where session_id > 50
order by user_objects_alloc_page_count + internal_objects_alloc_page_count DESC
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 27, 2011 at 9:23 am
It was collation... :angry:
February 2, 2011 at 11:39 pm
romanoplescia (1/27/2011)
It was collation... :angry:
Oh, now THAT's interesting. Talk about a difficult thing to find. Thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2012 at 8:50 am
When you mention collation, what was it set to?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply