SQL DBA Interview Questions & Answers

5 avg. rating (100% score) - 1 votes

SQL DBA Interview Questions & Answers

Are you prepared in attending an interview? Are you worried for job interview preparation? Then do not worry, you are at a right place. We’ve a right answer for your job interview preparation. If you are preparing for SQL DBA job interview and don’t know how to crack interview and what level or difficulty of questions to be asked in job interviews then go through Wisdomjobs SQL DBA interview questions and answers page to crack your job interview. SQL DBA does the administrator job where the system is configured with the needed software and maintain the Relational Databases. Below are the list of frequently asked SQL DBA interview questions and answers which can make you feel comfortable to face the interviews:

SQL DBA Interview Questions

SQL DBA Interview Questions
    1. Question 1. What Is The Difference Between Lock, Block And Deadlock?

      Answer :

      Lock: DB engine locks the rows/page/table to access the data which is worked upon according to the query.

      Block: When one process blocks the resources of another process then blocking happens. Blocking can be identified by using

      SELECT * FROM sys.dm_exec_requests where blocked <> 0
      SELECT * FROM master..sysprocesses where blocked <> 0

      Deadlock: When something happens as follows: Error 1205 is reported by SQL Server for deadlock.

    2. Question 2. What Is The Meaning Of Lock Escalation And Why/how To Stop This?

      Answer :

      understand that whole table would be locked for the processing thenn this is better to use TABLOCK hint and get complete table blocked. This is a nice way to avoid the wastage of sql server DB engine processing for lock escalation. Somewhere you may also need to use TABLOCKX when you want an exclusive lock on the table in the query.

    3. Question 3. How To Truncate The Log In Sql Server 2008?

      Answer :BACKUP LOG TestDB WITH TRUNCATE_ONLY is gone. SQL server doesn’t allow you to truncate the log now otherwise whole purpose of a DB is defeated.

    4. Question 4. What Changes In The Front End Code Is Needed If Mirroring Is Implemented For The High Availability?

      Answer :

      You need to add only FAILOVER PARTNER information in your front end code. “Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;”.

    5. Question 5. Where Does The Copy Job Runs In The Log Shipping Primary Or Secondary?

      Answer :

      Secondary server. This question is basically asked to find out whether you have a hands on work on log shipping or not.

    6. Question 6. What Are The Ways To Find What Code Is Running For Any Spid?

      Answer :

      Well there are many ways to do this.

      1. find the spid which you want to analyze. An spid is assigned as soon as a client connection is established with the SQL server. To find the spid you can run any of the following command:
        1. SP_WHO2 ‘ACTIVE’ — This will give you only active spids.
        2. SELECT * FROM sys.dm_exec_requests
      2. Get the spid from above two queries and use any of the following query to get what is happening behind that spid.
        1. dbcc inputbuffer()
        2. sql2005 and sql2008 – SELECT * FROM sys.dm_exec_sql_text()
        3. sql2005 and sql2008 – SELECT * FROM fn_get_sql()

    7. Question 7. When You Get Following Error? Error 3154: The Backup Set Holds A Backup Of A Database Other Than The Existing Database?

      Answer :

      The error comes when you are trying to restore the DB which already exists. Use WITH REPLACE option to restore the DB with a different name

    8. Question 8. Does Dbcc Checkdb Requires Db To Be In Single_user Mode?

      Answer :

      Yes and No. This is tricky question. If you are using repair option with CHECKDB then you have to have the DB in single user mode. Following is the method to have your DB in a single user mode.

      Use master
      go sp_dboption dbname, single, true

      Following is the error which you get when you run the DBCC CHECKDB with repair option wo having the DB in single user mode. The same is true for DBCC CHECKDB also

    9. Question 9. How To View The Error Log For Any Specific Instance?

      Answer :

      There are many ways but I prefer following method. Take a scenario when you want to find the error log when the DB was put in a single user mode.

      CREATE TABLE #Errorlog (Logdate Datetime, Processinfo
      VARCHAR(20),Text VARCHAR(2000))
      INSERT INTO #Errorlog
      EXEC xp_readerrorlog
      SELECT * FROM #Errorlog
      WHERE Text Like ‘%SINGLE%USER%’

    10. Question 10. According To You What Goes Into Making The Best Database Administrator?

      Answer :

      The primary job of DBAs is to secure the data. They should be able to keep it safe as well as reproduce it efficiently, whenever required. So as per my view, a Database Administrator who can fulfill the requirements of Securing Data and Retrieving Data is the best DBA.

      When I hire a DBA I always ask them questions about backup strategies and efficient restoring methodologies.

    11. Question 11. I Have All The Primary Data Files, Secondary Data Files As Well As Logs. Now, Tell Me Can I Still Restore The Database Without Having A Full Backup?

      Answer :

      You cannot restore the database without having a full database backup. However, if you have the copy of all the data files (.mdf and .ndf) and logs (.ldf) when database was in working condition (or your desired state) it is possible to attach the database using sp_attach_db.

    12. Question 12. As Per Your Opinion What Are The Five Top Responsibilities Of A Dba?

      Answer :

      I rate the following five tasks as the key responsibilities of a DBA.

      1. Securing database from physical and logical integrity damage.
      2. Restoring database from backup as a part of disaster management plan.
      3. Optimizing queries performance by appropriate indexing and optimizing joins, where conditions, select clause etc.
      4. Designing new schema, support legacy schema, and legacy database systems.
      5. Helping developers improve their SQL-related code writing skill.

       

    13. Question 13. One Of The Developers In My Company Moved One Of The Columns From One Table To Some Other Table In The Same Database. How Can I Find The Name Of The New Table Where The Column Has Been Moved?

      Answer :

      This question can be answered by querying system views.

      For SQL Server 2005 run the following code:

      SELECT OBJECT_NAME(OBJECT_ID) TableName
      FROM sys.columns
      WHERE name = 'YourColumnName'

      The previous query will return all the tables that use the column name specified in the WHERE condition. This is a very small but a very handy script.

    14. Question 14. What Is The Difference Between Sql Server 2000 Object Owner And Sql Server 2005 Schema?

      Answer :

      Let us first see the fully qualified query name to access a table for SQL Server 2000 and SQL Server 2005.

      SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]
      SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]
      SQL Server 2008: [DataBaseServer].[DataBaseName].[Schema].[Table]

      In SQL Server 2000, prior to dropping the user who owns database objects, all the objects belonging to that user either need to be dropped or their owner has to be changed. Every time a user is dropped or modified, system admin has to undergo this inconvenient process.

      In SQL Server 2005 and the later versions, instead of accessing a database through database owner, it can be accessed through a schema. Users are assigned to schemas, and by using this schema a user can access database objects. Multiple users can be assigned to a single schema, and they all can automatically receive the same permissions and credentials as the schema to which they are assigned. Because of the same reason in SQL Server 2005 and the later versions – when a user is dropped from database – there is no negative effect on the database itself.

    15. Question 15. What Is Bi? I Have Heard This Term Before But I Have No Idea About It?

      Answer :

      BI stands for Business Intelligence. Microsoft started to promote the acronym BI since the launch of SQL Server 2005. However, it has been in use for a long time. The basic idea of BI is quite similar to Data Warehousing. Business intelligence is a method for storing and presenting accurate and timely key enterprise data to CXO, IT Managers, Business Consultants, and distributed teams of a company, to provide them with up-to-date information to drive intelligent decisions for business success, which ultimately leads to enhanced revenue, reduced risk, decreased cost, and better operational control for business agility and competitiveness. An effective BI empowers end users to use data to understand the cause that led to a particular business result, to decide on the course of action based on past data, and to accurately forecast future results.

    16. Question 16. What Is Your Recommendation For A Query Running Very Slow?

      Answer :

      Well, your question is very difficult to answer without looking at the code, application and physical server. In such situations, there are a few things that must be paid attention to right away.

      Restart Server
      Upgrade Hardware

      Check Indexes on Tables and Create Indexes if necessary Make sure SQL Server has priority over other operating system processes in SQL Server settings.

      Update statistics on the database tables.

    17. Question 17. What Should Be The Fill Factor For Indexes Created On Tables?

      Answer :

      Fill factor specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. Fill factor must be an integer value from 1 to 100. The default is 0. I prefer to keep my servers default fill factor as 90.

    18. Question 18. Which Feature In Sql Server 2008 Has Surprised You? You Can Name Just One.

      Answer :

      Plan Freezing is a new feature I never thought of. I find it very interesting! It is included in SQL Server 2008 CTP5. SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans. This empowers organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

    19. Question 19. How Do You Test Your Database?

      Answer :

      This is a very generic question. I would like to describe my generic database testing method as well as stored procedure testing methods.

      Testing Databases:

      Table Column data type and data value validation.
      Index implementation and performance improvement.
      Constraints and Rules should be validated for data integrity.
      Application field length and type should match the corresponding database field.
      Database objects like stored procedures, triggers, functions should be tested using different kinds of input values and checking the expected output variables.

      Testing Stored Procedures:

      Understand the requirements in terms of Business Logic.
      Check if the code follows all the coding standards.
      Compare the fields’ requirements of application to the fields retrieved by a stored procedure. They must match.
      Repeatedly run the stored procedures several times with different input parameters and then compare the output with the expected results.
      Pass invalid input parameters and see if a stored procedure has good error handling.

    20. Question 20. What Are System Databases Into Sql Server (2005/2008)

      Answer :

      TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource.

    21. Question 21. What Stored By The Tempdb ?

      Answer :Row versions, cursor, temp objects.

    22. Question 22. What Stored By The Model?

      Answer :

      Templates of new database objects, like tables and column.

    23. Question 23. What Stored By The Master?

      Answer :

      Server’s configurations and logins.

    24. Question 24. What Stored By The Msdb?

      Answer :

      Scheduled jobs, Backup/Restore and DTA information.

    25. Question 25. Can We Perform Backup Restore Operation On Tempdb?

      Answer :

      NO

    26. Question 26. What Is Stored In The Mssqlsystemresource Database?

      Answer :

      Definition of sys objects, which logically shows into all database and DMVs.

    27. Question 27. Where The Sql Logs Gets Stored?

      Answer :

      It’s stored into root folder SQL server, LOG folder.

    28. Question 28. What Are The Joins In Sql Server?

      Answer :

      Inner Join, Outer (Left Outer & Right Outer) Joins and Cross join.

    29. Question 29. Describe The Left Outer Join & Right Outer Join.

      Answer :

      Left Outer join Retrieves the all records from LEFT table and matching from the RIGHT table, and null values where is no match. Right Outer Join just opposite.

    30. Question 30. How To Find The Version Of Sql Server?

      Answer :

      Select @@version

    31. Question 31. How To Find The Service Pack Installed?

      Answer :

      Select @@version Or select serverproperty (‘productlevel’)

    32. Question 32. What Are The Difference Between Primary Key And Unique Key?

      Answer :

      An unique key cant not be referenced as foreign key. And it may allow on null.

    33. Question 33. What Is Mean By Clustered Index And Non Clustered Index, Give Syntax Of Creation?

      Answer :

      create clustered index index_name on empmst(card)

    34. Question 34. What Is Scan Table/view And Seek Table/view When Its Occurs?

      Answer :

      A Table/view SCAN occurs when no useful indexes exist. A TABLE SCAN reads all data, row by row, to find the match.

    35. Question 35. What Is Sql Profiler. What Are The Default Templates With It?

      Answer :

      SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.

    36. Question 36. What Are The Dmvs?

      Answer :

      Dynamic Management Views (DMV) return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

    37. Question 37. What Is The Syntax To Execute The Sys.dm_db_missing_index_details?

      Answer :

      Select * from sys.dm_db_missing_index_details

    38. Question 38. What Is Lock Escalation?

      Answer :

      The Query Optimizer initially locks the required Rows for DML operations / Retrieval operations. This also get relevant Pages and Completely table to be 'INTENT' Locked. If more than 50% of rows are specifically locked then this automatically gets the complete PAGE or TABLE to be locked. Lock Escalation mechanism can be controlled by using Locking Hints.

    39. Question 39. How To Truncate The Log In Sql Server 2012?

      Answer :

      BACKUP LOG TestDB WITH TRUNCATE_ONLY is gone. SQL server does not allow you to truncate the log now otherwise whole purpose of a DB is defeated. You have to make sure whether you need log or not. If you do not need log then have the recovery model simple instead of full. If you do not want the log to be accumulated in some particular bulk logging then change the recovery model BULK LOGGED for that duration and take one TLog Backup just before and after this change. I shall discuss this later in my later blog. BACKUP LOG command backs up the t-log and frees the space in the log file.

    40. Question 40. What Is The Purpose Of Sql Profiler In Sql Server?

      Answer :

      SQL profiler is a tool to monitor performance of various stored procedures. It is used to debug the queries and procedures. Based on performance, it identifies the slow executing queries. Capture any problems by capturing the events on production environment so that they can be solved.

    41. Question 41. What Is Copyonly Data Backup? How This Is Useful?

      Answer :

      There are 21 types of Backups in SQL Server. A non-base FULL BACKUP is called COPYONLY Data Backup. This is used to service 'on demand' Backup requests from end users. Using this type of Backups, we can service the 'on demand' backup requests from end users without disturbing the Backup sequence of Jobs / Maintenance Plans or other differential Backups. This gives us easy restore paths as well in case of DR process.

    42. Question 42. What Are Truncate Options Available In Sql Server?

      Answer :

      Use TRUNCATE_ONLY option for SQL Server 2005 systems while performing Backup. This option is not available in SQL Sever 2008 and R2. Instead, we use ON_TRUNCATE option available in the Backup statement.

    43. Question 43. What Are Advantages Of Peer-peer Replication?

      Answer :

      Peer-Peer Replication decreases / nullifies the dependency on Distributor. In this Replication topology each node is Publisher, Distributor and Subscriber. This increases availability of the database system and Failure of any node does not impact the health of Replication process. This topology also offers automatic conflict detection and correction. Hence, recommended in Realtime.

    44. Question 44. What Options We Use To Secure Replication Data?

      Answer :

      Ensure that SQL Browser is running and TCP/IP is enabled. Enforce TDE (Transparent Data Encryption) so that every data bit is encrypted.

    45. Question 45. What Are The Uses Of Standby Mode In Log Shipping?

      Answer :

      If the Restore Jobs is scheduled / delayed for longer intervals this option can be used. As uses needs to be disconnected every time Restore Job runs(to ensure timely shipping of Transaction Logs) its recommended not to use this option for regular, less interval Log Shipping configurations. Using this option gives us advantage of Load Balancing for READONLY connections and Reporting purposes at the cost of timely disconnections to help success of Restore Jobs.

    46. Question 46. When Does Error 3154 Occur?

      Answer :

      The error comes when you are trying to restore the DB which already exists. Use WITH REPLACE option to restore the DB with a different database name.

    47. Question 47. What Are Recommended Options To Be Used While Using Db Mirroring?

      Answer :

      Database Mirroring is to be configured with TCP Protocol and ensure that data over each endpoint is encrypted. Better to make use of TDE for more security.

    48. Question 48. Where Can You Find The Error Log Information?

      Answer :

      We can make use of SQL Profiler , SQL Server Log or use xp_readerrorlog extended Stored Procedure to retrieve the error log information.

    49. Question 49. What Is The Status Of Services On Passive Node For Failover Cluster In Sql Server?

      Answer :

      SQL services will be in manual and stopped. Cluster service will be in automatic and started mode on both the nodes.

    50. Question 50. Can You Move The Resources After Pausing The Node?

      Answer :

      Yes resources can be moved after pausing the node. But we can't move them back till the node is paused.

    51. Question 51. What Happens If We Start The Full Text Service On Passive Node.

      Answer :

      This can be started on both the nodes as this doesn't have any dependency on SQL service or any resource which is possessed by active node.

    52. Question 52. What Is Data Compression?

      Answer :

      In SQL SERVER 2008 R2, Data Compression comes in two types viz., Row Compression where It minimizes the metadata (column information, length, offsets, etc.) associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar. Page compression uses the Row compression technique internally and also applies prefix level compression.For every column in a page, duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which reside after page headers. A reference number is assigned to these prefixes and that reference number is replaced wherever those prefixes are being used.

    53. Question 53. What Are The Basic Functions For Master, Msdb, Model, Tempdb And Resource System Databases?

      Answer :

      1. The master database holds information for all databases located on the SQL Server instance. As SQL Server cannot start without a functioning master database, we must administer this database with care and monitor Startup Parameters in Configuration Manager.
      2. The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
      3. The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
      4. The model is essentially a template database used in the creation of any new user database created in the instance.
      5. The resource Database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

    54. Question 54. What Is Service Broker?

      Answer :

      Service Broker is a message-queuing technology in SQL Server that allows developers to integrate SQL Server fully into distributed applications. Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable.

    55. Question 55. Where Sql Server User Names And Passwords Are Stored In Sql Server?

      Answer :

      They get stored in System Catalog Views sys.server_principals and sys.sql_logins.

    56. Question 56. What Is Policy Based Management (pbm)?

      Answer :

      Policy Based Management in SQL SERVER 2012 Administration allows you to define and enforce policies for configuring and managing SQL Server across the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes.

    57. Question 57. What Is Replication With Database Mirroring?

      Answer :

      Database mirroring can be used with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients which are known as the principal database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.

    58. Question 58. What Are Sparse Columns In Sql Server?

      Answer :

      A sparse column is another tool used to reduce the amount of physical storage used in a database. They are the ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non null values.

    59. Question 59. What Are The Steps To Take To Improve Performance Of A Poor Performing Query?

      Answer :

      Steps to take to improve performance of queries:

      • Use indexes efficiently
      • Create all primary and foreign keys and relationships among tables.
      • Avoid using cursors
      • Avoid using Select*, rather mention the needed columns and narrow the resultset as needed.
      • Denormalize
      • Use partitioned views
      • Use temporary tables and table variables
      • Reduce joins and heavy clauses like GROUP BY if not needed
      • Implement queries as stored procedures.
      • Have a WHERE Clause in all SELECT queries.
      • Use data types wisely
      • Instead of NULLS use string values such as N/A

    60. Question 60. What Is A Deadlock And What Is A Live Lock? How Will You Go About Resolving Deadlocks?

      Answer :

      • Deadlock occurs when two user processes/transactions have locks on 2 separate objects and each process is trying to acquire a lock on the object that has been acquired by the other process. In such a scenario each process is waiting for the other process to release the lock to acquire a lock on the object itself. When a request for exclusive lock is denied again and again because a series of overlapping shared locks are interfering with each other and to adapt from each other they keep on changing the status, it is known as live lock.
      • One can resolve deadlocks by using TRY CATCH blocks. If the code inside a TRY statement fails, the CATCH automatically catches the control of the flow letting the transaction rollback and resume execution.

    61. Question 61. What Is Blocking And How Would You Troubleshoot It?

      Answer :

      Blocking occurs when a process has acquired lock on a set of rows, and another process is trying to acquire a lock on the same set of rows. In such a case, the other process has to wait until the first process finishes its job and releases the lock on the above said rows.

      • Use sp_lock procedure to see type of locks acquired by various sessions on the server to find the cause of blocking.
      • Problem is hinted by the WAIT status is a lot of rows that are returned as an output of sp_lock stored procedure execution.
      • Use sp_who and sp_who2 to return more columns to get more information around the blocking.
      • Use DBCC INPUTBUFFER (spid).This will show the last 128 characters of the last T-SQL statement executed from connection referred through spid. This way one can identify the stored procedure or application module that caused blocking.
      • To resolve blocking, you can disconnect the connection causing the blocking using KILL command. If this does not solve the problem permanently, then rewrite the stored procedure/module causing the block more efficiently.

    62. Question 62. Explain The Different Types Of Backups Available In Sql Server?

      Answer :

      Types of backups available in SQL Server:

      • Complete: This creates a complete stand alone image of the database. This backup is self dependent and can be restored to either the same or a new database on same or other server.
      • Differential: This backs up only the modified contents since the last backup. They do not provide much flexibility.
      • Transaction log: This backs up all transaction logs since the previous transaction log backup or the complete transaction log backup if there has not been one in past.
      • Files and Filegroups backup: This option is suitable when time constraints are high and one cannot afford to perform a complete database backup. It also needs transaction logs backup to take place to make it worth choosing this option. After restoring file backup, apply transaction logs to roll the file contents forward to make it consistent with the database..

    63. Question 63. What Is Database Isolation In Sql Server?

      Answer :

      Database isolation comes into play when we need to isolate the database and protect it from other things on the network. This protection is achieved using locks. The type of lock and the level of isolation level needed is referred as isolation level in SQL Server.

      Types of isolation levels:

      READ COMMITTED: Shared locks are held while any data is being read.

      READ UNCOMMITTED: Specifies isolation level 0 locking. There are thus no shared locks or exclusive locks. Lease restrictive of all the isolation levels.

      REPEATABLE READ: Locks are applied on all data being used by a query. However, new phantom rows can be inserted into the data set by another user and are included in later reads within the current transaction.

      SERIALIZABLE: Issues a range lock on data set, preventing other users to update or insert data into dataset until the transaction is complete.

Popular Interview Questions

All Interview Questions

Sql Dba Practice Test

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Tutorial