SQL Server DBA Interview Questions Part 2


By:   |   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



Last Updated: 2007-07-09


get scripts

next tip button



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

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Sunday, August 02, 2015 - 9:58:53 AM - Dinesh Dattaray Vishe Back To Top

@jay,

if you want to create an index on a view or you want to preserve the base table schema once a view has been defined, in both these cases you have to use the "WITH SCHEMABINDING" clause to bind the view to the schema of the base tables.


Thursday, February 06, 2014 - 9:02:28 PM - Habtamu Abera Back To Top

Hi there! I have gone through the questions and answers you posted on your website and I have found most of them interesting. However, I have an issue concerning question # 2 (QUESTION DIFFICULT=MODERATE CATEGORY). Could you please change the MODEL database in question # 2 to TempDB or can you give me an explanation for mentioning the MODEL. I know  from experience that  all of the operations that you mentioned can't be performed on TempDB. 


Friday, September 06, 2013 - 8:20:35 AM - JAY PATEL Back To Top

can you please tell me   'What is the schema binding?'

 


Friday, August 10, 2012 - 7:00:57 AM - Shadab Shah Back To Top

The link for the answers above is broken. Please fix it.


Friday, May 28, 2010 - 10:10:51 AM - admin Back To Top

If you use your mouse to highlight the area under the question it will show the answers.


Wednesday, May 26, 2010 - 9:51:59 AM - sql_noob Back To Top

when do we see the answers?



download


Recommended Reading

SQL Server DBA Performance Tuning Interview Questions

SQL Server AlwaysOn Interview Questions and Answers Part 1

SQL Server DBA Phone Interview Questions

SQL Server AlwaysOn Interview Questions and Answers Part 2

SQL Server DBA Backup and Recovery Interview Questions





get free sql tips
agree to terms


Learn more about SQL Server tools