Loading ...
Sorry, an error occurred while loading the content.
 

SQL 2000 tempdb transaction log issue

Expand Messages
  • sqlannd
    Good afternoon, Recently I seem to be getting attacked by the plague of the tempdb creatures from the deep, first with Service Broker in SQL 2005 and now with
    Message 1 of 4 , Jan 3, 2007
      Good afternoon,

      Recently I seem to be getting attacked by the plague of the tempdb
      creatures from the deep, first with Service Broker in SQL 2005 and
      now with the tried and true SQL 2000 tempdb in an OLTP environment.

      Can any of you offer a suggestion about how I can troubleshoot and
      fix the issue we are having?

      Currently the transaction log for tempdb on our busiest production
      server continues to grow at a very fast rate and eventually fills up
      the disk. It acts as if checkpoints are not being issued, like
      transactions are not getting commited, etc. We've been searching
      desperately through the code not seeing where a problem could be,
      and monitoring in various ways has not indicated where the problem
      is. Any ideas?

      Any and all suggestions are appreciated.

      Thanks,
      Ann
    • Asim Ghani
      You can try querying the tempdb sysobjects table to look for temp objects (starting with #) and then determine the size of each object it s creation date and
      Message 2 of 4 , Jan 3, 2007
        You can try querying the tempdb sysobjects table to look for temp objects (starting with #) and then determine the size of each object it's creation date and last reference date.  That should give you some idea as to which object(s) are using large amount of space in tempdb.
         
        I use Diagnostic Manager which gives me most of this info.
         
        Hope that helps.
         
        -asim ghani

        ----- Original Message ----
        From: sqlannd <sqlannd@...>
        To: tampasql@yahoogroups.com
        Sent: Wednesday, January 3, 2007 5:24:11 PM
        Subject: [tampasql] SQL 2000 tempdb transaction log issue

        Good afternoon,

        Recently I seem to be getting attacked by the plague of the tempdb
        creatures from the deep, first with Service Broker in SQL 2005 and
        now with the tried and true SQL 2000 tempdb in an OLTP environment.

        Can any of you offer a suggestion about how I can troubleshoot and
        fix the issue we are having?

        Currently the transaction log for tempdb on our busiest production
        server continues to grow at a very fast rate and eventually fills up
        the disk. It acts as if checkpoints are not being issued, like
        transactions are not getting commited, etc. We've been searching
        desperately through the code not seeing where a problem could be,
        and monitoring in various ways has not indicated where the problem
        is. Any ideas?

        Any and all suggestions are appreciated.

        Thanks,
        Ann



        __________________________________________________
        Do You Yahoo!?
        Tired of spam? Yahoo! Mail has the best spam protection around
        http://mail.yahoo.com
      • sqlannd
        Thanks so much for you quick response. I will try that. - Ann ... objects (starting with #) and then determine the size of each object it s creation date and
        Message 3 of 4 , Jan 3, 2007
          Thanks so much for you quick response. I will try that.

          - Ann

          --- In tampasql@yahoogroups.com, Asim Ghani <asim_ghani@...> wrote:
          >
          > You can try querying the tempdb sysobjects table to look for temp
          objects (starting with #) and then determine the size of each object
          it's creation date and last reference date. That should give you
          some idea as to which object(s) are using large amount of space in
          tempdb.
          >
          > I use Diagnostic Manager which gives me most of this info.
          >
          > Hope that helps.
          >
          > -asim ghani
          >
          >
          > ----- Original Message ----
          > From: sqlannd <sqlannd@...>
          > To: tampasql@yahoogroups.com
          > Sent: Wednesday, January 3, 2007 5:24:11 PM
          > Subject: [tampasql] SQL 2000 tempdb transaction log issue
          >
          > Good afternoon,
          >
          > Recently I seem to be getting attacked by the plague of the tempdb
          > creatures from the deep, first with Service Broker in SQL 2005 and
          > now with the tried and true SQL 2000 tempdb in an OLTP environment.
          >
          > Can any of you offer a suggestion about how I can troubleshoot and
          > fix the issue we are having?
          >
          > Currently the transaction log for tempdb on our busiest production
          > server continues to grow at a very fast rate and eventually fills
          up
          > the disk. It acts as if checkpoints are not being issued, like
          > transactions are not getting commited, etc. We've been searching
          > desperately through the code not seeing where a problem could be,
          > and monitoring in various ways has not indicated where the problem
          > is. Any ideas?
          >
          > Any and all suggestions are appreciated.
          >
          > Thanks,
          > Ann
          >
          >
          >
          >
          > __________________________________________________
          > Do You Yahoo!?
          > Tired of spam? Yahoo! Mail has the best spam protection around
          > http://mail.yahoo.com
          >
        • Chris Leonard
          Ann, Since the problem is a tempdb *log* on SQL 2000, you should also look for long-running transactions. Long-running transactions in any database could, I
          Message 4 of 4 , Jan 4, 2007
            Ann,

            Since the problem is a tempdb *log* on SQL 2000, you should also look for
            long-running transactions. Long-running transactions in any database could,
            I believe, cause this behavior in a SQL 2000 tempdb. Not saying that this
            IS the problem, just that it's a natural thing to check. This command will
            check all your databases at once:

            exec sp_msforeachdb 'print ''database ?:'' dbcc opentran(?)'

            Cheers,
            Chris
          Your message has been successfully submitted and would be delivered to recipients shortly.