Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SQL Server System Databases Interview Questions

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (7)   |   Related Tips: More > Interview Questions DBA
Problem

In the latest installment of the SQL Server interview questions, we will outline questions suitable for a DBA interview to assess the candidates skills related to SQL Server system databases. In this tip, the questions are there to read, but the answers are intentionally hidden to really test your skills. Once you read the question and have determined your answer, then highlight the answer to see how you did. Good luck!

Solution

Question Difficulty = Easy

  • Question 1: What are the SQL Server system databases and can you outline the general functionality of each database?
    • Master - Database responsible for SQL Server instance related data.  You can also think of this database corresponding to the Windows SQL Server service account.
    • Resource - Database responsible for SQL Server system objects.  This database was introduced in SQL Server 2005 and is intended to ease the upgrade and rollback of SQL Server system objects.
    • Model - Template database for the creation of new user defined databases.
    • MSDB - Database responsible for SQL Server Agent related data such as Jobs, Alerts, Operators, etc.
    • TempDB - Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc.  Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
    • Distribution - Database responsible for managing replicated data.  This database could reside on the publisher or subscriber.
  • Question 2: True or False - Can you create objects in the Master, Model and MSDB databases?
    • True.
  • Question 3: Is it a good idea to create objects in the system databases?
    • In general , objects should not be created in the system databases.  In general, it is a best practice to create a separate database for user defined objects that would be used instance wide or create the objects in each of the needed databases.  From a DBA perspective, it is a common practice to create a dedicated DBA database in order to perform custom administrative tasks.
    • If objects are needed for future user defined databases those can be created in the Model database.
  • Question 4: Does Reporting Services create system databases during the installation process?
    • Yes.  The databases are:
      • ReportServer - Stores the reports related data.
      • ReportServerTempDB - Temporary database storage for Reporting Services.
  • Question 5: Can you move system databases?

Question Difficulty = Moderate

  • Question 1: Do users need explicit rights to the user defined databases?
    • No - Rights are granted through the Public Role and Guest user.
  • Question 2: What are the typical objects that are created in the TempDB database?
    • Temporary tables (#temptable or ##temptale)
    • Table variables
    • Cursors
    • Work tables
    • Row versioning
    • Create or rebuild indexes sorted in TempDB
  • Question 3: If the model database has the database recovery model set to full, what is the impact?
    • When new user defined databases are created, they will have a full recovery model.  This means that transaction log backups will be needed to maintain a reasonably sized transaction log.  If not the transaction logs will continue to grow indefinitely.
  • Question 4: Are all DTS packages stored in MSDB?  Are all SSIS Packages stored in MSDB?
    • All DTS packages do not need to be stored in the MSDB database, but that was the trend in SQL Server 2000.
    • All SSIS Packages do not need to be stored in the MSDB database.  Storage on the file system or in the MSDB database are more a matter of preference as opposed to an industry trend thus far.
  • Question 5:  Does the size of the TempDB database really make a difference and how should TempDB be sized?
    • In situations where temporary objects are created in T-SQL code (i.e. temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc.) the TempDB database makes a significant difference in overall performance.  In these situations the database should be sized appropriately and moved to a separate disk drive in order to support the IO requests.  If not, the default location and size may be appriopriate.
    • In situations where TempDB is not used in the applications, then

Question Difficulty = Advanced

  • Question 1: Are all SQL Server Agent configurations stored in the MSDB database?
  • Question 2: Please explain the relationship between logins and users in the system and user databases.
    • Logins - All logins reside in the master database
    • Users - All users reside in the master database, other system databases and in the user defined databases.
      • Additional information: MSSQLTips.com Category - Security
      • Additional information: MSSQLTips.com Category - Compliance
  • Question 3:  With the upgrade from SQL Server 2000 to SQL Server 2005, the system objects changed.  Can you name three of the mapped objects between the two versions of SQL Server?
    • Here are three examples, but others do exist:
      • System databases
        • SQL Server 2000 - master.dbo.sysdatabases
        • SQL Server 2005 - master.sys.databases
      • Database files
        • SQL Server 2000 - master.dbo.sysaltfiles
        • SQL Server 2005 - master.sys.master_files
      • IO Statistics
  • Question 4: Can you explain the differences in restoring the Master database versus a user defined database?
    • In order to restore the Master database the SQL Server instance must be in single user mode.
    • After the Master database is restored, the SQL Server instance restarts.
    • A different set of restore errors may occur as compared to user defined databases.  One example is if different databases exist from the backup to the current time period, errors will be recorded related to suspect databases.
  • Question 5:  What is the Resource database and in what version of SQL Server was it introduced?
    • The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
    • The Resource database was introduced in SQL Server 2005.
Next Steps
  • As you prepare for an interview, consider the interview tips on MSSQLTips.com.
  • Check out these related tips:
  • If you have a real stumper of a question, feel free to post it out to the forum listed below.


Last Update: 1/11/2008


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Tuesday, March 29, 2011 - 8:07:28 AM - Karen Weber Read The Tip

We're about to start a SQL Server 2008 implementation.  Do any of these answers change in the 2008 version?


Friday, April 08, 2011 - 9:02:47 AM - Jeremy Kadlec Read The Tip

Karen,

Yes - These questions are applicable for SQL Server 2008.

Thank you,
Jeremy Kadlec


Monday, August 20, 2012 - 8:04:11 AM - kranthi Read The Tip

send me the easiest way to create cursors, triggers and some stored procedures which uses temporary tables.....


Monday, August 20, 2012 - 3:13:46 PM - Jeremy Kadlec Read The Tip

Kranthi,

I would recommend reading the following resources:

Cursor - http://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/

Triggers - http://www.mssqltips.com/sql-server-tip-category/59/triggers/

Stored procedures - http://www.mssqltips.com/sqlservertutorial/160/sql-server-stored-procedure/

Temp tables - http://www.mssqltips.com/sqlservertip/1556/differences-between-sql-server-temporary-tables-and-table-variables/

 

HTH.

Thank you,
Jeremy Kadlec


Wednesday, November 28, 2012 - 11:01:21 AM - jayron Read The Tip

Thank you Jeremy Kadlec

This tips are useful indeed.

Best


Wednesday, November 28, 2012 - 1:12:46 PM - Jeremy Kadlec Read The Tip

Jayron,

Thank you so much for the positive feedback.

Thank you,
Jeremy Kadlec


Tuesday, February 19, 2013 - 7:25:47 AM - Ratheesh kumar Read The Tip

Realy helpfull



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.