many tempdb locks?

[New MSSQL DBA]

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:

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.

[Erland Sommarskog]

New MSSQL DBA (bo*******@gmai l.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, es****@sommarsk og.se

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

[joshsackett]

Go to this link and follow the instructions provided. It has proven
INVALUABLE to me!

http://support.microsoft.com/default...b;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.