Mass locks in tempdb

• Roust_m

  SSCoach

  Points: 17410

  More actions

  October 7, 2003 at 7:21 am

  #162559

  Hi!

  Few days ago a very long block chains began to happen very often.

  I noticed that this happens at the moments when one or two processes

  create too many locks in tempdb (up to 60000 and even more). (99% are

  locks on tables syscolumns, sysindexes & sysobjects in tempdb) After

  that everything nearly stops. /* to determine this I run this query:

  select req_spid, count(*) as Numb from syslockinfo (nolock)

  group by req_spid

  order by Numb desc */

  This is a sample output:

  ====================

  
  
  req_spid,Numb
  
  169,     58437
  
  617,     15624
  
  980,     404
  
  1003,    18
  
  58,      18
  
  529,     18
  
  281,     13
  
  520,     13
  
  573,     13
  
  542,     13
  
  198,     6
  
  114,     6
  
  79,      6
  
  333,     6
  
  91,      5
  
  

  =====================

  What can be the cause of this?

  Thanks.

• October 10, 2003 at 8:00 am

  This was removed by the editor as SPAM

• vambati

  SSCrazy

  Points: 2769

  More actions

  October 10, 2003 at 8:09 am

  #477868

  U can try to increase the no of locks . my guess would be too many processes starting at the same time, i mean jobs. SQL server has a known bug which MSFT is working on when too many jobs run at the same time that access sys tables(updates).

• NPeeters

  SSChampion

  Points: 12558

  More actions

  October 10, 2003 at 8:55 am

  #477876

  What has changed before this started occuring?

  Don't know if this could be the problem, but do you use Autogrow on the TempDB or on the production databases?

  Maybe set the 'increment' to a higher value or disable it all together and allocate a sufficient amount of disk.