50+ 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

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

The backup strategy for the biggest database I handled involved regular full backups, daily differential backups, and hourly transaction log backups.

• Regular full backups were performed to capture the entire database.

• Daily differential backups were taken to capture the changes since the last full backup.

• Hourly transaction log backups were taken to capture the changes since the last differential backup.

• Backups were stored on separate storage devices to ensure data redundancy.

• Ba...read more

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

Index reorganize and rebuild based on fragmentation level

• For fragmentation level < 5%, use reorganize

• For fragmentation level > 30%, use rebuild

• For fragmentation level between 5% and 30%, choose based on table size and usage

• Use ALTER INDEX statement to perform reorganize or rebuild

• Monitor fragmentation level regularly to maintain optimal performance

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

Yes, I use SQL Server Management Studio (SSMS) and SQL Server Profiler for monitoring SQL Server.

• I use SSMS to monitor server activity, query performance, and resource usage.

• I use SQL Server Profiler to capture and analyze SQL Server events and performance data.

• I also use third-party tools like SolarWinds Database Performance Analyzer and Redgate SQL Monitor for more advanced monitoring and alerting.

• Regularly monitoring SQL Server helps identify and resolve performance issues...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

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.

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

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

New SQL Server installation and configuration

• Determine hardware and software requirements

• Install SQL Server software

• Configure server settings and security

• Create and configure databases

• Set up backup and recovery procedures

DB defragmentation is a process of reorganizing data on disk to improve performance.

• DB defragmentation can be performed using built-in tools like DBCC INDEXDEFRAG and DBCC DBREINDEX in MS SQL Server.

• DB defragmentation can also be done using third-party tools like Ola Hallengren's Maintenance Solution.

• Types of defragmentation include index defragmentation and disk defragmentation.

• Index defragmentation reorganizes the physical order of index pages to improve query performance.

• D...read more

Failover types in cluster and always on

• In a cluster, failover can be automatic or manual

• In Always On, failover can be synchronous or asynchronous

• Automatic failover in a cluster requires a quorum of nodes to be available

• Synchronous failover in Always On ensures data consistency but can have higher latency

• Asynchronous failover in Always On has lower latency but may result in data loss

Functions return a single value, while stored procedures can perform multiple operations.

• Functions return a single value, while stored procedures can return multiple values or perform operations.

• Functions can be used in SELECT statements, while stored procedures cannot be used in SELECT statements.

• Functions cannot modify the database state, while stored procedures can modify the database state.

• Functions can be called from within stored procedures, while stored procedures cann...read more

Migrating from SQL Server 2008 to 2019 involves upgrading the database, transferring data, and testing for compatibility.

• Perform a side-by-side upgrade by installing SQL Server 2019 on a new server and transferring databases using backup and restore or detach and attach methods.

• Use the Data Migration Assistant tool to assess compatibility issues and plan for any necessary changes.

• Update applications and scripts to ensure compatibility with SQL Server 2019 features and changes...read more

Steps involved in migration process

• Assess current environment and identify migration goals

• Choose migration method (manual, scripted, tool-based)

• Prepare target environment and perform data migration

• Test migrated data and applications for functionality

• Implement post-migration tasks like user training and support

To configure a linked server in MS SQL, you can use SQL Server Management Studio or T-SQL commands.

• In SQL Server Management Studio, expand Server Objects, right-click Linked Servers, and select New Linked Server.

• Specify the linked server name, server type, connection settings, and security options.

• In T-SQL, you can use the sp_addlinkedserver stored procedure to create a linked server.

• You can also use sp_addlinkedsrvlogin to configure login mappings for the linked server.

My expected CTC is negotiable based on the job responsibilities and market standards.

• My expected CTC is based on my experience, skills, and the job responsibilities.

• I am open to discussing the salary range during the interview process.

• I am looking for a competitive salary that aligns with industry standards.

• I am flexible and willing to consider other benefits in addition to the salary.

Avoid deadlocks by setting proper isolation levels, minimizing transaction duration, using indexes, and handling errors.

• Set appropriate isolation levels to prevent locks from escalating to deadlocks

• Minimize transaction duration to reduce the likelihood of deadlocks

• Use indexes to improve query performance and reduce the chance of deadlocks

• Handle errors gracefully to avoid leaving transactions in an inconsistent state

Start up parameters are used to configure the behavior of SQL Server when it starts up.

• Start up parameters are specified in the SQL Server Configuration Manager.

• Common start up parameters include -d (database files location), -e (error log location), -m (single user mode), -T (trace flags), etc.

• Start up parameters can be modified to customize the behavior of SQL Server during startup.

• Example: -dC:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\

• Example: -eC:...read more

Setting up Always On group involves configuring multiple SQL Server instances for high availability and disaster recovery.

• Configure Windows Failover Cluster

• Install SQL Server on each node

• Enable Always On Availability Groups feature

• Create availability group and add databases

• Configure listener for client connections

Backup in SQL Server is the process of creating a copy of the database to protect against data loss.

• Backups can be full, differential, or transaction log backups.

• Full backups contain all the data in the database.

• Differential backups contain only the data that has changed since the last full backup.

• Transaction log backups capture all the transactions that have occurred since the last transaction log backup.

• Backups are essential for disaster recovery and data protection.

CTE is a temporary result set while views are permanent virtual tables.

• CTE is defined using WITH keyword, while views are defined using CREATE VIEW statement.

• CTEs can be referenced within the same query, while views can be referenced in multiple queries.

• CTEs are not stored as objects in the database, while views are stored as objects.

• CTEs are mainly used for complex queries or recursive queries, while views are used for simplifying queries and reusability.

Log shipping is a method used to maintain a secondary copy of a database for disaster recovery purposes.

• Transaction logs are backed up from the primary database and then copied to the secondary server.

• The transaction logs are then restored on the secondary server to keep it synchronized with the primary database.

• Log shipping can be set up to occur at regular intervals to minimize data loss in case of a failure.

• It is a common method used for disaster recovery and high availabi...read more

Implicit conversion is the automatic conversion of data types by SQL Server.

• Occurs when SQL Server automatically converts data from one data type to another

• Can lead to performance issues if not handled properly

• Examples include converting a string to an integer or a date to a string

Isolation level determines how transactions interact with each other in a database.

• Isolation levels include Read Uncommitted, Read Committed, Repeatable Read, Serializable.

• Higher isolation levels provide more data consistency but may lead to more locking and potential performance issues.

• Example: Using Read Uncommitted isolation level allows reading uncommitted data, while Serializable ensures data integrity but may lead to more blocking.

• Understanding isolation levels is cruci...read more

I am expecting a competitive salary based on my experience, skills, and the market rate for MS SQL DBAs.

• I am looking for a salary that reflects my expertise in MS SQL Server administration.

• I am open to negotiation based on the specific responsibilities and benefits offered.

• I have researched the average salary range for MS SQL DBAs in this location and industry.

• I am also considering other factors such as opportunities for growth and professional development.

Expected CTC should be based on industry standards, experience, skills, and responsibilities of the role.

• Consider industry standards for MS SQL DBA roles

• Take into account your level of experience and expertise

• Factor in the responsibilities and workload of the position

• Research average salaries for similar roles in your location

• Be prepared to justify your expected CTC based on your qualifications and value you bring to the role

DBA stands for Database Administrator. They are responsible for managing and maintaining databases.

• Responsible for ensuring the availability and performance of databases

• Implementing security measures to protect data

• Backup and recovery of data

• Monitoring database activity and optimizing performance

• Troubleshooting and resolving database issues

A pull request is a method of submitting contributions to a project on GitHub for review and merging.

• Pull requests are commonly used in version control systems like Git to propose changes to a codebase.

• They allow developers to collaborate and discuss changes before merging them into the main branch.

• Pull requests typically include a description of the changes, related issues, and any necessary tests or documentation.

• Reviewers can provide feedback, suggest modifications, and ul...read more

Stateful set is a Kubernetes controller that manages the deployment and scaling of a set of pods with persistent storage.

• Stateful set ensures that each pod in the set has a unique identity and stable network identity.

• Stateful set pods are created in order and are not replaced until they are deleted.

• Stateful set is commonly used for stateful applications like databases where each pod requires persistent storage.

• Example: Running a MySQL database on Kubernetes using a stateful s...read more