Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: TEMPDB full and cursors

    We have a client who has reported that his tempdb fills up every couple weeks and that he needs to restart SQL Server 2005 to free up space.

    Here's his message:

    SQL Server Alert System: 'SQL Error - Severity 017 - Insufficient Resources' occurred on \\server-name

    This error means tempdb on server-name is full, but I'm showing 9369 MB in space available within tempdb.


    Then he goes on to tell me that the mountpoint where tempdb lives is full, and that neither the tempdb datafile or transaction log file can grow. Considering the sizes, there are huge, uncommitted transaction occurring.

    He has since come back and reported that when TEMPDB fills up, there are server-side cursors that have not been released.

    This is an application that is running successfully at 100+ client sites. Most run on SQL Server 2000, perhaps a dozen are on SQL Server 2005. We've not heard of this problem prior to this report, although this is a new DBA at this particular client and he claims that, although he moved the database to 2005 from 2000 in the past couple months, he was told that this problem also occured in SQL Server 2000 before he was even there.

    Anyone have any idea where to look to determine if it's somehow related to cursors not being released?

    Thanks.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by barbg
    Anyone have any idea where to look to determine if it's somehow related to cursors not being released?

    Thanks.
    If it's SQL 2005 Enterprise Edition, is the customer running the database in Snapshot Isolation mode?

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    Quote Originally Posted by hmscott
    If it's SQL 2005 Enterprise Edition, is the customer running the database in Snapshot Isolation mode?

    Regards,

    hmscott
    I don't really know, and he's a very difficult client to deal with - he doesn't like us asking questions that seem to him to prove our stupidity! If I ask him, what can I tell him is the reason I am asking?
    Thanks.

  4. #4
    Join Date
    Nov 2003
    Location
    denver
    Posts
    11

    Temp DB Full

    This is a very typical problems, which get encountered due to bad practices. Can you please check the following to troubleshoot this issue.
    Ensure there are no long running queries running on the system, which use temporary objects.
    Ensure that There are no orphan objects, being created on the tempdb. You can check this by querying the sysobjects where type = u.
    Make sure that maintenance related jobs such as Re-indexing are not sorting the indexes in the tempdb database. This is necessary not a bad practice as long as you have adequate disk space.

    Lastly check the following article for capacity planning of tempdb.
    Capacity Planning for tempdb

    Cheers
    Shailesh patangay

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    and cartesian joins/products.

Posting Permissions

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