70+ MS SQL DBA Interview Questions and Answers

Yes, the user who deleted the table can be identified using the transaction log file.

• To find out the user who deleted the table, you can query the transaction log file using the fn_dblog function.

• The transaction log contains information about all the transactions performed on the database, including the table deletion.

• By analyzing the log records, you can identify the specific transaction that deleted the table and retrieve the associated user information.

• Restoring the table ...read more

The default port of SQL Server is 1433. Yes, we can change the default port by modifying the SQL Server Configuration Manager.

• The default port for SQL Server is 1433.

• To change the default port, open SQL Server Configuration Manager.

• Navigate to SQL Server Network Configuration and select Protocols for the desired SQL Server instance.

• Right-click on TCP/IP and choose Properties.

• In the IP Addresses tab, scroll down to the IPAll section.

• Change the TCP Port value to the desired por...read more

Point-in-time recovery is possible up to 07:30 PM with the given backup strategy.

• Full backup at midnight captures the database state at that time.

• Log backups every hour allow recovery to any point in time within the backup window.

• To recover to 07:30 PM, ensure log backups are taken up to that time.

• Example: If the last log backup was at 07:00 PM, you can restore to 07:30 PM using the log backup taken at that hour.

The default isolation level in SQL Server is READ COMMITTED. It ensures that each transaction sees only committed data.

• The default isolation level in SQL Server is READ COMMITTED.

• READ COMMITTED ensures that each transaction sees only committed data.

• It provides a balance between concurrency and data consistency.

• Under READ COMMITTED, a transaction can read data that has been modified by another transaction but not yet committed.

• However, it cannot read uncommitted data or data m...read more

CheckDB runs DBCC CHECKDB command in the background.

• CheckDB is a command used to check the logical and physical integrity of all objects in the specified database.

• DBCC CHECKDB is the command that CheckDB runs in the background.

• DBCC CHECKDB checks the allocation and structural integrity of all the objects in the specified database.

• DBCC CHECKDB also checks for common errors like torn pages, index and data page corruptions, etc.

Blockings occur when one transaction holds a lock on a resource, preventing other transactions from accessing it. Deadlocks are a specific type of blocking where two or more transactions are waiting for each other to release resources.

• Blockings happen when one transaction holds a lock on a resource and other transactions are blocked from accessing it.

• Deadlocks occur when two or more transactions are waiting for each other to release resources, resulting in a deadlock situatio...read more

Updating statistics is necessary after index rebuild or reorganize to ensure query optimization.

• Statistics provide information about the distribution of data in a table or index.

• Index rebuild or reorganize can change the distribution of data, making old statistics inaccurate.

• Outdated statistics can lead to poor query performance.

• Updating statistics after index rebuild or reorganize ensures query optimization.

• Maintenance plans can be set up to automate this process.

The database size I have used in my previous project was approximately 500 GB.

• The database size was around 500 GB.

• It contained various tables, indexes, and stored procedures.

• The data included millions of records from different sources.

• We regularly optimized the database to ensure efficient performance.

• Backup and recovery strategies were implemented to safeguard the data.

Parameter (-1) in DBCC TRACEON(1204,-1) is used to enable deadlock tracing for all sessions.

• DBCC TRACEON(1204,-1) enables deadlock tracing for all sessions

• The parameter -1 specifies that the trace flag should be enabled for all sessions

• Deadlock tracing helps identify and resolve deadlocks in SQL Server

Check if the missing msi files are required for the patch. If yes, download and install them.

• Verify if the missing msi files are essential for the patch

• Check if the msi files are available in the original installation media or backup

• If not, download the missing msi files from the vendor's website

• Install the missing msi files before applying the patch

Summary.txt contains summarized information while Detail.txt contains detailed information.

• Summary.txt provides a brief overview of data while Detail.txt provides a more comprehensive view.

• Summary.txt may contain aggregated data while Detail.txt contains individual data points.

• Summary.txt is useful for quick analysis while Detail.txt is useful for in-depth analysis.

• Example: Summary.txt may contain total sales for a month while Detail.txt contains sales data for each day of th...read more

Master and slave are terms used in database replication. Master is the primary database and slave is a copy of it.

• Master database is the primary database that is used for read and write operations.

• Slave database is a copy of the master database that is used for read-only operations.

• Replication is the process of copying data from one database to another.

• Replication can be one-way or two-way.

• In one-way replication, changes made to the master database are copied to the slave dat...read more

Deadlocks in SQL Server can be identified using SQL Server Profiler or by querying the system_health extended event session.

• Use SQL Server Profiler to capture deadlock events

• Query the system_health extended event session to view deadlock graphs

• Use sp_whoisactive to identify blocking and deadlocking processes

