tempdb keeps growing

  • I have configured my tempdb as follows:

    8 data files with an initial size of 14GB each. This was based on 2 weeks of monitoring the DB and seeing to what size it grows.

    I have set the autogrow to 10%.

    The thing is, the data files have now grown to 22 GB each - making the overall size just over 183 GB. The tempdb is on its own drive and the capacity of this drive is 200 GB.

    I am afraid that I might be running out of space....

    I know restarting the SQL service will shrink the files back to its initial size of 14 GB each, but this is a production server and a restart is not possible.

    Any possible solutions?

    Will the tempdb not release unused space?

  • Run this 3 query

    http://www.brentozar.com/archive/2011/11/how-tell-when-tempdb-problem-webcast-video/

    and post results. With this informations maybe someone will be able to help.

  • No database will ever automatically release free space back to the file system, but all data in your tempdb is temporary in nature and should be dropped as soon as the transactions using it have completed, hence freeing up space within the allocated files and mitigating the need for file growth if you have a reasonable amount of space to begin with.

    Your core setup of tempdb sounds reasonable enough. 😉

    It sounds like you might have som poorly written code running on your server - perhaps some developer forgot to include tempdb cleanup so they're just adding more and more temp tables and not dropping them when they're done.

    If so, the solution is to fix the "broken" code. Temporary workarounds can be to either keep growing the tempdb, delete stuff manually or keep restarting your server...


    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)
  • Thanks Vegard, just a quick question:

    What is the impact of the service broker on the tempdb?

    I have, amongst others, 2 DB on this server - one is the application database and the other hosts the service broker. And the 2 DB communicate with each other through

    the service broker, passing XML and sending thousands of messages each day. Could this also add to an increase in tempdb?

    I will definitely look into the cleanup op temp tables as well - thanks.

  • Yes, SB use tempdb

    "Service Broker explicitly uses tempdb for preserving existing dialog context that cannot stay in memory. The size is approximately 1 KB per dialog.

    Also, Service Broker implicitly uses tempdb by the caching of objects in the context of query execution, such as work tables that are used for timer events and background delivered conversations.

    Database Mail, Event Notifications, and Query Notifications implicitly use Service Broker. "

    http://msdn.microsoft.com/en-us/library/3065e567-dbeb-4770-9835-c6e1b44595b8.aspx

    But first check what is consuming most of tempdb space.

  • Just some feedback on this - I manged to resolve the issue....

    It was the service broker...

    We send XML messages in our broker and ever since the creation of our broker, we have sent over millions of messages. The problem was that there was an activation proc to Open these conversations, but there was not activation proc to CLOSE these conversations and so the XMLs remained in the tempdb. Once we implemented the proc to close the conversations, the tempdb started to release the space! Cant believe we missed this....

    The tempd usage has now dropped to just over 20 GB and remains constant

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply