SQL Server DBA Interview Questions Part 1

By:   |   Comments (10)   |   Related: More > Professional Development Interview Questions DBA


Problem

Finding DBAs is difficult, then properly interviewing a DBA to assess their skills is even more of a challenge.  This situation gets compounded when the interviewer does not fully understand the technology at a low level, but needs to find the right technical team member to join their team who does.  As such, in this three tip series we will cover some common questions to ask DBAs during a technical interview related to backups, maintenance, indexing, internal storage, etc.  Good luck!

Solution

Question Difficulty = Easy

  • Question 1: Consider a scenario where you issue a full backup.  Then issue some transaction log backups, next a differential backup, followed by more transaction log backups, then another differential and finally some transaction log backups.  If the SQL Server crashes and if all the differential backups are bad, when is the latest point in time you can successfully restore the database?  Can you recover the database to the current point in time without using any of the differential backups?
    • Answer: You can recover to the current point in time, as long as you have all the transaction log backups available and they are all valid. Differential backups do not affect the transaction log backup chain.
    • Additional Information: Backup and Recovery Tips
  • Question 2: Assume the same scenario, however instead of issuing differential backups, all three of the differential backups were full backups.  Assume all the full backups are corrupt with the exception of the first full backup.  Can you recover the database to the current point in time in this scenario?
    • Answer: Yes, just as it is with question 1.  Full backups do not affect the transaction log backup chain. As long as you have all of the transaction log backups and they are valid, you can restore the first full backup and then all subsequent transaction log backups to bring the database current.
    • Additional Information: Backup and Recovery Tips
  • Question 3: What methods are available for removing fragmentation of any kind on an index in SQL Server?
    • Answer (SQL Server 2000):
      • DBCC INDEXDEFRAG
      • DBCC DBREINDEX
      • CREATE INDEX...DROP EXISTING (cluster)
      • DROP INDEX; CREATE INDEX
    • Answer (SQL Server 2005): The same processes as SQL Server 2000, only different syntax
      • ALTER INDEX...REORGANIZE
      • ALTER INDEX...REBUILD
      • CREATE INDEX...DROP EXISTING (cluster)
      • DROP INDEX; CREATE INDEX
    • Additional Information: Indexing Tips and SQL Server 2000 to 2005 Crosswalk - Index Rebuilds

Question Difficulty = Moderate

  • Question 1: What is the fundamental unit of storage in SQL Server data files and what is it's size?
    • Answer: A page with a size of 8k.
  • Question 2: What is the fundamental unit of storage in SQL Server log files and what is it's size?
    • Answer: A log record, size is variable depending on the work being performed.
  • Question 3: How many different types of pages exist in SQL Server?
    • Answer:
      • Data
      • Index
      • Text/Image (LOB, ROW_OVERFLOW, XML)
      • GAM (Global Allocation Map)
      • SGAM (Shared Global Allocation Map)
      • PFS (Page Free Space)
      • IAM (Index Allocation Map)
      • BCM (Bulk Change Map)
      • DCM (Differential Change Map)
    • Additional Information: Indexing Tips

