DTS Copy locks web

I built a simple DTS job to copy the contents of a table in a SQL Server
2000 database from one server to another.
The transformation is a simple column copy from source table to destination
table.
I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identity
Insert and Always Commit Final Batch (inerst batch size=5000).
The source table has about 0.5 million records
As soon as I start the DTS job, any web page with a database connection
freezes up (i.e. you cannnot load it into a browser)
I do not have the Table Lock option set in the DTS package.
Does anyone have any ideas why a copy package would lock up the application
like this?
On Sat, 11 Aug 2007 08:44:51 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:

>I built a simple DTS job to copy the contents of a table in a SQL Server
>2000 database from one server to another.
>The transformation is a simple column copy from source table to destination
>table.
>I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identity
>Insert and Always Commit Final Batch (inerst batch size=5000).
>The source table has about 0.5 million records
>As soon as I start the DTS job, any web page with a database connection
>freezes up (i.e. you cannnot load it into a browser)
>I do not have the Table Lock option set in the DTS package.
>Does anyone have any ideas why a copy package would lock up the application
>like this?
Is the DTS package copying from or to the same table the pages are
accessing?
J.
|||It copies from the table that the web page is accesssing.
But a copy is just a read.
Why would this apparently lock the table?
|||On Sat, 11 Aug 2007 18:35:05 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:

>It copies from the table that the web page is accesssing.
>But a copy is just a read.
>Why would this apparently lock the table?
Because the web page connection has an isolation level of repeatable
read or serializable. Yes, it's excessive caution, but I suspect it's
the answer.
J.
|||Hi Dave,
I understand that when you executed your SQL 2000 DTS package in parallel
with your web application, all the web pages that used a database
connection froze up.
If I have misunderstood, please let me know.
From the issue appearance, it seemed that a deadlock was caused. To narrow
down this issue, I would like to collect more information from you:
1.What is the transaction isolation level of your web application?
2.What is the transaction isolation level of your DTS package?
Open your DTS package in Enterprise Manager, right click the panel in DTS
package designer, select "Package Properties", switch to the Advanced tab,
check the Transaction isolation level.
3. Refer to this article to enable the trace flag T1204 and T3605 so that
you can get more information in the error log.
SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/kb/832524/en-us
After the above operation, run your DTS package again to reproduce your
issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
error logs for further research.
By default, the SQL error logs are located in the folder
%ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
Look forward to your response and we are glad to work with you for further
research.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Thanks Charles.
The DTS package resides on the local server. I do not see any errors in
either the ERRORLOG or SQLAGENT files.
I uncheck the "Use Transactions" box in the DTS package properties but I
still get the app locking up until I cancel the DTS operation.
Can you tell me where I check for the transation isolation level on thec web
server?
Thanks
Dave
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:0OayAjW3HHA.360@.TK2MSFTNGHUB02.phx.gbl...
> Hi Dave,
> I understand that when you executed your SQL 2000 DTS package in parallel
> with your web application, all the web pages that used a database
> connection froze up.
> If I have misunderstood, please let me know.
> From the issue appearance, it seemed that a deadlock was caused. To narrow
> down this issue, I would like to collect more information from you:
> 1. What is the transaction isolation level of your web application?
> 2. What is the transaction isolation level of your DTS package?
> Open your DTS package in Enterprise Manager, right click the panel in DTS
> package designer, select "Package Properties", switch to the Advanced tab,
> check the Transaction isolation level.
> 3. Refer to this article to enable the trace flag T1204 and T3605 so that
> you can get more information in the error log.
> SQL Server technical bulletin - How to resolve a deadlock
> http://support.microsoft.com/kb/832524/en-us
> After the above operation, run your DTS package again to reproduce your
> issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
> error logs for further research.
> By default, the SQL error logs are located in the folder
> %ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
> Look forward to your response and we are glad to work with you for further
> research.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>