January 3, 2003 at 1:58 pm
The title says it all, almost. We've got a situation here that is totally beyond my understanding. I'm the lead DBA, but I don't know DTS at all well and the lead DTS person is out, mgmt wants this fixed, and it keeps recurring under different circumstances and I want to know once and for all what's the deal.
We have a series of DTS packages used for our warehouse solution. I don't know all the ins and outs of how it all works at the detail level, or what's important and what's smoke and mirrors; please ask for any missing details.
There's one step wherein a query is run, data is extracted, transformed, and loaded into a table. The table being populated is included in the query. Today--but not for the prior four plus months--I get a situation where this step is blocking itself. By this, I mean that the process hangs, doing nothing; in Enterprise Manger, when viewing Current Activity and Current Users, there are two connections present for this process, and one connection is blocking the other. The blocking connection is running the SELECT query, and the blocked connection is performing a BULK INSERT. When I run SQL Profiler while this is going on, it shows that the SELECT query starts at time X, and the INSERT starts .660 seconds later -- before the SELECT statment has completed.
Why does this happen? Should not the insert be waiting until all the data to be inserted as been defined? I mean, huh?
In another thread here on SQL Central, I found a link to a MS knowledgebase article [ http://support.microsoft.com/?kbid=318819 ]; I performed the recommended steps in there (mess with workflow properties / set "Execute on main package thread" On), but this has had no effect.
This has occured before in the past, in different steps across our ETL process. We've waved the dead chicken at it many times to make it go away, but we've never really understood why it went wrong and why what we did fixed anything. And now our main chicken waiver is gone, and I'm flailing about helplessly. (Don't worry about the short term, I'll tell mgmt that they can wait 'til Monday and like it.) For the long term, any ideas out there? What the heck is SQL doing?
Philip Kelley
January 3, 2003 at 2:27 pm
Hi Phillip -
Just a couple of questions as I read this.
Is there an unusual amount of data in the table today compared to other days?
Are you verifying there is room in the database to add these transformed records to the database?
M
Michelle
January 3, 2003 at 2:39 pm
Yes, there is definitely more data returned by the initial select statement than usual. It's not the most ever, but definitaly in the top 5%. (The query itself takes "1" second to run, according to query analyzer.)
Thanks for your quick reply!
Philip
January 3, 2003 at 4:33 pm
Is there a chance that the database is growing in size automatically? I've seen some processes appear to "hang" while the DB grows in size. Just a stab in the dark.
Also you might want to run DBCC INPUTBUFFER commands on the 2 spids while the blocking is happening to verify what is running.
-Dan
-Dan
January 3, 2003 at 4:47 pm
The database may well be auto-growing, but that wouldn't take more than a few minutes (if that), and shouldn't be replicable. I've hit the hanging problem six times over today...
Also, I believe that DBCC INPUTBUFFER is what is used by Enterprise Manager when you select "properties" for a connection in the Current Activity screen -- only returns the first 255 characters, right? If so, for the blocking connection, we get the first 255 of the SELECT query, and for the blocked connection we get nothing--which was darn confusing until I ran it through SQL Profiler and saw the BULK INSERT command (after which I noticed that the "Command" column Current ACtivity was set to "BULK INSERT").
To mention, we just finished doing the problem step's insert by hand, followed by running each subsequent step of the DTS package one by one. I know it's going to pop up again some day, and I'd really love to know what's going on and how to stop it!
Philip
January 6, 2003 at 9:22 am
I have experienced similar problems with DTS packages in the past, but have never looked into them in such detail as you appear to have done.
One thing that I have found which normally solves the problem is to limit the maximum number of tasks executed in parallel to 1. (This setting can be found in the Package Properties).
I think DTS packages can sometimes trip themselves up by running too mach tasks in parallel, but purhaps someone with more DTS experience and confirm/deny this.
Hope this helps.
Edited by - paulhumphris on 01/06/2003 09:24:06 AM
January 6, 2003 at 3:59 pm
I actually recall this. The original designers of the system learned about this the usual (painful) way, meaning all our DTS packages have had the "maxmimum tasks executed in parallel" set to 1 almost since inception (about a year ago).
Thanks for all the advice so far. Just knowing other people have hit this helps!
Philip
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply