Frequently Asked Questions in SQL Server DBA (SQL DBA)

---

1. What version and edition of SQL Servers that you are using in your current organization?

2. How many SQL Servers you are handling in your current organization?

3. Whom you will reach out to build a server in your current organization?

4. How did you get the SQL server installation files, service pack files to install the SQL server?

5. What are the minimum requirements to install SQL server?

6. How did you verify whether your system met minimum requirements to install SQL server or not?

7. What action you take if your system not met minimum requirements to install SQL server?

8. What do you do if the SQL server installation file corrupted or missing some DLL's?

9. Which approach you will follow to install the SQL server?

10. There is a need to install SQL server in 100+ servers. What will be the best way to make it faster?

11. Did you installed SQL server using configuration file? If so, how did you install that using configuration file and what are all the options you set in configuration file?

12. What is the service pack?

13. How frequently you will get the service packs to install on SQL servers?

14. What is the best approach you follow to install service pack in production environment? 

16. What are all the versions and editions which you have been worked so far?

17. What is the difference between Enterprise edition and Standard edition, any few differences?

18. How can you recommend your customer to go with Enterprise edition other than standard edition?

19. What is the latest version of SQL Server?

20. What are the new features in the latest version of SQL server?

21. What are all the features which you have worked on so far in SQL server?

22. How can you restart the database services?

24. What is the approach that you followed to restart a service in your current organization?

25. When did you restarted a database service and why?

26. Have you seen any issues while restarting the database services?

27. What will be the possible reason if a database service is not able to start even if we set its 

28. How to change the service account?

29. What is the usage of start-up parameters? Did you set any start-up parameters? If so, why did you setup start-up parameters and what are those parameters?

30. Can you tell few start-up parameters?

31. Partial backups?

32. What is the use of service account?

33. What is the minimum configuration of a production system which you have worked in your experience?

1. What is the meaning of a database? 

2. What are the system databases?

3. And: Can we drop the system databases? If so, how can we drop them? If not, why we can't drop them?

4. What is the purpose of these system databases?

5. What is the use of the objects of sys schema?

6. What is the resource database?

7. What do you do if a system database is corrupted

8. Have you rebuild the system database anytime in your experience?

9. If you have rebuilt a system database, what is the process that you have followed to rebuild it?

10. What is the use of MODEL database?

11. What is the use of TEMPDB database?

12. Can you tell more about the TEMPDB database?

13. What is the use of MSDB? What information will be stored in MSDB database?

14. What are most common objects (tables) which you have used form MSDB database?

15. Can we add more database files to these system databases?

16. Can we set these system databases in single user mode or read only mode?

17. What will happen to the TEMPDB database if I restart the sql server?

18. Can we store the user's data in the system databases?

19. What is the meaning of USER DEFINED database?

20. How many maximum databases that we can create in one sql server instance?

21. How can you create and drop a database?

22. How a new database will be created? How can you make sure a new database should be created with some objects?

23. What are all the options you should check while creating the database?

24. What are the objects that you can create in a database?

25. How do I know that what is the default path of database files?

26. How do I change the default path of database data and log files?

27. What is the meaning of tables and where these will store their data/records?

28. What are the file groups in a database?

29. What are all the different types of file groups?

30. What is the meaning on default/primary file group and secondary file group?

31. What are the different types of files in a database?

32. How a database file cab be assigned to a FILE GROUP?

33. How many maximum no.of file groups we can create in a database?

34. How can you make sure to store a table data into a specific file group?

35. Where actually the data in tables will be stored?

36. How may no.of maximum files that we can create in a database?

37. What is the page and extent in a database?

38. What are the different types of PAGES?

39. Can you tell me the basic details about a page?

40. What is the offset and header in a page?

41. What is the size of a page?

42. What will happen if I am trying to store data/record which is more than the size of a page?

43. Can you tell me how the data/records will be stored in a page?

44. What are the different types of extents?

45. What is the exact meaning of MIXED extent and UNIFORM extent?

46. How a UNIFOMR extent will be formed?

47. What is the minimum size of a table?

48. Can a page be allocated to two tables?

49. How can you find out that how many pages are allocated to a table?

50. How can you find out that how many pages are allocated to a table from UNIFORM extent and MIXED extent?

51. Can we open a data page and see what it contains?

52. What is the transaction log file in a database?

53. How it will record the transactions?

54. What information it will record as part of transactions?

55. What is a transaction and how it will be logging into transaction log file?

56. What is the meaning of virtual log file?

57. What is the meaning of transaction log records?

58. What is the meaning of LSN?

59. How the transactions logs are truncated/deleted/cleared from the transaction log file?

60. What transaction logs are truncated/deleted/cleared from the transaction log file?

61. When the transaction logs are truncated/deleted/cleared from the transaction log file?

1. What is a transaction?

2. How the transaction will execute against a database?

3. What is the buffer cache?

4. What the buffer cache contains?

5. What is the CHECKPOINT and what is the advantage of it?

6. What is the WRITE A LOG Header?

7. What is the meaning of COMMIT and Rollback a transaction?

8. What does it mean by committed and uncommitted transactions?

9. What the different types of recovery models?

10. What is the difference between FULL and BULK LOGGED recovery model?

11. What is the SIMPLE recovery model?

12. What is the use of keeping the database in FULL recovery model?

13. What is the use of keeping the database in BULK LOGGED recovery model?

14. What is the use of keeping the database in SIMPLE recovery model?

15. Can I take t-log backup if I keep the database in FULL?

16. Can I take t-log backup if I keep the database in BULK LOGGED?

17. Can I take t-log backup if I keep the database in SIMPLE If so, how you will take? If not so, why?

18. What is the difference between FULL and SIMPLE?

19. What is the difference between BULK LOGGED and SIMPLE?

20. What is the difference between FULL and BULK LOGGED?

21. What is the backup strategy/plan in your current organization?

22. What is a FULL backup contains?

23. What will be happening internally when you are taking FULL backup?

24. Does the FULL backup contain the changes from current active transactions?

25. How frequently you are taking FULL backups from the databases in your current organization?

26. How much time it will take to generate FULL backup of a database whose size is 2 TB?

27. What are the common issues or errors while taking the FULL backups?

28. Where you are storing the FULL backups in your current organization?

29. What is the differential backup?

30. Can I take the Differential backup without taking FULL backup? If so, how you can take Diff backup? If not so, why you can't take Diff backup?

31. What a Differential backup will contain? Will that contain all the changes?

32. Can we take the Differential backup if the database recovery model is BULK LOGGED?

33. How the differential backup knows about the latest changes which are done after FULL backup of DB?

34. What exactly happening while generating the differential backup?

35. I have taken the FULL backup from the database One year back; can I take the differential backup now?

36. What is the transaction log backup?

37. How the transaction log backup generate? What is exactly happening while taking the t-log backup?

38. What a transaction log backup contains?

39. How frequently you are generating the t-log backups in your organization?

40. Can we take the t-log backup if the database recovery model set to SIMPLE?

41. Can we take the t-log backup if the database recovery model set to BULK LOGGED?

42. Can we take the t-log backup if the database recovery model set to FULL?

43. Any difference between the t-log backup's which generated under BULK LOGGED and FULL recovery model?

44. What is the difference between INIT and NOINIT options in BACKUP statement?

45. What is the FORMAT option in BACKUP statement?

46. What is the media set and backup set?

47. Can we keep multiple backup sets in one media set?

48. How did you verify the backup set to see who took this backup, from which server and database, when they took this backup, what type of backup?

48. What is the POINT IN TIME restore?

49. Why do we need to do POINT IN TIME restore?

50. Can I restore FULL and Diff backup with POINT IN TIME option?

51. What is the TAIL LOG backup?

52. When you will take TAIL LOG backup? What are the advantages of TAIL LOG backup?

53. What is the COPY ONLY backup?

54. Can I take COPY ONLY backup for differential backup? If so, how? If not so, why?

55. What is the use of COPY ONLY backup?

56. Have you ever taken COPY ONLY backup? If so, why did you take this backup?

57. What is the difference between COPY ONLY backup and TAIL LOG backup?

58. What are the MIRROR backups? In which case, you can recommend taking the MIRROR backups?

59. What are the STRIPPED backups? In which case, you recommend taking the STRIPPED backups?

60. Did you compress the backup file while taking a backup of the database?

61. What are the different levels of compression for backup files?

62. How did you set the compression for backup files?

63. What are the recovery or restore phases? Can go through each and every phase in detail?

64. How the recovery phases are affected while restoring the FULL backups / Differential Backups and T-Log backups?

65. What are the different types of RESTORE states?

66. What does it mean by RECOVERY and NORECOVERY restore states?

67. When did you choose RECOVERY and NORECOVERY option while restore the database?

68. What will happen if I choose RESTORE STANDBY option while restoring the database backup file?

69. My database is currently showing as (...restoring) state. How did you bring it to ONLINE (READ WRITE)?

70. I have a FULL backup which is generated today morning at 8 AM. I have generated a Differential backup at 10 AM. After that I have generated a T-LOG backup at 12 PM. Can I restore the Diff and T-Log backup without restoring the FULL backup?

71. My database is in FULL recovery model and I have taken the FULL backup now. After that I have changed the recovery model to SIMPLE. Can I take the Differential backup now? If so, how to take diff backup? If not so, why?

72. I have FULL backup as of on Today morning 8 AM, after that I have taken Diff backup at 10 AM and I am taking T-LOG backups for every 30 mins. My customer is asking me to restore the database as of on 2 PM on today. How did you restore and what are all the backup files you choose?

73. I have FULL backup as of on Today morning 8 AM, after that I have taken Diff backup at 10 AM and I am taking T-LOG backups for every 30 mins. My customer is asking me to restore the database as of on 03:15 PM on today. How did you restore and what are all the backup files you choose?

74. We have scheduled the FULL backup to run on every Friday at 08 PM and Differential backup on every day at 10 PM and T-LOG backups for every 30 mins on every day.

My database has been corrupted on Thursday at 02:40 PM. I want to restore the database without losing any transactions. What approach you will follow and what backup files you will restore?

75. I have taken the T-LOG backups from today morning 8 AM to until today evening 10 PM. By mistake I have deleted a backup file that was generated at 10 AM. Can I restore the all-other transaction log backups without restore this missing backup file?

76. I have the FULL, DIFF and T-LOG backups as of on today 4 PM. Now the time is 7 PM and I noticed that the database got corrupted at around 06:45 PM. And there were lots of transactions are done after 4 PM and before 6:45 PM. My customer is asking me to restore the database as of 06:30 PM. What are all the steps you will follow to restore the database as of on 06:30 PM?

77. One of the database data file got corrupted, and I have decided to restore the database. What are all the steps you will follow to restore the database?

78. Are you using any backup tools to take the backups? What are they?

1. How do you find out the size of the database?

2. What it the free space in database?

3. How did you find out the free space in a database?

4. How did you find out the occupied space in each database file?

5. What is the meaning of initial database file size?

6. What is the meaning of AUTO GROW?

7. What are AUTO GROW settings that you have configured in your current company?

8. How did you configure AUTO GROW at database files?

10. What is the difference between AUTO GROW by percent and AUTO GROW by a value?

11. What is the maximum limit of size of a database data file?

12. What is the maximum size of MDF and NDF files in your current organization?

13. What is the maximum limit of size of a database transaction log file?

14. Can we store the MDF file and NDF files on different paths in different drives?

15. Can we store the database files on NETWORK/SHARED paths?

16. What is the main reason to do not store the database files on NETWORK/SHARED paths?

17. How the AUTOGROW option works on database files size?

18. When the AUTOGROW will occur on the database files?

19. How did you add a new data/transaction log file to a data?

20. How many maximum no.of files that we can add on a database?

21. How did you find out the current size of MDF, NDF, and T-LOG files in a database?

22. Why the transaction log file keeps on growing its size?

23. What do you do to control growing the size of t-log file in a database?

24. What are all the options that you follow to do not grow the size of t-log file on a database?

25. What do you do if the size of the database files reaches its maximum space in the disk?

26. What do you do, if the disk is running out of space under which you have kept the database files?

27. What is the purpose of SHRINKING the database files?

28. Does SHRINKING database files slow the server?

29. When you will shrink the database files?

30. Can we SHRINK T-LOG files?

31. How do you SHRINK the database files?

32. How frequently you will SHRINK the database files?

33. Have you shrunk database files in your experience?

34. Are you going to do any activities before SHRINKING database files?

35. What is the meaning of UNALLOCATED space?

36. What is the meaning of RESERVED space?

37. What is the meaning of UNUSED space?

38. What are all the steps you follow to get more free space in the database data files?

39. What do you do to get the more free space in the database transaction log files?

40. If we drop a table/delete the data from table, does it cause to decrease the size of database?

