many tempdb locks?

hi all,

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:

697200EXT1:156760 XGRANT
697200EXT1:94896 XGRANT
697200EXT1:132224 XGRANT
697200EXT1:140488 XGRANT
697200EXT1:181552 XGRANT
697800DB SGRANT
697200EXT1:165280 XGRANT
697200EXT1:127888 XGRANT
697200EXT1:173544 XGRANT
697200EXT1:152624 XGRANT
697200EXT1:160888 XGRANT
697200EXT1:144616 XGRANT
697200EXT1:198336 XGRANT
697200EXT1:107296 XGRANT
697200EXT1:99176 XGRANT
697200EXT1:169344 XGRANT
697200EXT1:115704 XGRANT

I am wondering what action is it doing, creating temp tables?? many
thanks.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/techin.../2000/books.asp|||Go to this link and follow the instructions provided. It has proven
INVALUABLE to me!

http://support.microsoft.com/defaul...kb;en-us;328551

Must have at least .818 patch installed.
Must add the -T1118 startup parameter.
Split the tempdb data file into equal sized files and uncheck the
'autogrow' function.sql