Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    Toronto
    Posts
    28

    Unanswered: tempdb growing out of control

    Hi all.

    I have a db application that has been running fine for months. Volumes have been gradually increasing and one day the system locked up.

    A stored proc that typically ran in 3 or 4 minutes never returned. The tempdb kept expanding to fill available disk space (100GB). This was the offending statement inside the stored proc:

    INSERT INTO cpp (CPPDate,MerchantLink,ReportNumber,FromDate,ToDate ,TransThreshold,DayThreshold,CPPType)
    SELECT S.CPPDate,S.MerchantLink,s.ReportNumber, s.FromDate,s.ToDate, S.OccurThresh,s.DaysThresh,'D'
    FROM #stuff S, Trans T with (nolock), Supplier P with (nolock)
    where T.MerchantNumber in (SELECT MerchantNumber FROM Merchant WHERE MerchantLink = s.MerchantLink)
    AND T.TranDate >= S.FromDate
    AND T.TranDate <= S.ToDate
    AND T.LoadDate <= @ReportDate2
    AND (T.SupplierNumber = P.SupplierNumber
    AND T.IncludeInCpp = 'Y'
    AND P.CountryNumber IN (SELECT CountryNumber FROM REPORTCOMBO WHERE ReportNumber = s.ReportNumber))
    GROUP BY CPPDate,Merchantlink,ReportNumber, FromDate,ToDate, OccurThresh, DaysThresh
    HAVING COUNT(DISTINCT T.AccountNumber) >= OccurThresh

    I realize that a "group by" uses the tempdb, but can't figure out why it would go away rather than returning an error.

    I have a workaround in place now. I split this big query into several steps using a cursor. (slower and clumsier, but it works) Statistics are updated daily, i have tried defragging, and reindexing with no success.

    Any thoughts would be appreciated. If you need any more details, please let me know.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Link on TEMPDB to tackle the issue:
    http://www.sql-server-performance.com/tempdb.asp
    http://www.sql-server-performance.co...emp_tables.asp

    The tempdb database can be used directly via TSQL, for example, as when temp tables are used; or it can be used indirectly, like it when ORDER BYs or GROUP BYs are run as part of a query.

    In some cases, you may not be able to easily control the queries that hit your database. For example, perhaps you allow Excel or Access users to query SQL Server data. In this case, you can't prevent users from writing bad queries.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto
    Posts
    28
    Originally posted by Satya
    Link on TEMPDB to tackle the issue:
    http://www.sql-server-performance.com/tempdb.asp
    http://www.sql-server-performance.co...emp_tables.asp

    The tempdb database can be used directly via TSQL, for example, as when temp tables are used; or it can be used indirectly, like it when ORDER BYs or GROUP BYs are run as part of a query.

    In some cases, you may not be able to easily control the queries that hit your database. For example, perhaps you allow Excel or Access users to query SQL Server data. In this case, you can't prevent users from writing bad queries.
    Hi Satya,

    Thanks for the feedback. It caused me to review the temp table, and i found that an index on the temp table in this query had been removed.

    I must have reached some limit, and without this index, performance was seriously affected. I am suprised that the tempdb would continue to grow and grow, rather than return some kind of error though......

    Many thanks!

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578

    Lightbulb

    If the TEMPDB hosted drive has enough space to copeup then OS & SQL will never complain about resources, and its a responsibility of DBA to keep an eye on such sizes in that kind of environment.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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