SQL DBA Interview: Master Top 30 Questions for Success

Point in Time Recovery in SQL Server:

Question 1: What is point-in-time recovery in SQL Server?

Answer: Point-in-time recovery enables the restoration of a database backup at any specific time, applicable to databases under the full or bulk-logged recovery model. Point-in-time recovery is not possible if the database uses the bulk-logged recovery model and the transaction log backup contains bulk-logged changes.

Point-in-time recovery allows to restore a database backup at any point of time. This type of recovery is applicable only to databases that run under the full or bulk-logged recovery model. If the database runs under the bulk-logged recovery model and transaction log backup contains bulk-logged changes, the recovery to a point-in-time is not possible.

Using SSMS:

Using Query:

RESTORE LOG your_database FROM DISK = 'log_16_00.bak' WITH STOPAT = '2015-11-19 15:09:55.000', RECOVERY

Let say example, Our backup strategy is weekly once full backup, daily differential backup and each an hour we are taking transaction log backup. Some crucial data were deleted at 15:10 and the last transaction log backup was made at 16:00. The transaction log backup from 16:00 gives us the opportunity to restore the database to its state at 15:09:55. In this case, only 40 minutes of updates will be lost.

Identifying Running Queries:

Question 2: How do you identify running queries on your server?

Answer: Running queries can be traced using SQL Profiler, DMV, and DMF queries. An example DMV query is provided to retrieve information about currently running queries, including session ID, status, login name, and more.

DMV and DMF Query

SELECT er.session_id AS SPID,
  ses.STATUS AS Status,
  ses.login_name AS LoginName,
  ses.host_name AS HostName,
  er.blocking_session_id AS BlkBy,
  DB_Name(er.database_id) AS DBName,
  er.command AS CommandType,
  OBJECT_NAME(st.objectid) AS ObjectName,
  er.cpu_time AS CPUTime,
  er.start_time AS StartTime,
  CAST(GETDATE() - er.start_time AS TIME) AS TimeElapsed,
  st.text AS SQLStatement
FROM sys.dm_exec_requests er
    OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
    LEFT JOIN sys.dm_exec_sessions ses
    ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
    ON con.session_id = ses.session_id
WHERE   st.text IS NOT NULL

Understanding Locking, Blocking, Deadlocking:

Question 3: Explain locking, blocking, and deadlocking in SQL Server.

Answer: Locking occurs when reading or modifying data, leading to potential blocking. Blocking happens when one process must wait for another to complete. Deadlocking occurs when two processes are mutually blocked, each waiting for the other to release a lock.

Identifying SQL Server Performance Issues:

Question 4: How do you identify performance issues in your SQL Server?

Answer: Performance issues can be identified using tools like SQL Profiler, Execution Plan, DMV, DMF queries, Activity Monitor, SP_WHO2, SP_READERRORLOG, and Performance Monitor.

Understanding Fill Factor in SQL Server:

Question 5: What is Fill Factor, and how is it used in SQL Server?

Answer: Fill Factor determines the percentage of data to fill on each index page. It is configured while creating an index on a table, with the default value being 100. Choosing an optimal Fill Factor value improves performance.

Read More: Fill Factor in SQL Server: Optimizing Index Performance

Copy Only Backup in SQL Server:

Question 6: Explain Copy Only Backup in SQL Server.

Answer: A copy-only backup is independent of the regular backup sequence and is useful when taking a backup that should not affect the existing backup sequence. It ensures a continuous log chain for transaction log backups.

 

Tail Log Backup in SQL Server:

Question 7: What is a tail-log backup in SQL Server?

Answer: A tail-log backup captures log records not yet backed up, preventing work loss and maintaining the log chain. It is crucial for recovering a database to its latest point in time.

Verifying Corrupted Database Backup in SQL Server:

Question 8: How do you ensure/verify that a database backup file (.bak) is not corrupted?

Answer: Use the RESTORE VERIFYONLY command to check the integrity of the database backup file. 

 

RESTORE VERIFYONLY FROM DISK = 'c:\backup\dbname.bak'; 

Recovering Database from Suspect Mode:

Question 9: How do you recover a database from suspect mode in SQL Server?

Answer: Recovery involves resetting the suspect flag, performing a consistency check, bringing the database into single-user mode, taking a complete backup, attempting a repair with potential data loss, and finally bringing the database into multi-user mode.