• Enable trace flag 1222 to capture deadlock information in the SQL Server error log

To resolve TempDB full issue, identify the cause and take appropriate action.

• Identify the cause of TempDB full issue using DMVs or third-party tools

• Check for long-running transactions or open transactions

• Check for large sorts or hash joins

• Increase the size of TempDB or add more files

• Move TempDB to a faster disk

• Restart SQL Server to clear TempDB

• Modify application code to reduce TempDB usage

The command for Tail log backup is BACKUP LOG WITH NORECOVERY

• Use the BACKUP LOG command to create a tail log backup

• Add the WITH NORECOVERY option to allow further log backups

• Tail log backups are used to capture any transactions that occurred after the last log backup

• Syntax: BACKUP LOG database_name TO disk = 'backup_device' WITH NORECOVERY

Issues faced in log shipping

• Network latency causing delays in log shipping

• Log backups not being taken frequently enough

• Failure to restore logs due to mismatched log backups

• Lack of monitoring and alerting for log shipping failures

Isolation levels in SQL Server determine how transactions interact with each other.

• There are five isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, and SERIALIZABLE.

• Each level has its own trade-offs between concurrency and consistency.

• The default isolation level is READ COMMITTED.

• Isolation levels can be set at the transaction level or for the entire database.

• For example, the SNAPSHOT isolation level allows for consistent reads even when data is be...read more

I used the ticketing tool to track and manage database-related issues and requests.

• Create and assign tickets for database-related issues and requests

• Monitor ticket status and update as necessary

• Communicate with stakeholders regarding ticket status and resolution

• Close tickets once issues are resolved

• Generate reports on ticket volume and resolution time

Yes, replication is the process of copying and distributing data from one database to another.

• Replication is used to improve data availability, scalability, and disaster recovery.

• It involves a publisher database that sends data to one or more subscriber databases.

• There are three types of replication: snapshot, transactional, and merge.

• Snapshot replication copies the entire database to the subscriber.

• Transactional replication sends only the changes made to the database since t...read more

Point in time recovery is the ability to restore a database to a specific moment in time.

• It allows for recovery of data up to a specific point in time.

• It requires regular backups and transaction logs.

• It is useful in case of accidental data deletion or corruption.

• It can be done manually or through automated tools.

• Example: Restoring a database to its state before a specific transaction occurred.

SQL Profiler is a tool used to capture and analyze SQL Server events and activities.

• SQL Profiler captures events such as queries, stored procedures, and errors.

• It can be used to troubleshoot performance issues and optimize queries.

• Profiling can be done on a live server or on a trace file.

• Events can be filtered and grouped for easier analysis.

• SQL Profiler has been replaced by Extended Events in newer versions of SQL Server.

Our backup strategy includes full backups weekly, differential backups daily, and transaction log backups every 15 minutes.

• Weekly full backups

• Daily differential backups

• Transaction log backups every 15 minutes

• Backups stored on separate disk

• Regular testing of backups for restoration

We use ServiceNow as our ticketing tool.

• ServiceNow is a cloud-based platform that offers IT service management (ITSM), IT operations management (ITOM), and IT business management (ITBM) solutions.

• It allows us to manage incidents, problems, changes, and service requests in a single system.

• We can also track the status of tickets, assign them to team members, and set priorities and deadlines.

• ServiceNow also provides reporting and analytics capabilities to help us monitor perform...read more

Database migration involves upgrading a lower version of a database to a higher version.

• Ensure compatibility between the source and target database versions

• Backup the source database before migration

• Use a migration tool or script to transfer data and schema

• Test the migrated database thoroughly before deploying to production

Quorum disk is a shared disk used in a Windows Server Failover Cluster to store cluster configuration data.

• Advantages: Provides a single point of reference for cluster configuration data, ensures consistency of data across all nodes, enables automatic failover of cluster resources.

• Disadvantages: Single point of failure, requires additional hardware, can be a bottleneck for cluster performance.

• Example: In a 3-node cluster, a quorum disk can be used to store the cluster configu...read more

Explanation of differences between logshipping, mirroring, tempdb corruption, master db corruption, and agents used in replication

• Logshipping involves copying and restoring transaction logs from one server to another for disaster recovery.

• Mirroring involves maintaining an exact copy of a database on a secondary server for failover purposes.

• Tempdb corruption can occur due to hardware failures, software bugs, or other issues affecting the temporary database.

• Master db corruption...read more

Replication is the process of copying and distributing data from one database to another.

• Types of replication include snapshot replication, transactional replication, and merge replication.

• Snapshot replication copies the entire database at a specific point in time.

• Transactional replication copies only the changes made to the database since the last replication.

• Merge replication combines changes made to the database at different locations.

• Replication can be used for backup, re...read more