Question Difficulty = Difficult

  • Question 1: What are the primary differences between an index reorganization and an index rebuild?
    • Answer:
      • A reorganization is an "online" operation by default; a rebuild is an "offline" operation by default
      • A reorganization only affects the leaf level of an index
      • A reorganization swaps data pages in-place by using only the pages already allocated to the index; a rebuild uses new pages/allocations
      • A reorganization is always a fully-logged operation; a rebuild can be a minimally-logged operation
      • A reorganization can be stopped mid-process and all completed work is retained; a rebuild is transactional and must be completed in entirety to keep changes
    • Additional Information: Indexing Tips and SQL Server 2000 to 2005 Crosswalk - Index Rebuilds
  • Question 2: Can you explain the differences between a fully-logged and minimally-logged operations?
    • Answer: In a fully logged bulk operation, depending on the type of operation being performed, SQL Server will log either each record as it is processed (when performing a bulk data-load for example), or an image of the entire page that was changed (when performing a re-index/create index for example).  In a minimally-logged operation, SQL Server will log space allocations only, and also flip bit values in the BCM pages, assuming they are not already flipped, for extents that are modified during the bulk operation.
    • This minimizes both the space required to bulk log operations during the execution of the         operation, and also the time required to complete the bulk operation, since very little data is logged and updated compared to a fully-logged scenario.
    • When a database is bulk-changeable (i.e. in the bulk-logged recovery model), the BCM pages are reset when the first "BACKUP LOG" operation occurs following the given bulk operation. During this transaction log backup, the extents that are marked as modified in the BCM pages are included, in their entirety within the transaction log backup.  This results in a much larger transaction log backup than would be expected for the size of the active transaction log. This is what allows you to recover a bulk-logged operation if you have the transaction log backup following the operation despite the fact that during the operation you are logging only space allocations.
    • Additional Information: Minimally Logging Bulk Load Inserts into SQL Server and  Backup and Recovery Tips
Next Steps
  • Stay tuned for the next two tips in the series which should be released shortly.  These future tips will also have the easy, moderate and difficult questions.
  • Check out the first set of interview questions - Interview Questions - SQL Server Concurrency and Locking
  • 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

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

View all my tips



Comments For This Article




Friday, April 3, 2015 - 7:18:33 PM - jay Back To Top (36823)

Hi Chad,

 

In your second question, I think answer should be NO. As after full backup LSN chain will break and if your full backups are corrupted than you can only go till first fullbackup and following transactional log backups. 

Any change after second corrupted full backup will be gone even though you have un corrupted transaction logs as LSN sequence broke after your second full backup which is corrupted.

 

Thanks,

Jay


Thursday, January 9, 2014 - 8:47:17 AM - Imran Back To Top (28002)

for recovering the master database in sql server 2008 and above

 

stop the instance and copy the master data file & log (*.mdf & *.ldf from 

C:\Program Files\Microsoft SQL Server\Instance Name\MSSQL\Binn\Templates\

to C:\Program Files\Microsoft SQL Server\Instance Name\MSSQL\DATA\

restart the server...........

copy of the system databases mainted simultaneous at template folder

Hope you got the answer


Tuesday, December 3, 2013 - 11:37:30 AM - Sahul Back To Top (27673)

For recovering the master database, i could not find Rebiuldm.exe file in the above mentioned path. Please help me to get that.

Many thanks in advance

 


Friday, July 27, 2012 - 3:01:02 PM - Dinesh Dattatray Vishe Back To Top (18831)

@pradeep,

Using Rebuildm.exe which is present (Programfiles/microsoft/tools/BINN). 


Sunday, May 6, 2012 - 6:27:21 AM - Pradeep Upadhyay Back To Top (17286)

 

How can we recover master database if master database lost?


Wednesday, June 22, 2011 - 2:27:57 PM - kranthi Back To Top (14064)

Hi,

This is one of the best  sites I can refer to my frnz....

Hope u post more DBA scenario based questions in future

Regards,

kranthi

 

---If u want to be at everest ...never be at rest


Monday, December 20, 2010 - 8:10:46 AM - Heymal Back To Top (10467)

Yes, I made a mistake when I answered the second question. It refresh my knoweledge


Wednesday, August 19, 2009 - 3:03:46 PM - Jeff Roughgarden Back To Top (3923)

Oh! You mean you wanted me to read the introduction? RTFM?

Thanks for the clarification.


Wednesday, August 19, 2009 - 2:47:39 PM - admin Back To Top (3922)

If you use your mouse to select the area where the answers are you will see reverese text and therefore the answers.

 Regards


Wednesday, August 19, 2009 - 9:45:12 AM - Jeff Roughgarden Back To Top (3917)

I would find these interview questions much more useful if you posted the answers as well.















get free sql tips
agree to terms