Step 1: Open SQL Server Management Studio and connect your database

Step 2: Select the New Query option and turn off the suspect flag on the database and set it to EMERGENCY
EXEC sp_resetstatus 'db_name';
ALTER DATABASE db_name SET EMERGENCY


Step 3: Perform a consistency check on the master database
DBCC CHECKDB ('database_name')

Step 4: Bring the database into the Single User mode and roll back the previous transactions
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE


Step 5: Take a complete backup of the database
Attempt the Database Repair allowing some data loss
DBCC CheckDB ('database_name', REPAIR_ALLOW_DATA_LOSS)

Step 6: Bring the database into the Multi-User mode
ALTER DATABASE database_name SET MULTI_USER

Refresh the database server and verify the connectivity of database

Code:

EXEC sp_resetstatus 'DATABASE_NAME';
GO
ALTER DATABASE DATABASE_NAME SET EMERGENCY
GO
DBCC checkdb('DATABASE_NAME')
GO
ALTER DATABASE DATABASE_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CheckDB ('DATABASE_NAME', REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE DATABASE_NAME SET MULTI_USER
GO

Reasons for Database Suspect Mode:

Question 10:What are the reasons a database can go into suspect mode? 

Answer: Suspect mode can result from data or log file corruption, improper shutdown, lack of disk space, or unsuccessful rollback or roll-forward operations.

11. What are the Recovery Models available in SQL Server and usage?

SQL Server backup and restore operations occur within the context for the recovery model of the database. Recovery models are designed to control the transaction log maintenance.

FULL:

1. Full recovery model will trace all the database operations to the log file.
2. The full recovery model is always best one for production servers.
3. In the full recovery model we can take all kinds of backups like full, differential, transaction log and point in time recovery(PTR) too.

BULK LOGGED: Bulk-logged recovery model is similar to FULL recovery model with the exception that bulk data modification operations(Index Creation, SELECT INTO, INSERT SELECT, BCP, BULK INSERT) are minimally logged in this case and hence it reduces the performance impact but at the same time, you might not be able to do point-in-time restore.

 

SIMPLE: Simple recovery mode will not keep the log fils permanently. When a SQL statement executes, changes are written to the data and log files, but they are not kept in the log file for long before being truncated.

12. How to recover the master database if corrupted?

The Master database is most important and main database in SQL server because all the logins and server configuration details are stored in this database. If the Master database has been corrupted then we cannot login even SQL server as well. There are two main ways to recover the master database from corrupted.

 

Restore Master Database from Backup

To recover the master database by backup, we should have a complete backup of master database.
First of all, We have to start the SQL server instance in single user mode. So we have to change the start up parameter of SQL server instance by using SQL server Configuration Manager.

Step 1: Open SQL Server Configuration Manager and choose SQL Server Services.

Step 2: Right click the service and choose the properties.

Step 3: In the properties window, choose the startup parameters tab and add a parameter "-m" as shown picture below.

Step 4: Start the SQL server service, this will start in single user mode.

Step 5: Open the Command prompt, connect the service using SQLCMD and restore the database by query.

Step 6: RESTORE DATABASE master FROM DISK = 'D:\Backupup\master.bak' WITH REPLACE

 

Step 7: After completing above script, remove the prefix (-m) from start up parameter which we did in third step.

Rebuild Master Database in SQL Server

If we don't have master database backup then we can rebuild the master database from SQL server installation path.

Step 1: Open the command prompt and try to change the SQL Server installation directories to the location of setup.exe file on the local server. However, its default location on the server is C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Release. But the 130 folder name would be varying based on the SQL server version which we installed.

Step 2: Paste the below syntax and execute the same

 

.\setup /ACTION=REBUILDDATABASE /INSTANCENAME=<instance_name> /SQLSYSADMINACCOUNTS=<admin_account> /SAPWD=<sa_password>

Step 3: As the rebuilding process completes, it returns the command prompt without any message.

Step 4: To ensure this rebuild, we can view the summary.txt log file. The default location of the summary.txt log file is: C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Logs

13. What is extended events in SQL server?

SQL Server Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.

Extended Events provide a way of generic tracing and troubleshooting framework which allows deeper and more granular level control of tracing which was not possible using earlier methods like DBCC, SQL Trace, Profiler and etc. As shown in picture above, we can create a session to trace the each events from the SQL server which can be stored on local drive.

14. What is Management Data warehouse in SQL Server?

The Management Data Warehouse is used as a data store to house the performance data collected on a scheduled basis, that allows you to analyze historically what has been happening in your environment. This can be used to troubleshoot performance issues, or to identify when a change occurred that resulted in a performance issue. Alternatively this can also assist in capacity planning with your environments. To be able to have this information we need to configure a Management Data Warehouse and setup the scheduled collection of appropriate data.

15. What is Resource Governor in SQL Server?

SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.

16. What are the operation modes available in database mirroring?

1. high performance(Asynchronous)
   Commit changes at the principal and the transfer them to the mirror. It gives high performance compare to other operation mode but there should be a gap between principal and mirror database.




2. High safety without automatic fail over(Synchronous)
   Always commit the changes at the both the principal and mirror. We have to bring up the mirror database as principal by manual if the principal is unavailable.
   




3. High safety with automatic fail over(Synchronous)
   Requires a witness server instance. Commit changes at both the principal and mirror if both are available. The witness controls automatic fail over to the mirror if the principal becomes unavailable.

17. Can we configure the database mirroring on System defined databases?(Master, Model, MSDB, TempDB)

No. We can configure the database mirroring only on user defined database.

18. If we are adding any users on Primary database, will it be copied on secondary database?

Yes. The database mirroring captures all the changes from primary database and applying the same on secondary database.

19. How to bring the Mirror DB as online if the principal DB is down?

Its depends on the operation mode which we configured on database mirroring and we have to follow the different steps to bring the database as online for each mode.

 

1. High safety with automatic fail over(Synchronous)
   
   In this mode we don't worry about the fail over and the mirror server will form a quorum with witness and will initiate an automatic fail over. By default, the level of transaction safety is set to FULL and we have to turn on the transaction safety level if we modified.
   

   ALTER DATABASE dbname SET SAFETY FULL -- dbname is name of the Mirrored database.
   ALTER DATABASE dbname SET SAFETY OFF -- Turn off the Safety
   





2. High safety without automatic failover(Synchronous)
   
   In this mode the automatic failover not allowed and we need to do manual intervention to make the mirrored database available.
   

   ALTER DATABASE dbname SET PARTNER OFF
   RESTORE DATABASE dbname WITH RECOVERY
   





3. high performance(Asynchronous)
   
   In this mode we have to perform a force service to make the database service available on the mirror. However, since the safety level is OFF, it is possible that there were transactions that did not make it to the mirror at the time of the failure of the principal.
   

   ALTER DATABASE dbName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
   

20. How will you do manual fail over even the principal database is working fine?

The manual fail over can swap the principal and mirror server roles each other, clients are disconnected from the former principal database and active transactions are rolled back. We can achieve this method in two different ways.

 

1. Using SQL Query

We have to execute this query on principal server and the mirror database becomes the principal and the principal database becomes the mirror.


ALTER DATABASE [Database_Name] SET PARTNER FAILOVER

2. 
   Using SSMS


We can do manual failover in simple steps using SSMS that right click on the database, choose Tasks and select Mirror option from the list. As shown in picture below, we just click on the Fail over button to perform the manual fail over on database mirroring.

21. What are the limitations or drawbacks in Database Mirroring?

1. Data loss is possible if we chosen asynchronous operation mode.
2. Database mirroring supports one to one server pairings.
3. We cannot mirror the database more than one.
4. Database mirroring can be possible only in full recovery mode.
5. We cannot directly execute the query and taking the backup on mirrored database except snapshot taken.
6. It works at database level not server level.

22. How many databases can be mirrored on a single instance of SQL Server?

We can configure 10 databases for 32-bit operating system. The database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session.

The 64-Bit Operating system supports more than 10 database mirroring depends on the number of processors and worker threads.

23. How will you apply the patches or service pack on Always on availability groups Server?

Step 1: Take the backup of your database.

Step 2: Check the consistency of your database using DBCC CHECKDB command.

Step 3: Open your Windows Fail Over Cluster Manager.

Step 4: Identify the primary and Secondary/Replica nodes from Always on Availability Groups.

Step 5: Pause the Secondary node (So that the Primary node knows the secondary is down)

Step 6: Patch the Secondary node.

Step 7: Reboot if required.

Step 8: Check for more patches or updates (reboot if required)

Step 9: If none needed then RESUME the secondary node.

Step 10: Fail over using High Availability Fail over Wizard (SQL Always on Dashboard)

Step 11: Pause the other node (which should be Secondary) on Windows Cluster Manager

Step 12: Apply Patches and updates

Step 13: Reboot if required

Step 14: Check for additional patches (reboot if required)

Step 15: RESUME node again in Windows Fail over Cluster Manager

Step 16: Fail over back to original node if want to keep in perspective (A being primary B Secondary)

24. What are the prerequisites need for transaction replication?

1. Primary key is important one when we are going to configure the Transaction Replication.
2. The Agent service must be started on Publisher, Distributor (Mostly Publisher and Distributor on same server) and Subscriber SQL Server instance.
3. Both SQL Server instances need the replication components installed.

25. What are the different agents available in replication?

1. Snapshot Agent
   Snapshot Agent prepares the schema and the initial bulk copy files of published tables and other objects, stores the snapshot files, and records information about synchronization in the distribution database. The Snapshot Agent is used with all types of replication and it runs at the Distributor.




2. LogReader Agent
   Log Reader Agent moves transactions marked for replication from the transaction log on the Publisher to the distribution database. Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor and connects to the Publisher. The Log Reader Agent is used with transactional replication.




3. Distribution Agent
   The Distribution Agent is used with snapshot replication and transactional replication. It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers. The Distribution Agent runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.




4. Merge Agent
   The Merge Agent is used with merge replication. It applies the initial snapshot to the Subscriber and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The Merge Agent runs at either the Distributor for push subscriptions or the Subscriber for pull subscriptions.




5. Queue Reader Agent
   The Queue Reader Agent is used with transactional replication with the queued updating option. The agent runs at the Distributor and moves changes made at the Subscriber back to the Publisher. Unlike the Distribution Agent and the Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution database.

26. What is Push and Pull subscription?

Push Subscription: Push subscription is pushing the data from publisher to subscribers and the changes are pushed to subscribers on demand, continuously, or on a scheduled basis.

 

Pull Subscription: Pull subscription is requesting the changes from Subscribers to Publisher. This allows the subscribers to pull data from publisher when needed. This is useful for disconnected machines and they can pull the data from publisher when connected.

27. What are the possible reason if the subscription database is not synchronized?

There are a number of possible reasons the subscription database is not synchronized.

 

1. One or more agents are not running or are failing with an error.
2. Publisher and Subscribers may have different version of Stored procedure or Functions.
3. The table might not be included or removed in replication articles list.
4. The replication might be initialized without snapshot and the changes have occurred on the Publisher since the publication was created.

28. Which recovery model is required on a replicated database?

Replication functions properly using any of the recovery models: simple, bulk-logged, or full. Merge replication tracks change by storing information in meta data tables. Transactional replication tracks changes by marking the transaction log, but this marking process is not affected by the recovery model.

29. How to add an article or table on existing publication without reinitialize?

There are few steps needs to be followed to add an article on existing publication without reinitialize.

30. What is the difference between RECOVERY, NO RECOVERY and STAND BY mode?

RECOVERY: The RECOVERY mode rollback all uncommitted transactions and the data file contains only committed transactions. The database is placed as ready to use state and we can access the data from the database. This is the default mode of SQL server database.

NO RECOVERY: The NO RECOVERY mode keeps the all uncommitted transactions and SQL Server does not need to analyze the transaction log and roll back any uncommitted transactions at the end of the restore process. This allows roll forward to continue with the next statement in the sequence. When you then restore the next transaction log, SQL Server has to first apply the contents of uncommitted transaction and restores remains.

STAND BY: Restore transaction log with standby option leaves the database read only mode. The uncommitted transaction are undone and saved in a file so that recover effects can be reversed when database is restored with recovery option.

These SQL DBA interview questions cover a wide range of topics, ensuring a comprehensive preparation for candidates seeking roles in SQL Server database administration. Remember to understand not only the answers but also the underlying concepts and principles to showcase a solid understanding of SQL Server administration.

Also see below articles

Add a new table or article on existing publication without reinitialize the subscription in SQL Server
Implement Best Practice and Performance tuning tips in SSIS and ETL Development