Solved

TempDB size housekeeping/maintenance

Posted on 2012-03-27
3
2,055 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 200 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 300 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

724 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