SQL Server DBA Interview Questions

Here, I am going to list out the SQL Server DBA level interview questions which myself/my friends have faced in real-time. Topics cover SQL Server 2000 to 2008. They are listed out random as and when it comes to my memory. I will try adding answers to these later on !

1. What is the difference between cluster installation for SQL Server 2005 and SQL Server 2008?

2. Log shipping has been set up new and is running fine. All on a sudden during the after hours, it starts failing. What could be the possible reasons?

3. There is excessive performance issues on a database server. What are the steps taken for the analysis and resolution?

4. Disk drive where data files (.mdf and .ndf) reside have crashed. We need to do the point in time recovery. List the steps taken in order to achieve this.

5. What would be the ideal disk layout for a SQL Server Database installation?

6. Running SQL Profiler for troubleshooting is causing high performance issues. What are the alernatives?

7. What all are the possible methods by which we can restore a database to another instance?

8. What is High Availability and what solutions could be implemented on SQL Server to achieve this?

9. You need to restart SQL Server services on an Active node in a two node cluster. How would you do it?

10. How would you patch SQL Servers in a fail-over cluster?

11. What is /3G, /PAE & AWE?

12. How to optimise memory usage in a multi instance SQL Server?

13. What is the default port in SQL Server, how to find it and how to reconfigure it?

14. What all are the system databases in SQL Server 2008?

15. What is the difference between index Re-org and index Rebuild?

16. What is the fundamental unit of storage in SQL Server data files and what is it’s size?

17. What is the fundamental unit of storage in SQL Server log files and what is it’s size?

18. How to perform point in time restore for a database in Simple Recovery Model?

19. Difference between Truncate and Delete?

20. How to troubleshoot a poor performing query?

21. What is blocking / deadlock ? How to troubleshoot and fix them?

22. List out the maintenance plan steps that you would accommodate for any database?

23. DB has full backup taken and then a transaction updates 100,000 rows and then the transaction is rolled back. If we take a differential backup at this point, will that backup lot of data or less and Why.

24. What is the min permission required to Display Estimated Execution Plan?

25.

MvM