SQL SERVER – TempDB Restrictions – Temp Database Restrictions

While conducting Interview for my outsourcing project, I asked one question to interviewer that what are the restrictions on TempDB? Candidate was not able to answer the question. I thought it would be good for all my readers to know answer to this question so if you face this question in interview or if you meet me in 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.

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL

About these ads

3 thoughts on “SQL SERVER – TempDB Restrictions – Temp Database Restrictions

  1. Pingback: SQL SERVER - T-SQL Script to Find Details About TempDB Journey to SQL Authority with Pinal Dave

  2. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  3. Hi,
    In this article you mention Adding filegroups. is a restriction in TEMP DB. Recently in an interview some one asked me question on TEMP db is
    1. What is the Maximum no of files can be added in temp db?
    2. What is the minimum no of files can be added in temp db?

    I search a lot but I got some where as many as possible, somewhere 8, somewhere two…..I fully confused.

    Request you to clarify my doubt?
    Regards,
    Munia

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s