tempdb locks

[Oleg V. Mouratov]

Good day.
I have a same problem with tempdb syscolumns locking. I call a
procedure thats creates a temporary tables fill its and try to insert a results into a table
which have a trigger which try to create a temporary tables.
Sometimes this work fine, but sometimes the server is hangs up. The sp_who shows next:

spid status loginame hostname blk dbname cmd
------ ---------- ------------ ------------------------------------ ----- ---------- ------------------------------------------------
1 sleeping sa 0 master MIRROR HANDLER
2 sleeping sa 0 master LAZY WRITER
3 sleeping sa 13 tempdb DUMP TRANSACTION
4 sleeping sa 0 master RA MANAGER
13 sleeping sa PROG1 0 SHOPNEW INSERT
14 runnable sa PROG1 0 master SELECT

The sp_lock procedure shows next:
............................ (I cut a big list)
13 Ex_page-blk 3 316 tempdb
............................

After that I can't make anything with hempdb (use sp_who2 or sp_lock2 or any other) and
can't a KILL 13. I also can't stop a service SQLServer.exe I can OLNY restart the NT...

Note: I tried to use a begin tran ... end tran. No effects reached.
And I can't understand why the server hang's up when he want...

I have a 20000 LE Threshold maximum.

[Paul Ross]

Sounds like you procedure is locking tempdb and stopping the trigger from running. Have you read Books Online - Whats New in SQL Server 6.5 - Data Definition Language in Transactions?

On 12/29/98 7:03:44 AM, Oleg V. Mouratov wrote:
> Good day.
I have a same problem with tempdb syscolumns locking. I call
> a
procedure thats creates a temporary tables fill its and try to insert a
> results into a table
which have a trigger which try to create a temporary
> tables.
Sometimes this work fine, but sometimes the server is hangs up.
> The sp_who shows next:

spid status loginame hostname
> blk dbname cmd
------ ---------- ------------
> ------------------------------------ ----- ----------
> ------------------------------------------------
1 sleeping sa
> 0 master MIRROR HANDLER
>
2 sleeping sa 0
> master LAZY WRITER
3 sleeping sa
> 13 tempdb DUMP TRANSACTION
4 sleeping sa
> 0 master RA MANAGER
>
13 sleeping sa PROG1 0 SHOPNEW
> INSERT
14 runnable sa PROG1 0 master
> SELECT

The sp_lock procedure shows next:
>
............................ (I cut a big list)
13 Ex_page-blk
> 3 316 tempdb
>
............................

After that I can't make anything
> with hempdb (use sp_who2 or sp_lock2 or any other) and
can't a KILL
> 13. I also can't stop a service SQLServer.exe I can OLNY restart the
> NT...

Note: I tried to use a begin tran ... end tran. No effects
> reached.
And I can't understand why the server hang's up when he
> want...

I have a 20000 LE Threshold maximum.