TempDB size housekeeping/maintenance
Posted on 2012-03-27
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?