Urgent

http://blogs.acceleration.net/ryan/archive/2006/05/12/2870.aspx

We already enabled Auto Update Statistics all the production databases.  I getting this issue on doing executing Selectinto statement to insert the data into # table using a big query.

Moreover This was not happend consistenly.

You can also turn on the trace flag (see Books Online) to get more info about the deadlocks.

or could be your temp db is out of space

it is possible the problem is due to parallelism and contention between threads, try using MAXDOP 1 for the query. can you also check that there are no other tasks that may interfere with your select/into query? if it's an option at all, consider rewriting a 10m row select/into as smaller batches (for many good reasons, log/temp space is one) - partitioning inserts by primary key

that expert is probably from the 70s (okay..SQL 7.0). SQL 2000 solved that problem long ago.

We received the Tempdb Log File full at one point of time. Even though we are having enough space in the drive. We changed the default value for the log file space into 1 GB. So the Tempdb issue was solved.  Even then the deadlock was appears again.

We also reindexing all the production tables daily before doing this data load.

As I suggested, shorten your rollbacks segments...The following code breaks into 10000 chunks

 Instead of

<<delete from yourtable where <condition matches 10 million records>>>

use…

-- set rowcount to delete sets of 10 000 records (use a smaller subset size if you think it's better)
set rowcount 10000

-- start delete
delete from yourtable where <condition matches 10 million records>
while @@rowcount > 0 -- loop until there's no record left to delete
begin
    -- after each delete there will be an implicit commit
    delete from yourtable where <condition matches 10 million records>
end


Hope this helps...

instead of

select f1, f2, f3..
into #abc
from abc where ...

change it to

-- get structure created
select f1, f2, f3..
into #abc
from abc where 1=0

-- temp index to help keep track of where we are up to
create index #keyid on #abc(f1)

-- variable for where we are up to
declare @f1 int set @f1 = 0

-- start inserting in batches of 50000
insert into #abc (f1, f2, f3..)
select top 50000 f1, f2, f3..
from abc where ...
AND f1 > @f1

-- continuing from previous position
while @@rowcount > 0 begin
    select @f1 = max(f1) from #abc   -- update progress
    insert into #abc (f1, f2, f3..)
    select top 50000 f1, f2, f3..
    from abc where ...
    AND f1 > @f1
end

Hi,

imitchie:
>> that expert is probably from the 70s (okay..SQL 7.0). SQL 2000 solved that problem long ago. <<

I've openned a question to discuss this here:
https://www.experts-exchange.com/questions/22988582/select-into-TempTable-vs-Create-TempTable-insert-TempTable-select.html

Regards
  David