Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Every two to three days we are getting the following SCOM error:

The database tempdb in SQL instance MSSQLSERVER on computer sqlserver has experienced a significant change in free space

This has been happening since installing SharePoint 2007 SP2 and occurs in the middle of the night, probably while SharePoint is performing search indexing tasks.

Currently tempdb has a size of 313.88 MB and free space of 49.72 MB. I've been told the threshold for the alert is 45%. We are using SQL Server 2005 64-bit.

How can I resolve this issue and should tempdb be configured differently? I'm acting as an accidental DBA on this one! Thanks...

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

A quick solution would be to make tempdb big enough to avoid fluctuations.

  • Resize the temp db to 1GB data, 500 MB log (or 2GB/1GB)
  • Stop SQL Server
  • Delete the tempdb LDF and LDF files
  • Restart SQL Server

Without going too deep, disk space is cheap and there is no harm in having a larger tempdb.

As your data grows, you'll also start to use more tempdb too.

This solution will help avoid physical file fragmentation and automatic file growth issues.

share|improve this answer
add comment

tempdb is used for lots of things, to name some:

  • Tempdb tables, table variables
  • Hash Matches
  • Sorting operations
  • Row version
  • DBCC CHECKDB
  • Re-building indexes, if the SORT_IN_TEMPDB option is used

It's expected that free space will change, you can size tempdb accordingly to have at least have double the maximum that might be required to get around the alert. I'd also leave auto-grow on just in case, you don't want to be in a situation where tempdb runs out of space.

share|improve this answer
add comment

Has the database had autoshrink turned on? If so then turn it off. It will grow to the maximum size it requires and stay there. You will probably want to review the amount that it is growing by as well. Without knowing what sort of sizes we are talking about and what your server spec is I don't want to give any specific suggestions about the growth increments.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.