Sql-server – Correct settings for tempdb to avoid significant changes in free space

scomsharepointsharepoint-2007sql servertempdb

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…

Best Answer

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.

Related Topic