>>Now, I can understand the deadlock mechanism. but where the source and destination databases are concerned, I AM THE ONLY USER. Can a SP deadlock with itself???
>
>SQL Server is supposed to recognize that a lock is being held by the same connection and ignore it. The only time I seen strange things like this is when I was creating temporary tables using SELECT INTO. SELECT INTO will place locks on some of the system tables in tempdb. If you issue the SELECT INTO within an explicit transaction, SQL Server will hold the locks until the transaction is terminated. The problem is that the locks interfer with the CHECKPOINT process.
>
>Are you using SELECT INTO?
>
>-Mike

Hi,

Wow you must be psychic, yes there is a SELECT INTO #TempTable inside my main loop, and I have an explicit transaction also around my main loop. I had wondered if something screwy was going on around tempdb, as this is the only thing that might be accessed by other users.

When I lokk further into this, I see that the deadlock occurs on the FETCH line for my main processing loop - This doesn't seem to point to tempdb though!?!

p.s. While I was screwing about with tempdb, I managed to increase the log file size from 1mb to 500mb, and the fun part is that it won't let me shrink it again. Any idea how I can recover that 0.5Gb

p.p.s If I didn't mention before, all this is happening in SQL Server 7.0