3

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...

Alex Angas
  • 2,007
  • 2
  • 26
  • 37

3 Answers3

1

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.

gbn
  • 6,009
  • 1
  • 17
  • 21
1

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.

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
0

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.

pipTheGeek
  • 1,152
  • 5
  • 7