?
Solved

TempDB size housekeeping/maintenance

Posted on 2012-03-27
3
Medium Priority
?
2,123 Views
Last Modified: 2012-04-05
Hi
My questions relates to the best practices approach on maintaining and doing housekeeping to keep tempdb size in check.
I have a 237GB SQL Server 2008 Data Warehouse Database and buisness users use Microstrategy to create/run reports against this database.
So users can pull different attributes and metrics onto a report grid interface in Microstrategy Desktop to create a report and Microstrategy auto-generates the SQL gehind the scenes - so I don't have full control over the SQL that hits the data warehouse database.

I noticed that every so often tempdb would explode in size - for example yesterday evening the size of tempdb increased from approximately 4GB to 14GB in 1 hour.
We used SQL Server Alerts to track this.
From using these alerts and DMV's we have isolated that it is users running reports in Microstrategy that is causing this increase in tempdb size and that it is the 'internal' portion of tempdb that is being used as opposed to the 'user' or 'version' portions.

Longterm we hope to have a project which analyses performance/efficiency on the Microstrategy side but in the short term I am wondering what best practices housekeeping DBA type tasks we could do in relation to tempdb.
We have restarted the SQL Server service on a few occassions, therefore re-creating tempdb and getting it back to a small size but because this is a production environment, this is not always an option.
So I was wondering if there are any other options?
I have read in some articles that regular shrinking of database files is not good because it results in fragmentation.
Right now when I look in Database Properties the current Size of tempdb is 14348.5MB and the Space Available is 14132.59MB.
So I am wondering if there is any best practices approach to reclaiming back the 14132.59MB of Space Available, other than restarting the SQL Server service?

Thanks
BCUNNEY
0
Comment
Question by:Barry Cunney
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 3

Assisted Solution

by:tsnirone
tsnirone earned 800 total points
ID: 37770845
Edit: Its not all up to date, but

This one might be helpful for you:

http://www.mssqltips.com/sqlservertip/1432/tempdb-configuration-best-practices-in-sql-server/
0
 
LVL 25

Accepted Solution

by:
jogos earned 1200 total points
ID: 37770857
Best practice is to have a tempdb that does not have to grow.

Having 237Gb of database and sql-queries that are not under control on how the are formed 14GB looks even small to me.

Restarting server just to get tempdb small .... you loose your dmv-statistics in that process.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37770925
<<I noticed that every so often tempdb would explode in size - for example yesterday evening the size of tempdb increased from approximately 4GB to 14GB in 1 hour>>
It only takes one process that needs lot of space or some consuming processes together to have it grow.  It's better that this large processes don't have to waith for space to be allocated or .... error 'unable to allocate space ...'.

It's always good to know what is consuming that space so if you monitor and see that some reports blows the tempdb you can think if there are ways to have that using less space or having it run on a moment not many other processes will suffer from it.

Ex
Look at the time of the growth, Can it match with a specific process, ex REBUILD index with SORT_IN_TEMPDB http://msdn.microsoft.com/en-us/library/ms188281.aspx
- is sort_in_tempdb necessary
- is rebuild necessary or is reorganize sufficient, do you look at need for rebuild/reorganize?
- is the moment ok
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question