What are the interview questions for SQL Server DBA?
SQL Server DBA Interview questions and answers
- 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 many types of data files exist in SQL Server?
three types
SQL Server databases have three types of files, as shown in the following table. Contains startup information for the database and points to the other files in the database. Every database has one primary data file.
What is MDF and LDF file?
MDF stands for Main Database File and contains all the information in a database. LDF records all the transactions and changes to the database. The ldf is critical for disaster recovery.
What is SQL Server administration?
Database administrators need to handle all facets of managing Microsoft SQL Server ranging from installation and patching to creating databases to managing permissions that allow users to use the databases. However, more than anything, they are responsible for protecting the data and maintaining performance levels.
What is profiler in SQL?
SQL Server Profiler is an interface to create and manage traces and analyze and replay trace results. Events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when diagnosing a problem. SQL Trace and SQL Server Profiler are deprecated. The Microsoft.
How can check database performance in SQL Server?
To perform monitoring tasks with SQL Server Management Studio
- View the SQL Server Error Log (SQL Server Management Studio)
- Open Activity Monitor (SQL Server Management Studio)
- Monitoring performance by using the Query Store.
What are the wait types in SQL Server?
There are two main categories for the SQL Server Wait Statistics; the Single Wait type, where the query is waiting for CPU resource availability and the Resource Wait type, where the query is waiting for a specific resource availability, such as I/O or Memory resources.
What is NDF in SQL?
ndf extension is a secondary database file used by Microsoft SQL Server to store user data. NDF is secondary storage file because SQL server stores user specified data in primary storage file known as MDF.
What is the maximum number of files in one database?
The sum of the number of all objects in a database can’t exceed 2,147,483,647. Objects include tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database can’t exceed 2,147,483,647.