SQL SERVER DBA INTERVIEW QUESTIONS


The list consist of Basic to Advance level SQL Server DBA Interview Questions and Answers.
Please visit this link for our video answers for SQL Server DBA Interview Questions. You might not see Video Answer link for some of them. But Youtube list contains answers for most of them. If you don't see the Video Answer written in front of some of them, hover over the question, you might find the text answer.

All the best with SQL Server DBA Interview:)
  1. What are the Editions of SQL Server available?
  2. What is SQL Server Instance?
  3. What is the difference between default and Named Instance? (Video Answer)
  4. How many SQL Server Instances can be installed on one Server? (Video Answer)
  5. What is Collation? What Collation will you pick while installing SQL Server?
  6. Can you have different Collation on different Databases in SQL Server Instance? (Video Answer)
  7. Can Instance Level Collation be different from Database Collation? (Video Answer)
  8. What are the best practices to place data files, log files and tempdb on Storage?
  9. What are file groups? How would you relate File groups with Partitioned Table?
  10. Is it best practice to have Auto Shrink enable on Database? (Video Answer)
  11. While installing SQL Server instance , you have used default accounts. Later you want to change to Service account, how would you do that? (Video Answer)
  12. What is TDE and why do we use it?
  13. If TDE is enabled on Database, Do we have to write some special code on application side to access data?
  14. What are the steps to enable TDE on database?
  15. Is there any performance overhead because of TDE enabled on Database?
  16. If you need to restore TDE Enabled database to different server, what steps are involved?
  17. What are different types of data compression available in SQL Server?
  18. How would you allocate minimum and maximum memory to SQL Server Instance?
  19. What is the difference between Offline and Emergency state of a Database?
  20. Can you set Database into ReadOnly state?
  21. What is CPU Affinity?
  22. What is IO Affinity? (Video Answer)
  23. What is MAXDOP , How do you change it?
  24. How would you find out who has dropped database?
  25. If you have to find out that how many times the backup of a database is taken, Where will you look for that information?
  26. How would you find open transactions in SQL Server?
  27. How would you find blocked processes in SQL Server?
  28. What is locking in SQL Server? (Video Answer)
  29. How would you trace deadlock in SQL Server? ( Video Answer)
  30. Let's say you have lost the password for sa, How would you recover that? (Video Answer)
  31. What is TUF(Transaction Undo File) file and in which process it is created? (Video Answer)
  32. What is link Server? Can link server be created for Oracle from SQL Server? (Video Answer)
  33. What is sparse column? What are the advantages and disadvantages?
  34. Which DBCC Commands have you used often as SQL Server DBA?
  35. How would you script all SQL Server Agent Jobs? (Video Answer)
  36. How would you script entire database (Tables, SPs,Views etc.) with data?
  37. How would you migrate a database from SQL Server Instance to Another SQL Server Instance?  (Video Answer)
  38. You have taken a backup of a database from SQL Server 2008R2, Can you restore this to SQL Server 2005 Instance?
  39. What will be your strategy when you need to migrate SQL Server 2008 to SQL Server 2012? (Video Answer)
  40. What is difference between Backup/Restore and Detach/Attach Database? (Video Answer)
  41. When should we update Statistics on SQL Server Database and why? (Video Answer)
  42. How would you find out how much space is allocated to Database Log file and how much is used? (Video Answer)
  43. What is Database Engine Tuning Advisor and where do you have used it? (Video Answer)
  44. If you need to Kill all processes related to a Database , how would you do that?
  45. Explain the steps to perform Table Partitioning and best practices? (Video Answer)
  46. How would you determine the version and Edition of SQL Server Instance you are working on? (Video Answer)
  47. What are the steps to Restore a SQL Server Database to a Point in Time? (Video Answer)
  48. How would you shrink the tempdb database in SQL Server? (Video Answer)
  49. Which built in tool you have used to Monitor SQL Server activity? (Video Answer)
  50. How would you identify table level locks in SQL Server? (Video Answer)
  51. What is isolation level and what are four types of them? (Video Answer)
  52. If you need to query Oracle database from SQL Server, how would you do that? (Video Answer)
  53. What are the differences between DMV's and DMF's? (Video Answer)
  54. The tran log of a database has grown huge, How would you shrink the log file? (Video Answer)
  55. What are the important points you consider before coming up with Backup strategy? (Video Answer)
  56. You have installed SQL Server Instance on Window 2008 Server. Total Memory of Server is 64 GB but you need to assign only 12 GB to SQL Server Instance, how would you do that? (Video Answer)
  57. What is the difference Between Index Rebuild and Index Reorganize? (Video Answer)
  58. How would you create a SQL Server Agent job that only should run on 5th business day of each month? (Video Answer)
  59. You have provided some permission on a Database to a users, but user is not able to connect to that Database. Where will you see the error details in SQL Server? (Video Answer)
  60. You have a big database, Before taking the full backup you want to estimate the total time for backup. How would you do that? 
  61. You have received a large( 2 GB in size) .sql file, How would you execute that .sql file on SQL Server?
  62. You need to find out the Job execution history for last ten days, Where will you search for that? (Video Answer)
  63. You are planning to move database from one server to another server. But there are changes the stored procedures are using some cross database queries. How would you find out if any of the Stored Procedure is using DatabaseTest in definition?
  64. Your company has found out that the account SQL Services are running is not what it should be. They want you to change to Service account, How would you do that? (Video Answer)
  65. What is the differences between differential database backup and Transaction log backup? (Video Answer)
  66. What permissions will you grant to a users in SQL Server database, so He/She can truncate table?
  67. What are DDL Triggers? Where and why have you created them? (Video Answer)
  68. How would you perform Schema comparison between two databases? (Video Answer)
  69. You need to perform Data Comparison between two tables,How would you do that? (Video Answer)
  70. You need to backup all the logins and Jobs, How would you do that? (Video Answer)
  71. You have installed Name Instance of SQL Server, After installation you realized that you misspelled the instance name. Can you correct the name of SQL Server Instance without re- installation?
  72. You have installed SQL Server instance on Windows 2008 (MyServerName\MySQLInstance). If your want to change the computer name to only ServerName, Will SQL Server Instance will or You have to re-install SQL Server Instance?
  73. What is DAC (Dedicated Administrator Connection) ? Is it enabled or disabled by default? Why and where we use this feature?
  74. You need to create full backup of all the databases from one of the SQL Server Instance every night. If any new database is created that should also become the part of backup process. What would be your approach to perform this? (Video Answer)
  75. All the sudden, users start complaining that the SQL Server is running slow. What steps you would take to analysis the problem? (Video Answer)
  76. What is by default SQL Server Agent Job history Retention? If you need to keep two week of history for each of the job what steps will you take when some of the jobs run every five minutes and some run one time a day? (Video Answer)
  77. If you need to find all the processes running by specific login, How would you do that? (Video Answer)
  78. How would you find out how many transactions/second SQL Server Instance is performing? (Video Answer)
  79. How would you find currently running queries on SQL Server Instance? (Video Answer)
  80. How would you find orphan users and fix them? (Video Answer)
  81. How would you script user with permission from a SQL Server Database?
  82. Your company has a Database for which the Tran Log grows very fast. What strategies you should adopt to keep the Tran log reasonable? (Video Answer)
  83. What is the difference between Actual Execution plan and Estimated Execution plan? (Video Answer)
  84. What is the difference between Differential Backup and Full Backup? (Video Answer)
  85. What are two Server Authentication modes available in SQL Server? (Video Answer)
  86. If you have installed SQL Server Instance with Windows Authentication Mode and later you want to change to Mix Mode (SQL Server and Windows Authentication Mode), How would you do that and does it require service restart? (Video Answer)
  87. What is the difference to Restore database "Restore With NoRecovery" and "Restore With StandBy"? (Video Answer)
  88. What are major differences between SQL Server 2005 and SQL Server 2008 version?
  89. What are the major difference between SQL Server 2008/R2 and SQL Server 2012 version?
  90. What is the difference between Full Backup and Copy option in Full Backup? (Video Answer)
  91. How would you find out that How long SQL Server Instance is running? (Video Answer)
  92. What is index fragmentation? (Video Answer)
  93. What is the difference between Physical and Logical Reads? (Video Answer)
  94. Does SQL Server allow duplicate indexes ( indexes with different name but same definition)? If yes, How would you find them and keep only one of them? (Video Answer)
  95. What is the difference between shrinking and truncating database log file? (Video Answer)
  96. What are the best practice to configure TempDB? (Video Answer)
  97. What is Latch? What is the difference between Latch and Lock? (Video Answer)
  98. What is the difference between Schema and Database?
  99. What is the Statistics in SQL Server? (Video Answer)
  100. What are Trace Flags in SQL Server? (Video Answer)
  101. What is SQL Server hash operator?
  102. What is the difference between 64-Bit and 32-Bit releases of SQL Server? (Video Answer)
  103. What are the best practices to shrink a database? 
  104. What is Service Account in SQL Server? (Video Answer)
  105. What is SQL Server Column Encryption? What are the steps to create Column Encryption?
  106. What is the difference between Service Master Key and DataBase Master Key?
  107. To See encrypted data by Column Encryption, What permissions are required by user to access data?
  108. If you need to Backup and Restore Column Encrypted Database, What steps has to be performed?
  109. If we enable Column Level Encryption in SQL Server, Will there any impact on performance?
  110. What is Encryption Hierarchy in SQL Server?
  111. What is Replication in SQL Server? (Video Answer)
  112. Why do we use Replication, Provide couple scenarios? (Video Answer)
  113. What are the types of Replication? (Video Answer)
  114. When do we use snapshot replication? (Video Answer)
  115. What exactly merge replication is? (Video Answer)
  116. Can you schedule replication? And under what circumstances do we schedule replication? (Video Answer)
  117. Your team needs to know if replication breaks/fails, what exactly would you do to accomplish that? ( Video Answer)
  118. What is re-initializing means in replication? (Video Answer)
  119. Under what circumstances will you re-initialize replication? (Video Answer)
  120. How would you add new tables in existing replication? (Video Answer)
  121. Can you explain what would be the replication overhead on production server? (Video Answer)
  122. Transactional replication is set on production source, article schema changed on source but target is not showing that change, what exactly is the issue? (Video Answer)
  123. How will you truncate the replicated table? (Video Answer)
  124. How would delete replicated database? (Video Answer)
  125. Can you bring replicated database offline? (Video Answer)
  126. What is orphan replication? And how would you cleanup replication? (Video Answer)
  127. What is the difference between log shipping and replication? (Video Answer)
  128. Under what scenarios would you use log shipping? (Video Answer)
  129. What is new in SQL server 2012 that's not available in previous versions of SQL servers? (Video Answer)
  130. What is AlwaysOn in sql server 2012? (Video Answer)
  131. What are prerequisites of AlwaysOn? (Video Answer)
  132. What is Availability Group? (Video Answer)
  133. How many databases can be in One Availability Group? (Video Answer)
  134. What is Listener in AG? (Video Answer)
  135. Why do we use Listener in Availability Group? (Video Answer)
  136. Under what circumstances Availability Group fails over? (Video Answer)
  137. What is primary replica in AG? (Video Answer)
  138. What is secondary replica in AG? (Video Answer)
  139. How many secondary replica can be configured? (Video Answer)
  140. What are some advantages of using AlwaysOn feature? (Video Answer)
  141. Can you rename Availability Group? (Video Answer)
  142. You have setup AlwaysOn and send application team to connect with SQL server using Listener, application can't connect with Listener name, what could be the issue?
  143. Can you configure Listener using static port? (Video Answer)
  144. Listener port is set to 1533, can you connect to SQL server using Listener name? (Video Answer)
  145. Availability Group is in resolving state, what does it mean? (Video Answer)
  146. What are SQL server Browsing services? (Video Answer)
  147. Why do we use SQL server Browsing services? (Video Answer)
  148. What is recommended configuration of SQL server browsing services? (Video Answer)
  149. Can you setup replication with AlwaysOn? (Video Answer)
  150. Can you use primary or secondary replica as a distributor? (Video Answer)
  151. What are the issues using primary or secondary replica as your main distributor? (Video Answer)
  152. What is Auto failover of primary replica? (Video Answer)
  153. What does read intention mean in AG? (Video Answer)
  154. What does in-memory Store procedure mean? (Video Answer)
  155. What are tempdb recommended settings in sql server 2012?
  156. SQL server connection is timing out, what would you do to resolve it?
  157. Tempdb log is full, how would you shrink tempdb? (Video Answer)
  158. Can you move tempdb files location without restarting sql server services?
  159. What is SQL Server Clustering? (Video Answer)
  160. Why do we use clustering? (Video Answer)
  161. What are the prerequisites of sql server clustering?
  162. How to add a resource in existing cluster? (Video Answer)
  163. What is failover? 
  164. What is shared storage in SQL server cluster?
  165. How would you find out if SQL server failover happened? (Video Answer)
  166. Applications can't connect to SQL Server after failover, what could be the issue? (Video Answer)
  167. SQL Server Agent resource is not coming online, what could be the issue?
  168. What is the difference between cluster mode and standard mode sql server installation? (Video Answer)
  169. SQL server cluster installation failed, where would you look the cause of failure? (Video Answer)
  170. How many SQL server instances can you add in a cluster? (Video Answer)
  171. Can you find out using SSMS current node for SQL Server Services? (Video Answer)
  172. What is alias in sql server?
  173. How can you find out sql server IP address?
  174. How can you find out SQL server Port? 
  175. What is active-active cluster? (Video Answer)
  176. What is active-passive cluster? (Video Answer)
  177. What is cluster aware? (Video Answer)
  178. Is SSRS cluster aware? (Video Answer)
  179. Is SSIS cluster aware? (Video Answer)
  180. Is it recommended to install SSRS and SSIS during cluster mode SQL Server Installation? (Video Answer)
  181. What is scale out deployment in SSRS? (Video Answer)
  182. Can you configure SSIS as cluster aware?
  183. Some of your external vendors can not connect to SQL server in intranet, what could be the cause? (Video Answer)
  184. You can't connect SQL server from client machine with in intranet, what could be the cause? (Video Answer)
  185. Client can connect to SQL server using IP address but can't connect using SQL server instance name, what could be the cause? (Video Answer)
  186. How to configure SSIS to store packages in MSDB? (Video Answer)
  187. How to configure SSIS to store package in central location?
  188. What is backward compatibility means in SSIS?
  189. How to configure SQL server's memory? (Video Answer)
  190. What are best practices to configure SQL server's memory?
  191. If SQL server's memory is not configured, what can happen to the system? (Video Answer)
  192. System Admin sends you an email that SQL Server services are taking almost all the memory, what steps would you take to resolve it? (Video Answer)
  193. SQL server lost connection with AD, what will happen to SQL Server?  (Video Answer)
  194. SQL services are not starting, where would you look for the cause? (Video Answer)
  195. Can you restore master database? (Video Answer)
  196. You want to know if SQL services restarted or stopped via email, how would you accomplish that? (Video Answer)
  197. How do you keep up with SQL server updates?
  198. Have you ever installed SQL server patches?
  199. What is the latest service pack available in sql server 2012? (Video Answer)
  200. You are restoring the databases by using SQL Server agent job,How would you find that how much percentage of restoration is completed?
  201. What is deployment ? Have you been involved in deployment process?
  202. If you have to deploy 20 SSIS Package to SQL Server Integration Services, How would you do that?
  203. If you have to deploy SSIS Packages to File system, What steps would you follow?
  204. What is Version Control? Which software you have used for version controlling?
  205. What is Team Foundation Server? Why did you use it?
  206. What is Check-In and Check-Out in TFS?
  207. What are the best practices when you need to deploy DDL and DML scripts to Database?
  208. How would you deploy SSRS report to Report Server? If you have to deploy 100 reports to Report Server how would you do that?
  209. Can Data Sources, Data Sets deployed from SSRS Project to Report Server? or You have to create them manually?
  210. How would you deploy SSAS Cube to SQL Server Analysis Services Server?
  211. Can we deploy more than one Cube in SSAS Database?
  212. What are the best practices for SQL Server Change Management?
  213. A users has left the organization, How would you drop his login and user name from all databases from SQL Server?
  214. You need to set Recovery mode =Simple for all the databases on development machine. How would you do that quickly? 
  215. How would you rename logical files of SQL Server Database? 
  216. How would you move data or log file of a Database from one drive to another? 
  217. What are the types of Database files? How would you get the information about them?
  218. What is Contained Database? In which version of SQL Server they were introduced?  (Video Answer)
  219. What is Policy- Based Management in SQL Server? (Video Answer)
  220. How would you identify the isolation level used by the query when dead lock occurs? (Video Answer)
  221. What is FileTable Feature in SQL Server, How does it works? (Video Answer)
  222. You need to import large amount of data to tables in a database, you want to keep the transaction log size small while import the data, How would you do that? (Video Answer)
  223. You need to save passwords in SQL Server Table, What type of encryption would you use?
  224. For what purpose do you use Dynamic management views?
  225. You have 35  Databases on one of the SQL Server instance, You need to create process to take full backup nightly and transaction log backup every hour, how would you do that? (Video Answer)
  226. You need to store and manager unstructured data in SQL Server, Which approach you would use it? (Video Answer)
  227. What is FileStream in SQL Server? (Video Answer)
  228. What are advantages of FileStream?
  229. What is the difference between FileStream and FileTable?
  230. You have a Server that has 24 processors, You need to install multiple SQL Server instances on it.What method you will use to allocate processors to each instance depending upon the requirement? (Video Answer)
  231. Suppose you have a big table with million of rows. You need to provide a solution in which the most recent data should be stored on fastest storage and old data on slow storage, What solution would you provide for this scenario? (Video Answer)
  232. Why do you use Resource Governor? (Video Answer)
  233. Can you use Resource Governor for SSAS,SSIS and SSRS services? (Video Answer)
  234. How would you find out when SQL Server was restarted last time?
  235. You have CDC enabled on a database, If you take a backup of CDC enabled Database and restore to another instance, Will CDC Tables available?
  236. How would you restore CDC enable Database to different instance in a way that CDC tables should be available?
  237. If You have to include new article ( table, view, Stored Procedure or function) in Replication, how would you do that?
  238. What are different type of indexes available in SQL Server? (Video Answer)
  239. What are filtered Index in SQL Server? (Video Answer)
  240. You need to save credit card information in SQL Sever, What would be your suggestion for encryption?
  241. You need to save some image file for your application, What would you suggest? You are using SQL Server 2014.
  242. Can you create two tables with different collation in a database? (Video Answer)
  243. You get the request to create ER( Entity Relationship Diagram) for the tables in a database, Which tool/s would you use to create that? (Video Answer)
  244. What is Full Text Search in SQL Server? Where do you need to use this feature? (Video Answer)
  245. Do you recommend auto growth for Database files? If not why? (Video Answer)
  246. ANSI explain 4 types of isolation, SQL Server has  one more type in SQL Server , What is the name of that? (Video Answer)
  247. What is default Isolation level set for SQL Server Instance? (Video Answer)
  248. Can you have In memory Table without Index or without Primary key? If not then Why? (Video Answer)
  249. What's your experience with Backup Compression, It does compress the backup file, Does it take long to restore compressed backup?
  250. How Database Backup Encryption in SQL Server 2014 is different from TDE ( Transparent Data Encryption).
  251. Is it recommended to Run DBCC CheckDB on Production servers? If not then what solution you suggest? (Video Answer)
  252. What is mount point? Why do we need to use it? (Video Answer)
  253. What are data pages in SQL Server? (Video Answer)
  254. What are Extends in SQL Server? (Video Answer)
  255. How many files do you need for Tempdb? (Video Answer)
  256. Can SQL Server Instance be up without TempDB? (Video Answer)
  257. Is TempDB created based on Model database properties?
  258. How would you find out if CDC ( Change Data Capture ) is enable or disable on a database?
  259. How would you enable CDC on a Database?
  260. How would you disable CDC on a Database?
  261. How would you configure CPU Affinity?
  262. How would you track changes of a particular period by using Change Data Capture function?
  263. How would you disable CDC ( Change Data Capture ) on a table?
  264. How to Configure existing standalone sql server named instance if Machine names changes? (Video Answer)
  265. Replication Error, can't connect to actual server, @@servername returns Null (Video Answer) 
  266. How to give read permission to non administrative accounts to Event Viewer in Windows 2008R2/2012 ? (Video Answer)
  267. How to resolve Availability Group Listener Errors?
Common Interview Questions and Answers for SQL Server DBA
Real Time Interview Questions with Answers for SQL Server DBA
Top 100 Interview Questions for SQL Server DBA
SQL Server Database Administration interview questions with answers
Fresher and Experienced SQL Server DBA Interview Questions
Microsoft interview Questions for DBA
SQL Server DBA interview Q and A
Top 10 SQL Server DBA Interview Questions and Answers
SQL Server - Interview Questions and Answers
Top 5 Interview Questions for Microsoft SQL Server DBA

19 comments:

  1. I like your post very much. It is very much useful for my research. I hope you to share more info about this. Keep posting sql server DBA online training hyderabad

    ReplyDelete
  2. could you please send me the answer for question number 71,72,73

    ReplyDelete
  3. How to resolve Availability Group Listener Errors?

    question 267, link is not working. Can you please fix it

    ReplyDelete
  4. Extremely decent blog and articles. I am really extremely glad to visit your blog. Presently I am discovered which I really need. We are extremely thankful for your blog entry.
    Singing Classes in Delhi | Fashion Designing Course in Delhi | Makeup Course in Delhi | Event Management Course in Delhi | Interior Design Course in Delhi

    ReplyDelete
  5. Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles sql dba training training

    ReplyDelete
  6. Great, thanks for your article! By the way, I have just discovered SQL Certification website. Kindly visit and check it out.

    ReplyDelete
  7. Hii, Thank you so much for this blog, I started to learn SQL Server training online and it is about to complete glad to read this interview questions, and I think it could be more good if you would included the answers also, but I appreciate your efforts for making this blog. Thank you for this informative blog.

    ReplyDelete
  8. I would say, there are three fundamental kinds of SQL Server Certifications.These are for processes, programming arrangements, and different arrangements. I'm intentionally barring related fields, for example, business knowledge or testing and approval to keep this answer clean.

    ReplyDelete
  9. Thanks for your information, it was really very helpful and well explained.
    cheap camera for photography
    best cheap camera for photography

    ReplyDelete
  10. I definitely enjoy this. It is a great website and has a nice article.erp software companies

    ReplyDelete
  11. Preparing for a SQL Server DBA interview? Don't miss out on key questions like BillOnlineCheck. Mastering these can lead to success in landing your dream job. Keep practicing and stay confident!






    ReplyDelete