Share via

So you think you know SQL...Administration Part I

  • Article

So you think you know SQL...Administration Part I

The 2nd in the series...this one revolves around Administration and Internals of Sql Server, and is the 1st of 2-4 groups of questions in just Administration and Internals...again, these are seperated into easy, moderate, and difficult groups. As always, if there are any questions/comments/etc., post a comment and we'll discuss it, enjoy!

For answers to these questions, go here when you're ready to see them:

Part I: https://www.mssqltips.com/tip.asp?tip1299
Part II: https://www.mssqltips.com/tip.asp?tip1281

Part III: https://www.mssqltips.com/tip.asp?tip1288

--------------------------------------------------------------------------------------------------
EASY
--------------------------------------------------------------------------------------------------

Q: What can you do to guarantee any backup is valid?

Q: What is the name of the system database that contains all executable system objects (i.e. system stored procedure code, system view definitions, etc.)

Q: Can you update system tables in Sql Server, and if so, how?

Q: If a database goes down for whatever reason (assume data file is corrupt for example), and the database cannot be brought back online (i.e. you are in a recovery situation), what must be done first to ensure you can retrieve the latest data modifications (assume FULL recovery model)?

Q: What are the 3 recovery models SQL Server exposes?

Q: What 3 basic phases does database recovery go through during recovery, and in what order do they occur?

Q: Consider a scenario where you take a full backup, then some log backups, then a diff backup, some more log backups, then another diff, then some more log backups, then you crash - if all the diff backups are bad, when is the latest you can restore to?  i.e. can you recover the database to the current point in time without using any of the diff backups?

   FOLLOW-UP Q: Assume the same scenario, however instead of taking diff backups, everywhere that the diff backups were taken were instead FULL backups (so you have 3 full backups) - 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 here?

Q: What options/arguments can be specified in a BACKUP LOG statement to keep inactive log records from being truncated?

Q: What methods are available for removing fragmentation (any kind) on an index in Sql Server?

--------------------------------------------------------------------------------------------------
MODERATE:
--------------------------------------------------------------------------------------------------

Q: Name as many operations as possible that cannot be performed on the model database.

Q: What page verification options are available in Sql Server and how do they work?

Q: What is the fundamental unit of storage in SQL Server data files and what is it’s size?

Q: What is the fundamental unit of storage in SQL Server log files and what is it’s size?

Q: Name as many different types of Pages in SQL Server as possible

Q: Is the sequence of data rows on given data/index page guaranteed to physically match the logical order of rows (i.e. by index key, row id, etc.)?

Q: What is the fundamental unit in which space and allocation is managed within Sql Server and what is it’s size?

Q: What are the primary differences between an index reorganization and an index rebuild?

Q: If you need to REBUILD a non-clustered index that is 10gb in size and have 5gb of free data-file space available with no room to grow the data file(s), how can you accomplish the task?

Q: During an index reorganization operation, if the index spans multiple files, will pages be allowed to migrate between files?

--------------------------------------------------------------------------------------------------
DIFFICULT:
--------------------------------------------------------------------------------------------------

Q: Explain the difference between a fully-logged and minimally-logged operation

Q: 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?

Q: What special type of page within Sql Server is responsible for tracking (via a simple bitmap) extents that have been modified since the last "BACKUP DATABASE" statement?

 

Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.

Comments