Restrictions on TEMPDB

• Adding filegroups.
• Backing up or restoring the database.
• Changing collation. The default collation is the server collation.
• Changing the database owner. tempdb is owned by dbo.
• Creating a database snapshot.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Running DBCC CHECKALLOC.
• Running DBCC CHECKCATALOG.
• Setting the database to OFFLINE.
• Setting the database or primary filegroup to READ_ONLY.

How the TempDB becomes Full?

• DBCC CHECKDB will perform its work in tempdb
• DBCC DBREINDEX or similar DBCC commands with "Sort in Tempdb" option can make the tempdb full
• Large resultsets involving unions, order by, group by, joins, temp tables etc. can also fill up tempdb
• Any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb
• An ODBC DSN with the option 'Create  temporary stored procedures' set can leave objects in tempdb.

Compatibility Levels

• 60 = SQL Server 6.0
• 65 = SQL Server 6.5
• 70 = SQL Server 7.0
• 80 = SQL Server 2000
• 90 = SQL Server 2005
• 100 = SQL Server 2008