This is crazy - DTS 'hangs' on the 1st of Month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

This is crazy - DTS 'hangs' on the 1st of Month

Author

Topic

smueller72
Starting Member

4 Posts

Posted - 2007-02-07 : 17:19:27

Hi all - I'm posting/reposting a question I posted on Google Groups hoping I might get a better response here. This one has me baffled.

--------------------------------------------------------------------

Hi all - I've got the craziest situation going on with one of my DTS
packages. Specifically, I have a package that includes a number of
Data Driven Query tasks that hangs on one of the tasks only after the
first of the month. I will run without a hitch from about the 6 or
7th of a given month but between the 1st and the 6th or 7th it hangs.
I know this sounds crazy. I've thrown the specific package into the
debugger and what is happening is that it processes the first record -
does an Update Query - gets to the 'End Function' statement and then
just sits. Thinking it may be related to something in the data that
is only present after the 1st of a new month, I went ahead and
resorted the source query so that it processes a different record and
the same issue occured. I've also checked for any scheduled processes
that I have running only on the first of the month that may have a
record/table/db locked but I haven't been able to identify anything.
As the subject line suggests, I know this sounds crazy and it is
certainly driving me crazy but any ideas/assistance in debugging would
be greatly appreciated.

Regards,
Steve Mueller

smueller72
Starting Member

4 Posts

Posted - 2007-02-07 : 17:20:40

and these are the couple follow up posts I've made after doing some experimenting

-------------------------------------------------------------------
sp_who2 doesn't show any blocking.

I've changed the the package to run on a single thread with no change
in results.

I've also changed the package to utilize a DTSLookup to run the Update
query(albeit a rather large update query) and I get the same results -
i.e. processing just sits on the DTSLookup line. Taking it a step
further, I ran the same update query outside of the DTS in Query
Analyzer and the query was successful. I'm not sure where to go from
here. As I said, this is crazy - and give it another day and it will
probably start running without a hitch. My problem is that I need the
data updated asap as it feeds some important weekly reports that users
are waiting on. Back to the drawing board - any other suggestions
would be greatly appreciated.

smueller72
Starting Member

4 Posts

Posted - 2007-02-07 : 17:21:20

-------------------------------------------------------------------

I just ran the sp_who2 proc while the package was running and I
noticed the two lines that seemed odd. The first line(spid 55) is
appears to be performing a SELECT and blocking the second line(spid
56) which appears to be attempting to perform the update query. Am I
reading this correctly? If so, is this the cause of my issue and, if
so, I'm a bit confused as to why the SELECT statement would be running
- it's as if the subsequent SELECT is occuring prior to the Update
query finishing. If this is my issue, what can be done to resolve?
Per my initial post, this process has run fine until recently when
this began occuring on the first of every month. Any help would be
appreciated.

55 RUNNABLE SELECT 297 50 2/7/2007
10:28 DTS Designer 55
56 sleeping 55 UPDATE 0 0 2/7/2007
10:28 DTS Designer 56

smueller72
Starting Member

4 Posts

Posted - 2007-02-07 : 17:21:46

Just a bit more info. I've seperated a chunk of the code into a
seperate package not using DDQ. When I run it in debug mode and step
through it works fine. However, if I execute the step it hangs.
Looking at the sp_who2 results I notice entries similar to the above.
However, I do notice that there is a SELECT for every DTSLookup that
is contained in the DTS and I see that the INSERT is blocked by one of
the SELECT's. So it appears that blocking is my problem but I'm not
too sure what the cause of this is and, more importantly, how to
resolve. Does this sound like it could be my problem? Any reason why
this would occur intermittently(sp)? Any insights on how to resolve
would be appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources