There are total 185 questions are answered under SQL Server DBA category in the book “SQL THE ONE”. In this chapter we can see questions and answers on DBCC, Common, Snapshots, Transaction Log, Shrink, Boot Page, XML, Triggers, Identity, DBA Responsibilities and Database Design. Below are the Top 30 SQL Server DBA Interview Questions:
1. How to know the estimated TEMPDB space required for running CHECKDB command?
2. We are using In-Memory feature for one of our production database. The database contains memory-optimized tables. Can we run DBCC CHECKDB on this database? Does it support?
3. What are the most common issues a SQL DBA should deal with as a part of DBA daily job?
4. How to recover a database that is in suspect stage?
5. What is your approach in AUTO_GROWTH option while creating a new database?
6. Which operation is faster COMMIT or ROLLBACK? Why?
7. You got an alert by saying that a SQL Agent job has been failed on one of the server. You need to find the root cause. What is approach?
8. SQL Server is not responding. What is action plan?
9. How many data files I can put in TEMPDB? What is the effect of adding multiple data files?
10. What is the difference between Temp tables and Derived tables, Table variables and temporary tables?
11. What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?
12. Have you ever faced any issues with SQL Server Error Log? How do you manage SQL Server Error log
13. What is SQL DUMP? Have you ever dealt with this?
14. We are not able to connect to SQL Server. Can you list out what are all the possible issues and resolutions?
15. One of the disk drive is 95% full within a very less time. Now you started getting disk full alerts and you need to free up the space. What are the different options you try to clear the space? And what are the possible reasons that cause the sudden disk full issues?
16. Let’s say we are not receiving emails from last 4 hours as there is a problem with database mail. Now we would like to restart the database mail. Have you ever faced this issue? If yes how do you restart the database mail?
17. Is it possible to configure Database Mirroring/Log Shipping when servers are having different drive structure?
18. Where are the real time situations where we can use database snapshots?
19. You got an alert that says a user database log file is growing and it reached 85%. How do you handle the situation?
20. One of our databases is in simple recovery mode but still the log file is growing and it’s not getting reduced after CHECKPOINT and SHRINK. Why it’s log file is not getting reduced when the database is in simple recovery mode?
21. Can you explain what happens to VLF’s when we see the error “Log File is full”?
22. How to know the number of VLF created on a given database log file?
23. How do you troubleshoot transaction log full issue?
24. If SHRINK DATAFILE is having the bad impact why it’s still available in SQL Server?
25. We have a situation where we need to perform delete on large portion of a database sized 4.3 TB. How to avoid shrink operation?
26. Let’s say our database BOOTPAGE is corrupted. How do you recover the DB?
27. Documentation is part of a DBA responsibility. Can you describe what all the things a DBA should document?
28. What is SLA, incident, Change Request, Service Request, Problem Request?
29. Have you ever heard the words “RTO,” “RPO” and “RLO”?
30. We know we cannot use Order by inside a view. Can you tell me why?
Summary:
These Top 30 SQL Server DBA Interview Questions are the sample questions randomly collected from the book “SQL THE ONE”.
Please share your feedback and comments on below comments section or reach me on sqltheone@gmail.com. We wish you all the best for the next interview.
Here you can find more Details about the book “SQL THE ONE”.
Nice ones…