Solved

TempDB size housekeeping/maintenance

Posted on 2012-03-27
3
1,841 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
  • 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now