Hourly Batch Process Locking My Database

Hello everyone,

I have around 20 reports in an ASP web-application which connects to a
SQL Server 2000 dB, executes stored procedures based on input
parameters and returns the data in a nice tabular format.

The data which is used in these reports actually originates from a 3rd
party accounting application called Exchequer. I have written a VB
application (I call it the extractor) which extracts data from
Exchequer and dumps the same into the SQL Server dB every hour. The
running time for the extractor is an average of 10 minutes. During
these 10 minutes, while the extractor seems to run happily, my ASP
web-application which queries the same dB that the extractor
application is updating becomes dead slow.

Is there anyway I can get the extractor to be nice to SQL Server and
not take up all its resources so that the ASP web-application users do
not have to contend with a very very slow application during those
times?

I am using a DSN to connect to the dB from the server that runs the
web-application and well as the other server which runs extractor.
Connection pooling has been enabled on both (using the ODBC
Administrator). The Detach Database dialog gives me a list of open
connections to the dB. I have been monitoring the same and I have
noted 10-15 open connections at most times, even during the execution
of extractor.

All connection objects in the ASP as well as VB applications are
closed and then set to nothing.

This system has been in use from 2002. My Data file has grown to 450MB
and my Transaction Log is close to 2GB. Can the Transaction Log be a
problem. For some reason, the size of the Transaction Log does not go
down even after a complete dB backup is done. Once a complete dB
backup is done, doesn't the Transaction Log lose its significance and
can be actually deleted? Anyway this is another post I'm doing today
to the group.

In the extractor program,
1) I create a temporary table
2) I create an empty recordset out of the table
3) I loop through the Exchequer records using Exchequer's APIs, adding
records into the recordset of the temporary table as I go along.
4) I do an UpdateBatch of the Recordset intermitently
5) I open an SQL Transaction
6) I delete all records from the main table
7) I run a INSERT INTO main_table SELECT * FROM #temp_table
8) I commit the transaction

I hope that the information is sufficient

Thanks
Sam