Re: Missing rows from Transform - Intermittent Issue??
From: Jiamin Zeng (jzeng_at_solucient.com)
Date: 04/23/04
- Next message: Obaid: "Job status on Mainfram"
- Previous message: Kasp: "Building DTS package through scripts?"
- In reply to: Pete K.: "Re: Missing rows from Transform - Intermittent Issue??"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 23 Apr 2004 09:48:44 -0700
Hi, I am experiencing the exact same problem with the
server memory while running a DTS package. However the
same DTS had been running for over a year. There has been
no change to the tables or the environment. It was
transfering 500 million rows without problem before, now
it dies after about 90 million. No error, no warning,
just hang. Bouncing SQL Server did not help. Nor did
rebooting Windows. Any suggestion will be greatly
appreciated. Thanks.
>-----Original Message-----
>Thanks for your reply. Turns out I am running out of
system memory on
>the server when running this package, so not all rows are
getting
>processed - but no error messages or indication of this
condition. I
>am able to get the package to process all rows if I
stop/start the SQL
>Server services just prior to running the package. BTW,
the source
>SQL for my Transform is joining across about 30
tables/views (which
>includes about 20 instances of one of the dimension
tables), so it's
>definitely eating up the memory.
>
>Any suggestions on what I should do in my DTS package to
better manage
>the server memory? I'm using the Fast Load option on the
Transform
>task - I've tried playing with some of the task options
(e.g., Fetch
>Buffer Size & Insert Batch Size), but I don't have a good
>understanding on the impact these options have.
>
>Thanks,
>Pete
>
>
>
>"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in
message news:<#EXvIuHGEHA.3252@TK2MSFTNGP11.phx.gbl>...
>> I do not think you are missing anything.
>>
>> are there any constraints on the destination? It is
the rowcount in the
>> destination you are using to determine how many rows
were moved or are you
>> looking at the GUI?
>>
>> What about using the ExecuteSQL task to assign the
value of COUNT(*) to a
>> Global Variable and then msgbox out that GV. Does that
give you the correct
>> rowcount?
>>
>> What SP of SQL Server + any hotfixes?
>>
>> Does the value of the amount of rows moved change every
time?
>>
>> Are you just doing a 1:1?
>>
>> What about on a smaller load? Same thing?
>>
>> --
>> --
>>
>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>> www.SQLDTS.com - The site for all your DTS needs.
>> I support PASS - the definitive, global community
>> for SQL Server professionals - http://www.sqlpass.org
>>
>>
>> "Pete K." <pfk92000@yahoo.com> wrote in message
>> news:63924075.0404012040.2c8f5a60@posting.google.com...
>> > Hi!
>> > I have been having an intermittent problem with a
Transform in one of
>> > my DTS packages that I'm stumped on - when I run the
Transform source
>> > query thru SQL Analyzer, I consistently get 443K rows
returned. When
>> > I execute the DTS package (with the exact same SQL in
the Trasnform),
>> > I *usually* only get about 101K rows processed - when
I run the
>> > package again, I might get 99K or 102K rows -
*occasionally* I do get
>> > the right number of rows loaded. This problem
happens whether I run
>> > the package interactively or thru a job. And there
are no error
>> > messages
>> >
>> > I'm guessing that there's some sort of temp space
issue. However,
>> > there's definitely enough disk space...the log file
is set for
>> > unlimited growth...I've shrunk the destination
database log file as
>> > well as the tempdb data and log files before running
the package. The
>> > only steps in the package are an Exec SQL task that
creates a view,
>> > and the Transform which references the view - each of
these steps is
>> > set to execute on the main package thread.
>> >
>> > What am I missing?? Any help on this issue would be
greatly
>> > appreciated!
>> >
>> > Thanks,
>> > Pete
>.
>
- Next message: Obaid: "Job status on Mainfram"
- Previous message: Kasp: "Building DTS package through scripts?"
- In reply to: Pete K.: "Re: Missing rows from Transform - Intermittent Issue??"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
