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 Identit
y
>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.
> ========================================
==============
>|||Hi Dave,
The transaction isolation level of your web application depends on your
coding. If you did not set transaction isolation level explicitly, by
default, it was READ COMMITTED.
Since there was no deadlock information in the error log, I think that this
issue should be related to lock escalation. I noticed that your source
table had about 0.5 million records and your DTS inserts batch size was
5000, it is very common that a large batch insert or update will cause
table level lock which is incompatible with shared lock. In this case, your
application could not read any data from the table until the bulk
insert/update finished.
I recommend that you schedule a time to run your DTS job when your web
application is not busy or has no workload.
You may also consider transfer the data incrementally, each time
transferring a few records to the destination table.
The methods can ensure that there is little impact to your application
performance.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
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.
========================================
==============sql