Questions[tempdb](server)

After months of perfectly flat disk usage, my tempdb file suddenly grew by several gigs over the weekend. Nobody at the company is aware of anything that might have changed.

When I checked the tempdb database, it had only a few very small tables, whose names were strings of hex digits.

In searching for the cause, I found the following message repeated every few minutes for several days in the event log:

DBCC SHRINKDATABASE for database ID 2 is waiting for the snapshot transaction 
with timestamp 51743762409 and other snapshot transactions linked to timestamp 
51743762409 or with timestamps older than 51801253540 to finish.

I can't find any possible way that DBCC SHRINKDATABASE could have been run by anybody on the tempdb (which is DB ID 2). Microsoft's own documentation says that SHRINKDATABASE should never be run on tempdb while it's online, so I can't imagine that SQL server is running it itself.

I'm trying to figure out:

• What could have caused such sudden rapid growth in the tempdb file? I'm not aware of any code that uses temporary tables or declares table variables on this server. What else uses the tempdb file?
• Why is DBCC SHRINKDATABASE running on tempdb at all, and why is it failing?

I have run into a problem where a tempdb database set to have unrestricted growth has used up all the available disk space (10x its norm). I am considering setting a maximum size limit, something big but just not all the space.

If a query pushes tempdb to the limit, I'm hoping the query that's using up the space will be killed off and free the space allowing everything else to work correctly. Is this likely to happen or will SQL Server stop completely?

Thanks for any answers.

I've a SQL 2005 installation and my templog.ldf file keeps growing to consume all the free space on the drive it's on. Sometimes it'll stop with a few mb free but sometimes it goes further, this being the c drive I think this behaviour may be implicated in some other issues I've been seeing.

My question is, what should I do, I can move the log to another drive but I've reason to assume it won't just do the same thing there. I'm assuming that this behaviour is likely as a result of something I can change and that 45gb is an unusual size for the tempdb log to get to. We do use a lot of temporary tables and table valued functions in our code so there is plenty of scope to use tempdb, I can understand the tempdb database growing but don't understand the reason for the growth of the templog.

So far, I've ran DBCC OPENTRAN('tempdb') to see if any old transactions are hanging around, they aren't. I've read about how to shrink the tempdb and have done this a few times, but I'm really wondering what if anything I can do to stop this happening in the first place or more details on why it might be growing so much in the first place.

==EDITS==

1) The tempdb is using simple recovery model

2) The growth in templog occurs over a couple of hours in the morning when we have some scheduled queries running, basically a load of reporting which runs out of office hours for the day ahead. The size of the file steadily grows over this time. We control how many concurrent reports are running at the same time, increasing the number of concurrent reports increases the rate at which the log grows.

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

We've been having trouble caused by what we believe is contention within tempDB.

Whenever we are having problems, our system is always waiting on one particular resource: 2:1:103, which when we look it up (using DBCC PAGE(2,1,103)) tracks back to object_id 75, which is the system table sysmultiobjrefs.

To solve this problem, we can sometimes get away with killing hanging spids waiting on that resource... in the worse cases, we have to actually stop SQL and start it back up.

Any ideas on how to alleviate this?

We're running SQL 2005 SP3 x64 on a quad/quad server with 128GB of RAM. Disks are also on a SAN with log/tempdb/data each on its own RAID 1/0 drive.

TempDB has 16 data files (one for each core) and one log file.

Thanks in advance.