Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009

    Unanswered: SQL sever 2008 Tempdb size

    Hi all

    We using SQL 2008 here, our tempdb on the reporting server is almost 9Gb, sometimes when running queries or reports we get errors back saying that the tempdb log file is full, but the .ldf file is only about 200Mb, and the .mdf is almost 9 Gig, tried shrinking the database and .mdf file, but the size remains the same.

    How can we reduce the size of our tempdb, surely its just used to store temp data and data for #temp tables.


  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    Actually you don't want to shrink tempdb, but rather find out what makes it grow. In 2K8 you can see it from standard reports in SSMS. Also, if the error says "the log file is full", it size is irrelevant. What's relevant is how much of the allocated space is actually used by transactions. In your case all 200MB are used and further allocation is either not possible or is not allowed.

    P.S.: just in case, check what the recovery model is set for your tempdb? It should be SIMPLE, And another thing, check what is the used portion of the MDF file? It's awfully big to be at 9GB, unless you're loading large volumes of data into tempdb. This is possible to do in DW environments, but if you're running an OLTP env, - tempdb data device should not be that big.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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