Techyaz.com

SQL Server DBA Interview Questions & Answers

by · Published · Updated

This is second series of SQL Server DBA Interview questions & answers. I have published SQL Server Interview questions & answers on Database Basics and Architecture in attached article. Here, you will have another round of SQL Server DBA Interview questions based on general database administration.

SQL Server DBA Interview Questions and Answers

Question- What is your troubleshooting strategy? Suppose a user reported an issue, how will you start to fix the issue?

Answer- A good troubleshooters are those who understand the in-depth information about the product\features where issue arises. To fix any issue, we should follow below points.

  1. First we should understand the issue/error codes, ask questions to the user about when and how they get error.
  2. If we are not getting anything from step 2,  we need to read error log file, Job history log, and Event log files based on the nature of the issue.
  3. Look into SQL Server transactions and analyze the wait types and other parameters like CPU, IO, Memory if it is related to performance issue.
  4. If we are still not getting any solution, try to search the error on social sites or take help from your seniors because you are not the only one who has received this error.

Question- Tell me in which sequence SQL Server databases come ONLINE after starting or restarting a SQL Server Instance.

Answer- There is no defined sequence for user databases to come online. Although, below is the sequence in which system databases come online. User databases can come online anytime after master database become accessible.

  1. Master
  2. MSDB
  3. ResourceDB (Although this database is not visible in SSMS)
  4. Model
  5. Tempdb

Question- What should be the optimum AutoGrowth size for SQL Server databases?

Answer- Optimum Autogrowth size of any database file is based on the data growth. We should analyze the data growth and set autogrowth size accordingly. Ideally, Microsoft suggests to keep one-eighth of the size of data file. Read attached article to Understand database Autogrowth in SQL Server.

Question- How will you check the total number of autogrowth events occurred on your database?

Answer- You can get it by running Disk Usage dashboard report of any database. There is a section of all autogrowth and auto shrink events occurred on the database. Have a look in the attached article where i have shown this dashboard report to see number of autogrowth events for a database.

Question-What will be the impact on database if you have default value of Autogrowth or you keep small value of autogrowth size?

Answer- If we go with default value of Autogrowth or any amount that is less in size and not per your database growth pattern will lead to the performance issue. Read attached article to know why performance issue will be occurred if you have inappropriate value set to autogrowth.

Questions- What is compatibility level and how it impacts SQL Server?

Answer- Compatibility level shows the SQL Server version on which your database is running. This is used to support the backward compatibility. You can run your databases to the lower version of SQL Server having higher version of SQL Server installed on your machine by setting compatibility level of the corresponding SQL Server version.

Question- What do you think, should we enable Auto_Shrink option for a database?

Answer- We should never enable database auto shrink property for any database. Read this article to understand why we should not enable auto shrink for any database.

Question- What would be your options if your database files are full with maximum disk capacity?

Answer- We have below options in case disk space is full where data files are placed.

  1. Add Additional Disk Space to the same drive.
  2. Add additional data file to the different disk where you have enough space or you can add new disk as well to create this data file.
  3. Move the data file to another drive that has enough space.
  4. Delete\Purge unused data from your database to make some space.

We can choose any option depending on the requirement.

 Question- What will you do if your log file drive is full during data load?

Answer- We have below options in case you are in middle of data load.

  1. Run Transaction log backup frequently.
  2. Add another log file in another disk where is enough space to grow the log.
  3. You can enable Auto shrink during data load if you are running with SIMPLE recovery model.

Question – How many SQL Server Instances we can install on a cluster Instance?

Answer- We can install 25 instance on clustered environment and maximum 50 instances on standalone instances.

Question- What is the difference between updating statistics using sp_updatestats and UPDATE STATISTICS?

Answer- sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.

While going with UPDATE STATISTICS you have quite lot of options to use for stats update. This command scan all tables and data based on your scan value during updating statistics. This can take lot of time and performed intensive IO operations.

Question- Do we need to update Statistics after index rebuild operation?

Answer- No, Update stats is not required after index rebuild operations because statistics got updated during index rebuild operation.

Read More SQL Server DBA Interview Questions:

I hope you like these DBA interview questions. Please follow our facebook page and Twitter handle to get latest updates.

Manvendra Deo Singh
Follow me:
Latest posts by Manvendra Deo Singh (see all)
Summary
SQL Server DBA Interview Questions & Answers
Article Name
SQL Server DBA Interview Questions & Answers
Description
SQL Server DBA Interview questions & answers. I have published SQL Server Interview questions & answers on Database Basics and Architecture in my last article. Here, you will have another round of SQL Server Interview questions based on general database administration.
Author
Publisher Name
https://techyaz.com

Tags:

You may also like...

2 Responses

  1. Peter says:
    October 30, 2017 at 10:00 am

    Good questions for preparing for an interview. Although there could be more practical ones as well. Many companies like to ask work-sample questions, not just theoretical questions. For example these SQL interview questions: https://www.testdome.com/d/sql-interview-questions/17
    are the type of coding questions that are very common. I highly recommend practicing these types as well.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *