Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    13

    Unanswered: Problem in recovering "tempdb" space

    Hi,

    I am using a SQL6.5 server for my application. My application involves thousands of operations (insert, modify and deletes) on one of the databases i.e. database called 'mydb'; with triggers installed for the operations performed on the tables of 'mydb' database. The 'tempdb' of my server is consistently growing due to these operations. The data space available on the 'tempdb' database is depleting extremely fast. I would like to know what is the way to control this? I suppose my database server will run into trouble if my tempdb database runs out of space.

    Please advise me on this.
    Thanks,
    Siddharth.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    If temdb runs out of space the server will probably crash.
    tempdb should be set to truncate the log on checkpoint.
    Check which tables are in existence in tempdb - it sounds like either you have very long running queries which are holding large tables in tempdb or long running transactions - both indications of poor code / design.

    try dbcc opentran (dbname) on all databases - transactions should only last seconds - if you have long running ones find out why.
    Do select * from tempdb..sysobjects and check what the owners of the tables are doing - you'll probably find that tables are being created but not dropped or that unneeded sorts are being performed.

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Sometimes it's not just transactions "BEGIN TRAN...COMMIT TRAN" that you need to look at. Any queries that require working space will eat up tempdb. Look for queries that use DISTINCT, GROUP BY, ORDER BY, subSELECTS. This queries require SQL Server to create temporay working tables that will be placed in tempdb.

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    But these will all create transactions and be caught by the dbcc opentran.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •