SQL Server DBA Interview Questions Part 2

By: Chad Boyd   |   Updated: 2007-07-09   |   Comments (6)   |   Related: More > Professional Development Interview Questions DBA

Problem

In the second part of the SQL Server Administration interview series we will outline questions suitable for a DBA interview to assess the candidates skills.  These questions relate to system objects, database recovery models and recovery processes page verification. 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 is the name of the system database that contains all executable system objects (i.e. system stored procedure code, system view definitions, etc.)
  • Answer (SQL Server 2000): master database
  • Answer (SQL Server 2005): resource database
• Question 2: Can you update system tables in SQL Server, and if so, how?
  • Answer (SQL Server 2000):  Yes - to allow updates to the system catalog, update the 'allow updates' system configuration option to a value of 1 and reconfigure the server. This will allow you to make direct updates to the system catalog.
  • Answer (SQL Server 2005): No - updates to the system catalog are explicitly prohibited. The 'allow updates' system configuration option still exists, however it is completely ignored.
• Question 3: What are the 3 recovery models SQL Server exposes?
  • Answer:
    • FULL
    • BULK_LOGGED
    • SIMPLE
• Question 4: What are the three basic phases for database recovery and in what order do they occur?
  • Answer:
    • Analysis
    • Redo
    • Undo

Question Difficulty = Moderate

• Question 1: As a database is recovering, after which phase will the database be available/online?
  • Answer (SQL Server 2000): After the Undo phase.
  • Answer (SQL Server 2005):  In all editions but Enterprise, after the Undo phase (if running FULL recovery model). With Enterprise edition, after the Redo phase. Fast recovery is possible because transactions that were uncommitted when a crash occurred reacquire whatever locks they held before the crash. While these transactions are being rolled back, their locks protect them from interference by users.
• Question 2: Name as many operations as possible that cannot be performed on the model database.
  • Answer:
    • Adding files or filegroups.
    • Changing collation. The default collation is the server collation.
    • Changing the database owner. model is owned by sa.
    • 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.
    • Setting the database to OFFLINE.
    • Setting the database or primary filegroup to READ_ONLY.
• Question 3: During an index reorganization operation, if the index spans multiple files, will pages be allowed to migrate between files
  • Answer: No - pages will not migrate between files during an index reorganization.

Question Difficulty = Difficult

• Question 1: What page verification options are available in SQL Server and how do they work?
  • Answer (SQL Server 2000): Only "Torn Page Detection" is available in SQL Server 2000.
  • Answer (SQL Server 2005):  Both "Torn Page Detection" and "Checksum" page verification options exist in SQL Server 2005.  Page verification checks help to discover damaged database pages caused by disk I/O path errors. Disk I/O path errors can be the cause of database corruption problems and are generally caused by power failures or disk hardware failures that occur at the time the page is being written to disk.
    • TORN PAGE DETECTION works by saving a specific bit for each 512-byte sector in the 8-kilobyte (KB) database page and is stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information. Unmatched values indicate that only part of the page was written to disk. In this situation, error message 824 (indicating a torn page error) is reported to both the SQL Server error log and the Windows event log. Torn pages are typically detected by database recovery if it is truly an incomplete write of a page. However, other I/O path failures can cause a torn page at any time.
    • CHECKSUM works by calculating a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header. If the values do not match, error message 824 (indicating a checksum failure) is reported to both the SQL Server error log and the Windows event log. A checksum failure indicates an I/O path problem. To determine the root cause requires investigation of the hardware, firmware drivers, BIOS, filter drivers (such as virus software), and other I/O path components.
• Question 2:  What special type of page within SQL Server is responsible for tracking (via a simple bitmap) extents that have been modified by bulk-logged operations since the last "BACKUP LOG" statement?
  • Answer: "BCM" (Bulk-Changed-Map) pages.
    • Although BCM pages appear in all databases, they are only relevant when the database is using the bulk-logged recovery model. In this recovery model, when a BACKUP LOG is performed, the backup process scans the BCMs for extents that have been modified. It then includes those extents in the log backup. This lets the bulk logged operations be recovered if the database is restored from a database backup and a sequence of transaction log backups. BCM pages are not relevant in a database that is using the simple recovery model, because no bulk logged operations are logged.  In addition, the concept of a transaction log backup in SIMPLE recovery is not possible. They are not relevant in a database that is using the full recovery model, because that recovery model treats bulk logged operations as fully logged operations.
    • The interval between DCM pages and BCM pages is the same as the interval between GAM and SGAM page, 64,000 extents. The DCM and BCM pages are located behind the GAM and SGAM pages in a physical file.

Next Steps

• Check out the first two SQL Server interview  tips:
  • Interview Questions - SQL Server Concurrency and Locking
  • Interview Questions - SQL Server Administration (Tip 1 of 3)
• Check out all of the Professional Development tips on MSSQLTips.com. If you have a question that you ask when you interview DBAs that is a real stumper, send it to [email protected] and we will include it in one of our future tips.
• Stay tuned for the third tip in this series which should be released shortly.

About the author

Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips