SQL Server DBA Interview Questions and answers for experienced

Question.1   Explain about your SQL Server DBA Experience?

Answer:   This is a generic question often asked by many interviewers. Explain what are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the answer that lets the interviewer know how suitable are you for the position to which you are being interviewed.

Question.2    What are the different SQL Server Versions you have worked on?

Answer:    The answer would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005 and 2008. If you have worked only the some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.

Question.3   What are the different types of Indexes available in SQL Server?

Answer:   The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.

Question.4   What is the difference between Clustered and Non-Clustered Index?

Answer:   In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.

In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.

Question.5   What are the new features in SQL Server 2005 when compared to SQL Server 2000?

Answer:   There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here :

Database Partitioning

Dynamic Management Views

System Catalog Views

Resource Database

Database Snapshots

SQL Server Integration Services

Support for Analysis Services on a a Failover Cluster.

Profiler being able to trace the MDX queries of the Analysis Server.

Peer-toPeer Replication

Database Mirroring

Question.6   What are the High-Availability solutions in SQL Server and differentiate them briefly?

Answer:   Failover Clustering, Database Mirroring, Log Shipping and Replication are the High-Availability features available in SQL Server. I would recommend reading this blog of mine which explains the differences between these 4 features. Comparing the High Availability Features in SQL Server 2005

Question.7   How do you troubleshoot errors in a SQL Server Agent Job?

Answer:   Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.

Question.8   What is the default Port No on which SQL Server listens?

Answer:   1433

Question.9   How many files can a Database contain in SQL Server?How many types of data files exists in SQL Server? How many of those files can exist for a single database?

Answer:   A Database can contain a maximum of 32,767 files.

There are Primarily 2 types of data files Primary data file and Secondary data file(s)

There can be only one Primary data file and multiple secondary data files as long as the total # of files is less than 32,767 files.

Question.10   What is DCL?

Answer:   DCL stands for Data Control Language.

Question.11   What are the commands used in DCL?

Answer:   GRANT, DENY and REVOKE.

Question.12   What is Fill Factor?

Answer:   Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.

Question.13   What is the default fill factor value?

Answer:   By default the fill factor value is set to 0.

Question.14   Where do you find the default Index fill factor and how to change it?

Answer:    The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to a desired value there and click OK to save the changes.

The other option of viewing and changing this value is using sp_configure.

Question.15   What is a system database and what is a user database?

Answer:   System databases are the default databases that are installed when the SQL Server is installed. Basically there are 4 system databases: Master, MSDB, TempDB and Model. It is highly recommended that these databases are not modified or altered for smooth functioning of the SQL System.

A user database is a database that we create to store data and start working with  the data.

Question.16   What are the recovery models for a database?

Answer:  There are 3 recovery models available for a database. Full, Bulk-Logged and Simple are the three recovery models available.

Question.17   What is the importance of a recovery model?

Answer:  Primarily, recovery model is chosen keeping in view the amount of data loss one can afford to. If one expects to have minimal or no data loss, choosing the Full recovery model is a good choice. Depending on the recovery model of a database, the behavior of database log file changes. I would recommend you read more material on log backups and log file behavior and so on to understand in depth.

Question.18   What is Replication?

Answer:   Replication is a feature in SQL Server that helps us publish database objects and data and copy (replicate) it to one or more destinations. It is often considered as one of the High-Availability options. One of the advantages with Replication is that it can be configured on databases which are in simple recovery model.

Question.19    What the different types of Replication and why are they used?

Answer:   There are basically 3 types of replication: Snapshot, Transactional and Merge Replication. The type of Replication you choose, depends on the requirements and/or the goals one is trying to achieve. For example Snapshot Replication is useful only when the data inside the tables does not change frequently and the amount of data is not too large, such as a monthly summary table or a product list table etc. Transactional Replication would useful when maintaining a copy of a transactional table such as sales

Visit for : SQL Server DBA Online Course