-
tempdb locks
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.
-
tempdb locks (reply)
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|