March 18, 2010 at 10:26 pm
Hi
i have 2 packages
1. first Package get data from one database into Temp Table
2. Second package gets diff data from Temp Table to main table,
both temp and main tables are with same DDL...
First Package runs pretty fast and gets around 500k rows which is like past 7 days worth of data in my database
Second package while getting the Data from Temp table gets hangs up , just sits there in Yellow color no error message is displayed , this has been waiting for more than 12hrs,
I run a stored procedure to get the data from Temp table to main table,
Here is the Syntax of SP
ALTER PROCEDURE [dbo].[Proc_DiffData_EC_JOB]
AS
BEGIN
SET NOCOUNT ON;
ALTER PROCEDURE [dbo].[Proc_DiffData_EC_JOB]
AS
BEGIN
SET NOCOUNT ON;
Select * from dbo.DW_T_EC_JOB_TEMP where dbo.DW_T_EC_JOB_TEMP.EC_STEP_ID not in
(select EC_STEP_ID from DW_T_EC_JOB where (dbo.DW_T_EC_JOB.EC_STEP_ID = dbo.DW_T_EC_JOB_TEMP.EC_STEP_ID))
ENDEND
I tried to google around and tried different options
option 1 , tried to remove * and added all the columns in the select list
option 2 , tried to change the Transaction property to required for the dataflowtask
Option 3, Created the tables and packages , Jobs all over again 🙁
Both of them did not work ,
This packages actually worked fine when i initially created, i dont undertand what happened last 3-4 days back it started hanging , and all other tables which are dependent on this paticular table also started hanging and this package runs continously for days,
I have gone through the posts given in sql server 2005 , but i work in 2008...
Any suggestion is highly appreciated ...
Please Help ,
March 18, 2010 at 11:13 pm
Try....
Select * from dbo.DW_T_EC_JOB_TEMP t
where
t.EC_STEP_ID not in
(select j.EC_STEP_ID from DW_T_EC_JOB j
INNER JOIN DW_T_EC_JOB_TEMP t
ON
j.EC_STEP_ID = t.EC_STEP_ID)
--instead of--
--where (dbo.DW_T_EC_JOB.EC_STEP_ID = dbo.DW_T_EC_JOB_TEMP.EC_STEP_ID))
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 18, 2010 at 11:52 pm
It did not work :crying:.......
Again hanged..........every time it justs hungs up at the 2271 rows..in debugging mode........just stays yellow, exactly when it reaches 2271 rows, no error message is displayed, i checked my data manually in the temp table, and looks fine , i mean all the datetime fields having datetime data, nvarchar having char data etc......
I cannot understand what is happening after 2271 rows...is there any way where i can see at which column debugging hanged?:(
March 18, 2010 at 11:56 pm
As a test, add a TOP 2270 in you're select statement, see if it works?
If you confirm that you're data is in fact healty and normal, it's a bit strange to fail at the same pace everytime...
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 19, 2010 at 12:04 am
Yes, i selected Top 3000 rows and saw if there is any starnge data , and found that everything seems to be as expected, actually this number is getting changed my package gets past 7 days worth of data , so when i run the package today , at 2271 rows it gets hanged, yesterday same thing happened at 2230 rows for the same table ...
the data is fine , because if at all there is any crappy data (data mismatch with the datatype) in there , data should not enter into temp table , but all the data enters into the temp table pretty fast without any issues, the problem is when the data is entering into main table from temp table...
Both Temp table and Main table's DDL is same......no difference at all........
March 19, 2010 at 12:06 am
interesting...
by how much does you tempdb and log grow during this process?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 19, 2010 at 12:08 am
I have Control Flow SQL task which deletes all the Temp table Data everytime package begins.........
March 19, 2010 at 12:12 am
I meant you're system TEMPDB, not the custom temp table..
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 19, 2010 at 12:14 am
Not sure about it ,How do i check that?
I am new to SQL SERVER, please help me if am missing something here.....
March 19, 2010 at 12:19 am
before starting the job, run the following:
DBCC sqlperf(logspace)
after starting the SSIS job, execute it again, every 10 seconds, and see if there is a significant change in the size of tempdb log, and the space used on the tempdb log file.
and again as soon as the job starts to hang.
post result of before and after please.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 19, 2010 at 12:42 am
Attached is the Excel File with Results........
March 19, 2010 at 1:09 am
downloads a "test.zip" and contains xml's?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 19, 2010 at 1:22 am
Not Sure whats wrong with the file ,
here are the results, before, while and after execution os the package, i dont see much difference
Database NameLog Size (MB)Log Space Used (%)Status
tempdb8.17968849.379180
tempdb8.17968853.008590
tempdb8.17968858.022920
March 19, 2010 at 1:26 am
I see what you mean.
do you have indexes on those 2 tables?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 19, 2010 at 1:29 am
no, i dont have any indexes........
should i or should'nt i have??
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply