SQL SERVER DBA Interview Questions ( Part 1)
1. what is your current profile \ day to day Activity in your current org.
a. it must contain info abt your environment,team size, number of servers, versions you are working with, database sizes.
2. What is the backup strategy followed by your organization.
3. How many pools are there in sql server , describe each.
4. describe each background process in sql server and their working.
5. if we fire a select * from tablename query , what happens in background , explain whole processing .
6. what is the difference b\w lazywriter and checkpoint
7. Difference b\w file and filegroup.why use filegroup ?
8. what is page , explain page structure.( Page header, row offset, size of page, row overflow)
9. difference b\w page and extent. uniform extent \mixed extent.
10. different types of pages.
11. how many types of transactions are there.
12. explain structure of Transaction Log file. ( VLFs, log Truncation, Write ahead log, sequential, Rotary nature, Active \Inactive Logs)
13. Difference b\w Windows Authentication and SQL Server authen.
14. how to change Authen mode after installation.
15. How to add \ Remove file to database using script.
16. Different database options ( collation, compatibility level, autoclose, sutoshrink,auto update stats)
17. What is the mean of Compatibility Level, share comp level for each version.
18. how to move database files from one location to another.
19. what is database collation setting of database? what issue may occur if db collation setting is changed.
20. how to attach db if log file is deleted and we have a data file only.
21. Different database modes ( Online, offline, Emergency , Suspect, Read-only, Recovering)
22. if a database get into suspect mode , how will you recover it.
23. how many types of recovery models are there. describe each ( Full, Bulklogges, Simple.)
24. If are installing sql server and it fails abruptly , how will you check the error .
25. describe each system database , it use, what it contains, recovery model, criticality etc.
26. what is Resource database , where is it found ? how to backup this .
27. What will happen if msdb database fails , will sql server work?
28. How to move system database from one location to other.
29. How to recover msdb \master database.
30. difference b\w master and Resource db. location of resource db ( Physical\logical location)
31. difference b\w Login and User.
32. Server Roles \ Database Role
33. how to unlock account using script ( two ways.)
34. different tables \views for users or permissions.
35. login using sqlcmd , in single user mode.
36. how to login if forgot sa password and no other login is there to login into.
37. what is DAC , how to login using DAC.
38. what role must be given for
a. to see only the stored procedure
b. So that one can create jobs \ssis package.
39. How to move Logins from one server to another server.
40. Why is the use of Index , what is the role of cardinality .
41. what is heap.
42. difference b\w Clustered \Non clustered Index.
43. difference b\w Index Scan \ Index Seek.
44. what is page split ? Fill factor ?
45. What is Index fragmentation , how to remove this. (Stored procedure.)
46. Difference b\w Index Rebuld and Reorganize.
47. How many types of Recovery models there ?
48. how many types of backups ? explain each.
49. What happens in backup process.
50. What is tail log backup, copy only backup.
51. difference b\w backup with INIT and FORMAT.
52. What is the use of ENABLE AWE , lock pages in Memory.
53. What happens in RESTORE DATABASE PROCESS ( data copy, redo,undo)
54. what is the use of restore with RECOVERY n WITH NO-RECOVERY n WITH MOVE
55. What are ORPHAN USERS. How to fix them (sp_change_user_login).
56. How to do point time recovery.
57. How to trace which user deleted\dropped rows from table.
58. What is Logshipping.
59. What are Prerequisites for Logshipping.
60. How many Jobs are there in LS? explain each with there location.
61. What happens in STAND BY database in LS.
62. Does a Full backup\Logbackup work in Logshipping
63. what is .TUF, .WRK file in Logshipping.
64. what all Permissions are used in Logshhiping.
65. How to add a file to primary database in LS configuration. will it affect LS.
66. How to move database files of a Primary database\Secondary database in LS.
67. How to change Recovery model of a primary db in LS.
68. How to Failover to secondary DB . ( Explain all steps.)
69. Log shipping is halted ,, after checking you found that many backups are not restore\copied to secondary db.when u tried manually u found one log backup file of the log backup chain is deleted\dropped . what will be you do RESUME Logshipping.
70. What is the difference b\w Service Pack \ Pathes and Fixes.
71. How to identify deadlocks \Blockings?
72. share 5 DBCC commands.
73. Type of Locks \ Isolation level.
74. How to check disk space on server.
75. Difference b\w sql server 2005 and 2008
i. b\w sql 2012 to 2008 r2
76. Share some advance features of 2008R2 than 2008.
77. One query which was running in 10 seconds ,, is taking a lot of time now. how will you troubleshoot this.