Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

tempdb keeps growing Expand / Collapse
Author
Message
Posted Monday, June 3, 2013 4:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 125, Visits: 1,048
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?
Post #1459163
Posted Monday, June 3, 2013 4:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 223, Visits: 1,720
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.
Post #1459173
Posted Monday, June 3, 2013 4:46 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:50 AM
Points: 517, Visits: 346
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...




Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

"It is better to light a candle than to curse the darkness."
(Chinese proverb)

Post #1459177
Posted Monday, June 3, 2013 5:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 125, Visits: 1,048
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.
Post #1459196
Posted Monday, June 3, 2013 5:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 223, Visits: 1,720
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.
Post #1459200
Posted Tuesday, July 2, 2013 8:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 125, Visits: 1,048
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

Post #1469570
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse