|
Home > Archive > Microsoft SQL Server forum > June 2005 > many tempdb locks?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
many tempdb locks?
|
|
| New MSSQL DBA 2005-06-30, 3:23 am |
| 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:1567
60 X GRANT
697 2 0 0 EXT 1:9489
6 X GRANT
697 2 0 0 EXT 1:1322
24 X GRANT
697 2 0 0 EXT 1:1404
88 X GRANT
697 2 0 0 EXT 1:1815
52 X GRANT
697 8 0 0 DB S GRANT
697 2 0 0 EXT 1:1652
80 X GRANT
697 2 0 0 EXT 1:1278
88 X GRANT
697 2 0 0 EXT 1:1735
44 X GRANT
697 2 0 0 EXT 1:1526
24 X GRANT
697 2 0 0 EXT 1:1608
88 X GRANT
697 2 0 0 EXT 1:1446
16 X GRANT
697 2 0 0 EXT 1:1983
36 X GRANT
697 2 0 0 EXT 1:1072
96 X GRANT
697 2 0 0 EXT 1:9917
6 X GRANT
697 2 0 0 EXT 1:1693
44 X GRANT
697 2 0 0 EXT 1:1157
04 X GRANT
I am wondering what action is it doing, creating temp tables?? many
thanks.
| |
| Erland Sommarskog 2005-06-30, 7:24 am |
| 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
| |
| joshsackett 2005-06-30, 9:23 am |
| 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.
|
|
|
|
|