This article will help you with some popular SQL Server DBA Interview questions and answers. I would recommend you to read this article as well as SQL Server Interview questions and answers to learn more about SQL interview questions. In this article also, I tried to answer each question in a very precise manner.
You must also prepare the below questions along with improving your technical skills if you are looking for a job change. Having technical knowledge is not enough to get a job these days rather you should show your dedication, positive attitude, flexibility, and read to adopt new changes to your interviewers. Interviewers ask a lot of other non-technical questions to evaluate your qualities like I just mentioned above (dedication, positive attitude, flexibility and read to adopt new changes). Make sure to prepare their answers as well before going into the job market. I am giving the example of some of these questions.
- Tell me something about yourself and your experience in SQL Server?
- Brief me about your database environment like the number of instances, databases, biggest database size, etc.?
- Explain your backup and database maintenance strategy for your biggest critical OLTP database?
- How do you handle major incidents?
- Tell me something about the last issue you have faced and how did you fix that?
- Suppose there is a database issue, and you don’t have any solution. You did everything to find the solution to fix the issue, but you did not get anything. What would be your next step to handle this situation?
- Explain any of your two deliveries or solutions for which you have been recognized by your client and leaders?
Let’s discuss some technical questions and their answers in the below sections now.
SQL Server Interview Questions
What is SQLOS?
SQLOS stands for SQL Server Operating System. SQLOS is the lowest layer of SQL Server Database Engine which is responsible to perform critical internal tasks like scheduling threads on CPU to execute SQL Serve transactions, memory management, deadlock detection, and IO completion activities. It works just like another mini operating system for SQL Server operations.
What is the ghost cleanup process in SQL Server?
Any record deleted from SQL Server is not actually deleted from its physical data pages but marked as “To BE DELETED” or “GHOSTED” by changing a bit in the row header. These records will be cleaned up by an internal process that is called the ghost cleanup process. This is a single-threaded background process that automatically runs on some intervals to check if any page is marked for ghost records or not and if it finds any, then it physically removes those ghosted records from pages.
What would be the potential reason behind SQL Server connection issues?
You might be failed to connect to SQL Server instance because of various reasons such as:
- Ensure you are entering the right connection details like server name and port no
- Ensure SQL Server port is open from the machine you are connecting to the instance
- Make sure SQL Server instance is running
- TCP or named pipe protocols are enabled through SQL Server configuration manager
- SQL browser service is not running
Is it possible that database physical file names also changed after renaming a database?
No, renaming a database will not change its physical file names. You need to manually perform additional steps to change the database physical file names.
Explain how will you change the physical file name of a data file?
Changing the physical database file name is multistep activity. We must not perform such activities on regular basis and only do them on a need basis. Below are the high-level steps to change a physical file of a database.
- Set database in OFFLINE state
- Go to the location where your database files are saved and rename each file as per your desired naming convention
-
Update system catalog with new files names by running ALTER DATABASE statement for each file separately
1234USE masterGOALTER DATABASE SQLSHACK MODIFY FILE (Name='SQLSHACK_Data1', FILENAME='F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQLSHACK_Renamed.mdf')GO - Bring database ONLINE
How do you get I/O statistics of a specific database file like the number of reads, writes, IO stalls, etc.?
We can use either the system dynamic management function sys.dm_io_virtual_file_stats or a system function fn_virtualfilestats to get this information. We need to pass database and file id details about that specific database file for which we are looking for IO statistics. Below is its example. I am pulling IO statistics for the master database log file.
|
1 2 3 |
SELECT * FROM sys.dm_io_virtual_file_stats(db_id, file_id); GO SELECT * FROM fn_virtualfilestats(dbid,fileid) |
What is Automatic Seeding in Always on availability group?
Automatic seeding is a feature launched in SQL Server 2016 to initialize the secondary replica. Earlier we have only one option to initialize secondary replica that is using backup, copy and restore database operation. We use this feature while creating the always on availability group. Although Microsoft has given this feature, but it is not suitable for very big databases or if you have multiple databases on your SQL Server instance because this feature is a single-threaded process that affects performance and can take long hours to initialize your secondary replica.
Can you explain the limitation of Basic availability groups?
Basic availability groups replace database mirroring features. It provides us to maintain a single secondary replica. Below is its main limitation when we compare it with standard availability groups.
- Only 2 replicas can be created. One primary replica and another one is a secondary replica
- Only 1 database will work as an availability database just like in database mirroring
- Secondary replica cannot be used for reading transactions, database backups, or other database maintenance activities
- Basic availability groups cannot be part of distributed availability groups
- We cannot upgrade basic availability groups to standard availability groups. We need to drop them and reconfigure standard availability groups from scratch
What is Enhanced Database Failover in Always on availability group?
Microsoft has added a new feature in SQL Server 2016 availability groups where you can configure your databases participating in availability groups to failover if they are unable to do write operations. Earlier versions of SQL Server were not triggering failover if any database from the availability group was unable to write operation. It is also called database level health detection in the availability group. This is an optional feature that can be configured during creating an availability group or you can even configure it by running the below ALTER statement for existing availability groups.
|
1 2 3 4 5 |
--Enable Enhanced database failover on an existing availability group ALTER AVAILABILITY GROUP [AGNAME] SET (DB_FAILOVER = ON) --Disable Enhanced database failover on an existing availability group ALTER AVAILABILITY GROUP [AGNAME] SET (DB_FAILOVER = OFF) |
How do you know whether Enhanced Database Failover is configured or enabled for your availability group or not?
We can get this information by accessing a dynamic management view sys.availability_groups. This DMV has a column named DB_Failover, if the value of this column is 0 it means enhanced database failover is not enabled and if the value of this column is 1 then enhanced database failover is enabled for that availability group.
Can we run DBCC CHECKDB on the secondary replica of the availability group?
Yes, we can execute CHEKDB on a secondary replica for database integrity check. This will ensure the integrity of the database hosted on secondary replica only. This execution will not guarantee the integrity of the database hosted on a primary replica as a primary replica uses a different set of storage systems to host their databases.
Can we create database snapshots of a secondary database in the availability group?
Yes, database snapshots can be created of primary or secondary databases if their role state is either PRIMARY or SECONDARY. Database snapshots cannot be created if the role state is RESOLVING.
You have created a database snapshot on the primary replica. What would happen to the database snapshot if the primary replica failed over to the secondary replica?
Database snapshots will not have any impact. They will be there on previous primary replica and you can use them for your requirements.
Can we get how much space is needed in the tempdb database to run DBCC CHECKDB without running its actual execution?
DBCC CHECKDB heavily uses a tempdb database to temporarily store data and perform activities that are needed for integrity check like reading each data page and ensuring there is no corruption. We can use DBCC CHECKDB WITH ESTIMATEONLY statement to predict the estimated amount of tempdb space before its actual execution.
Can we change the restore mode of the secondary database from NORECOVERY to STANDBY in SQL Server log shipping configuration?
Yes, we can change the secondary database restore mode from restoring to standby to use it for read-only operations. We will use a system stored procedure sp_change_log_shipping_secondary_database to make this change. This stored procedure will take an input value 1 for argument @restore_mode while running it. There are two values for this argument. Use 1 to restore the log with STANDBY and use 0 to restore the log with NORECOVERY.
What is the functionality of Queue Reader Agent in Merge Replication?
Merge Replication does not use a queue reader agent so there is no functionality of queue reader agent in Merge replication.
Can we replicate SQL Server data to other RDBMS systems like Oracle?
Yes, SQL Server replication can be used to publish data from SQL Server to Oracle or Oracle to SQL Server.
Can we prevent replicating DELETE operations for a specific article in Replication?
DELETE statements can be prevented to replicate any articles in SQL Server replication. We can get this done by accessing the properties window of the identified article and choose not to replicate delete statements there.
What would be the impact on Log shipping if the SQL Server Agent job is stopped and not running?
Log shipping relies on agent jobs to do all its activities. If SQL Server Agent is stopped, log shipping will not perform these activities of backup, copy, and restore and can become out of sync if the agent will be stopped for a longer duration.
Please share this article and let us know your feedback in the comment section.
He loves to talk and write about database technologies. He has lead and delivered many projects from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR solutions, automation, and major performance tuning projects.
You can also find him on LinkedIn
View all posts by Manvendra Singh
- Difference between SQL SELECT UNIQUE and SELECT DISTINCT - January 16, 2024
- How to do a group by clause - April 12, 2023
- PostgreSQL vs MySQL: Understanding their differences - March 13, 2023