41. What would happen to the database files size if we dropped the tables/delete the data from tables?

42. How did you know that which data file got free space if I drop a table in the database?

43. How did you know which tables that we can drop from the database?

44. What is the process that you follow to drop the tables from the database?

45. How did you compress a table?

46. What will happen if we compress a table?

47. Did you compressed the tables in your experience?

48. What kind of tables, you suggest compressing?

49. Can we compress a table again and again (more than one time)?

50. What will happen if we compress a table 2 times or 3 times...etc?

51. What are all the types of compressions?

52. How did you know whether the table(s) are already compressed or not?

53. What is the difference between PAGE compression and ROW compression?

54. Can we move the database files from one file group to another file group?

55. Can we move the database files from one path to another path?

56. Why do we need to move the database files from one path to another path?

57. What are steps you follow to move the database files from one drive to another drive?

58. Can we move the MDF, NDF and LDF file from current path to another path?

59. If I move the database files from one drive to another drive, does it change the size of the database?

60. Can we access the database while moving the database files?

61. What is the meaning of DETACH & ATTACH process?

62. What will happen if we DETACH a database?

63. What is the use of adding the secondary data files?

64. Scenario:

The D: drive is running out of space, no space. But there are the database files from two databases in this drive. Customers are complaining that they are unable to insert the data into these two databases. What are the steps you will follow in this case?

1. What is the authentication process in sql server?
2. What does it mean by autherization?
3. What is the login in SQL Server?
4. What is the meaning of windows account or login?
5. What is the meaning of windows authentication mode and SQL Server authentication mode?
6. What are all the default accounts in SQL Server?
7. Can we delete/drop these default accounts?
8. How can you setup the SQL Server instance to allow SQL Server authenticated logins to connect to SQL Server?
9. What we have to do before creating the SQL Server authenticated logins?
10. What will be happening when you are trying to create a windows authenticated account in SQL Server?
11. Can you tell me something about Active Directory Groups?
12. Can I add all my SQL Server authenticated logins to the Active Directory Groups?
13. What is the SID and its real usage?
14. I have created a windows authenticated login in one sql server and also created the same windows authenticated login in one of another server. Can you tell me whether these two logins will get two same SID or different SID?
15. I have created a SQL Authenticated Login in two sql servers with same name. Can you tell me whether those two SQL Authenticated logins will get the same SID or different SID?
16. How can you identify what are the windows authenticated logins, SQL Authenticated logins and AD Groups in SQL Server?
17. How frequently you will suggest to chagne the passwords of SQL Authenticated users in your organization?
18. How can you get back the forgot password for a SQL Authenticated login?
19. How can you list down all the available logins?
20. Can you verify when was the SQL Authenticated login has changed its password?
21. Can we delete/drop the SA login?
22. Can we use SQL Authenticated logins to connect to SQL Servers if SA login has disabled?
23. How can you disable a login?
24. How can you make sure to do not allow a login to connect to SQL Server?
25. What do you do if a user requested to UNLOCK a login?
26. Why a login will be locked out?
27. How did you findout the date and time when a user has changed their password?
28. What do you do if a user requested to grant access to run BULK operations on the SQL Server databases?
29. What do you do if a user requested to grant access to run SQL Profiler on the SQL Server instances?
30. What are the permissions you grant if a user requested to create temp tables in the TEMPDB?
31. Can you list some operations or tasks which can be done only by SYSADMIN?
32. What are all the server roles? Can you describe about each one?
33. Can we add our own server roles?
34. What do you do if a user is requesting to grant SYSADMIN access on one of the SQL Server?
35. What do you do if a user is requesting you to grant access to table BACKUP of all the databases?
36. What does it mean by service account?
37. How did you change the service account?
38. What are all the complicaitons if we change the service account?
39. What are the default service accounts?
40. Why should I change the service accounts from their default accounts?
41. What are network protocols?
42. What is the use of TCP\IP, Named Pipes and Shared Memory protocols?
43. Can we connect to the SQL Server if we disable TCP\IP protocol?
44. What is the port number?
45. Did you changed the port number in your experience?
46. Why should we change the port number?
47. What is the dynamic port and did you recommend to setup dynamic port number?
48. What are advantages and disadvantages if we setup dynamic port number?
49. How did you change the PORT number?
50. Can you come across in your experience to setup port numbers for any sql server instance?
51. Can we set negative number as PORT number?
52. What do you do if there is another application using the same PORT number which is assigned to SQL Server instance?
53. How did you find the current PORT number of SQL Server?
54. How did you connect to the SQL Server instance from SSMS, if the current PORT number is 1234?
55. What do you do when a user saying that they are unable to access a database?
56. What will be the reasons if a user has exeucted one stored procedure yesterday, but he can't able to execute the same stored procedure today? No one has revoked his permissions.
57. I have created a user in a database by mapping to login 'X'. Can I map the same user to other login 'Y'?
58. Can we create two users in a database and map those two users to a LOGIN 'X'?
59. Can you disable a user id in a database?
60. What are the orphand users?
61. What does it mean by fixing the orphand users?
62. What is the best way to handle the orphand users?
63. Why and how did we got the orpand users in a database? Though we have not created orphand users
64. What is the DBO user in a database?
65. How can you grant READ access on whole database to a user?
66. How can you grant access to a user to send out database emails?
67. What are the permissions you grant if a user requested to create jobs and schedule them?
68. How can you grant only INSERT, UPDATE and DELETE access on a table, not entire database?
69. What are the fixed database roles?
70. When we can suggest to create user defined database roles?
71. What will happend if I grant DENY DATA READER and DB DATA READER access to a user? Can that user access any object?
72. What is role of PUBLIC?
73. How can you suggest to secure the sensitive data in database tables?
74. What are all the ways to encrypt the data in database tables?
75. What is the DATABASE MASTER KEY and how it is protected?
76. What is the SERVICE MASTER KEY and how it is protected?
77. How do you verify whether DATABASE MASTER KEY and/or SERVICE MASTER KEY is enabled on database?
78. How did you enable DATABASE MASTER KEY on a database?
79. What is the CERTIFICATE in SQL Server database?
80. Did you encrypted the data by using CERTIFICATE?
81. How can you protect the database files and backup files?
82. What is the primary requirement to store the encrypted data into database?
83. What are the functions you have used to encrypt and decrypt the data by using either PASSCODE, CERTIFICATE and ASSYMMETRIC KEYS?
84. Can we encrypt the SALARY data which are in DECIMAL data type?
85. Can we encrypt the AADHAR NUMBER which is in CHAR data type?
86. What is the TDE? Did you configured the TDE?
87. What is the process to implement TDE in your organization?
88. Have you done the database refresh?
89. What the stpes you follow before you start database refresh?
90. What is the reason to perform database refresh?
91. What are all the issues which you have faced while doing database refresh?
92. What is the AUDIT and SERVER AUDIT SPECIFICATION?
93. Can we suggest to turn on SQL Server Audit?
94. Why we need auditing?
95. What are the events we can trace using SERVER AUDIT SPECIFICATION?
96. How did you track all the TRANSACTION statements which are running against the database?
97. How did you find out who dropped the objects?
98. How did you READ the saved audit events from a flat file and application log and security log?
99. What are all the best practices you followed so far in your experience?

Scenario1:

There 15 members are joined in SQL Development team, and they are asking you to grant access on around 50 SQL Server instances. What is the simplest way you follow and best practice?

Scenario2:

How can you find out what access that a login contains in one SQL Server?

Scenario3:

What do you do if your manager is asking you to revoke access temporarily for specific users for sometime?

Scenario4:

How can you make sure to change the password for SQL Authenticated login when it logging at first time?

Scenario5:

What are the reasons and What do you do when a user reported by saying that he was connected to SQL Server yesterday, but he is unable to connect to the SQL Server today?

Scenario6:

Can you list down all the logins who are having SYSADMIN access in the SQL Server?

I would like display the logins from an AD if that AD is having SYSADMIN access.

Scenario7;

One of the SQL Authenticated login has forgot the password and entered the wrong password for few times. So finally the account has been locked out. How can you unlock it and what are complications to unlock a login?

Scenario8:

What do you do when a user is asking you to grant READ access on all the databases and make sure that user should automatically get READ access on the newly creating database?

Scenario9:

There is a user in my database. I have granted DB Owner access at database level and granted DENY access on a table. Can you tell whether that user can access that table?

Scenario10:

There is a user in my database. I have granted SYSADMIN access to a login and granted DENY DATA READER access on a database. Can you tell whether that user can access that database or not?