Re: Massive DTS job (5 days and i am nervous)

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 06/19/04 

Date: Sat, 19 Jun 2004 08:55:24 -0400

Inserts are done as a transaction. Therefore, you won't see them unless you
run the following:

set transaction isolation level read uncommitted
select count (*) from MyTable

-- or
select count (*) from MyTable with (nolock) 

-- 
   Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com/sql
.
"N" <raasen@hotmail.com> wrote in message
news:u0Xm7ufVEHA.3988@tk2msftngp13.phx.gbl...
The way this was written and i am using the current stored proc being
executed as an example is that no transactions are used. At the beginning of
the sp a set xact_abort on is set. This current SP has three insert into
select from statements. the first one seems to have finished and inserted
3.8million rows. Now i am going to assume it is busy with the second insert
into select from but the amount of rows in the table it is inserting into is
not moving from the 3.8 million mark.....??????? I would have thought i
would be able to see the rows being inserted one by one as the data is
returned?
"N" <raasen@hotmail.com> wrote in message
news:#Y6MRnfVEHA.212@TK2MSFTNGP12.phx.gbl...
> Hi
>
> I have inherited a DTS job that takes about 5 days to run.
> This job is made up of about 20 stored procedures and some vbscript and so
> on.
> There is a bit of a process log that is kept in a table which is basically
a
> log of a start of a stored proc and then logs an end time for the stored
> proc. The stored procs are made up of loads of sql statements. Is there
any
> way i could see which statement is being executed at the current point in
> time? dbcc inputbuffer just gives me the "exec stored_proc" command used
in
> the SQL task. I can see that something is happening as the disk space
usage
> is increasing and the CPUTime and DISKIo are going up for the spid
executing
> the sql task. There are also no blks for any of the processes so i am
> certain nothing is blocking anything. I however, would like to know which
> sql statement is being executed just to satisfy myself that this thing is
> not hanging on one sql statement the entire time....
>
> any ideas?
>
>

Relevant Pages

  • Re: DB API 2.0 and transactions
    ... CURRENT_TIMESTAMP within a transaction should be the same. ... manadate that CURRENT_TIMESTAMP in only evaluated once in each SQL ... transaction-initiating SQL statement takes place. ... src = self.__cnx.source ...
    (comp.lang.python)
  • Re: Massive DTS job (5 days and i am nervous)
    ... i remembered about profiler a bit too late. ... > Columnist, SQL Server Professional ... > log of a start of a stored proc and then logs an end time for the stored ... > not hanging on one sql statement the entire time.... ...
    (microsoft.public.sqlserver.programming)
  • Re: Massive DTS job (5 days and i am nervous)
    ... Columnist, SQL Server Professional ... log of a start of a stored proc and then logs an end time for the stored ... not hanging on one sql statement the entire time.... ...
    (microsoft.public.sqlserver.programming)
  • ADO.NET/C#/SqlTransaction/SqlCommand Object passing between functi
    ... I'm trying to wrap multiple SQL 2000 data updates within a single ADO ... separately my various routines involved with the big transaction. ... Let's call the starting routine R1. ... R1, R3 and the stored proc run with no errors, but the data is never ...
    (microsoft.public.data.ado)
  • Re: Massive DTS job (5 days and i am nervous)
    ... Our development transform server and our production transform server are ... > Columnist, SQL Server Professional ... > log of a start of a stored proc and then logs an end time for the stored ... > not hanging on one sql statement the entire time.... ...
    (microsoft.public.sqlserver.programming)