While conducting Interview for my outsourcing project, I asked one question to interviewer that what are the restrictions on TempDB? The candidate was not able to answer the question. I thought it would be good for all my readers to know the answer to this question so if you face this question in an interview or if you meet me in the interview you will be able to answer this question.
- 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.
- 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.
Please leave a comment in the comment section with your thoughts. I will be happy to read them and learn along with you about TempDB. Trust me TempDB has some restriction but it is indeed very helpful and key element in the SQL Server.
Reference: Pinal Dave (http://blog.SQLAuthority.com)