Posted by Erland Sommarskog on 06/30/05 13:40

New MSSQL DBA (boscong88@gmail.com) writes:
> we have a SQL2000SP3 runing in W2K3. The application is JDEdwards.
>
> recently I've observed that once in a while (about a few hours), there
> would be a process from the ERP application that holds quite a number
> of extent locks in tempdb, can be as high as 10000 locks. when I run
> sp_lock on that spid, it gives something like this:
>
> 697 2 0 0 EXT 1:156760 X GRANT
> 697 2 0 0 EXT 1:94896 X GRANT
> 697 2 0 0 EXT 1:132224 X GRANT
> 697 2 0 0 EXT 1:140488 X GRANT
> 697 2 0 0 EXT 1:181552 X GRANT
> 697 8 0 0 DB S GRANT
> 697 2 0 0 EXT 1:165280 X GRANT
> 697 2 0 0 EXT 1:127888 X GRANT
> 697 2 0 0 EXT 1:173544 X GRANT
> 697 2 0 0 EXT 1:152624 X GRANT
> 697 2 0 0 EXT 1:160888 X GRANT
> 697 2 0 0 EXT 1:144616 X GRANT
> 697 2 0 0 EXT 1:198336 X GRANT
> 697 2 0 0 EXT 1:107296 X GRANT
> 697 2 0 0 EXT 1:99176 X GRANT
> 697 2 0 0 EXT 1:169344 X GRANT
> 697 2 0 0 EXT 1:115704 X GRANT
>
>
> I am wondering what action is it doing, creating temp tables?? many
> thanks.

Yes, that is likely to be locks for allocating space for temp tables.
It could also be overflow space for table variables, internal work
tables for sort etc.

Since the locks linger, this happens within a non-committed transaction.
It could be because it's a single query, or a long user-defined transaction
that accumulates data.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]