Post reply

locks on system tables in tempdb

  • August 25, 2004 at 10:00 am

    #85233

    An application that hits my SQL Server is putting IX locks on system tables, and X locks on pages, on those same system tables in tempdb.

    SQL 2000 - SP3a....

    Usually, the locks get released - but not always.

    I have a trace running, but I can't tell what part of their code is placing these prohibitive locks on tempdb.  Does anyone have an idea how I can figure this out?

    Thanks!

  • August 25, 2004 at 10:17 am

    #520562

    Trace or constant sp_locks should do it. This was a problem in previous versions, but table creation and other ops were supposed to not lock tables in 2000. Are you getting blocks?

  • August 25, 2004 at 10:22 am

    #520565

    Yes.  We're getting blockers.  It seems to happen only when the locks are not released.  When things are working the way the app developers intended, then no one realizes that they're placing locks on tables in tempdb.

    I'm thinking this is a problem with the application not completing some process.  Because when we look at sysprocesses for the blocker spid, open_tran = 1.  This will linger until we kill the process.

    Thanks.

  • August 25, 2004 at 11:23 am

    #520579

    http://support.microsoft.com/default.aspx?scid=kb;en-us;159747&Product=sql2k

  • August 25, 2004 at 12:05 pm

    #520592

    Thanks Allen.  I'm showing this to the application development team.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply