Question 311 – What is Dead Lock?

• A deadlock is a situation where in two transactions wait for each other to give up their respective locks.
• When this happens, the SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue.
• The aborted transaction is rolled back and an error message is sent to the user of the aborted process.
• SQL Server detects deadlocks and terminates one user’s process.
• Deadlocking can occur with locks, parallelism, threads, and application events.
• Most frequent source of deadlocking is resource locking where the resources are table or index objects.

Question 312 – What is an example of Deadlock?

Transaction A attempts to update table 1 and subsequently read/update data from table 2, whereas transaction B attempts to update table 2 and subsequently read/update data from table 1. In such situations, transaction A holds locks that transaction B needs to complete its task and vice versa; neither transaction can complete until the other transaction releases locks.

Question 313 – How to Resolve Deadlock?

• In SQL Server 2000, the Lock Monitor thread detects the deadlock.
• It uses a periodic detection system, inspecting processes about every 5 seconds to determine if there are any deadlock cycles.
• When it finds one, it automatically chooses one thread as the deadlock victim.
• It then rolls back the victim thread’s transaction, cancels its query, and returns error 1205 to its client.
• The Lock Monitor generally chooses the least expensive transaction to roll back.
• You can override this somewhat using SET DEADLOCK_PRIORITY to LOW for a session.
• Whenever both threads have the same DEADLOCK_PRIORITY setting, the Lock Monitor will have to choose one of them as the victim.

Question 314 – What is Live Lock?

• A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.
• SQL Server detects the situation after four denials and refuses further shared locks.
• A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
• This is different than deadlock as in deadlock both the processes wait on each other.

Question 315 – What is an example of Live Lock?

A human example of live lock would be two people who meet face-to-face in a corridor and each moves aside to let the other pass, but they end up moving from side to side without making any progress because they always move the same way at the same time and never cross each other. This is good example of live lock.

Question 316 – What are the options available to Move Database between servers?

There are lots of options available; you have to choose your option depending upon your requirements. Some of the options you have are:

• BACKUP/RESTORE,
• Detaching and attaching databases,
• Replication,
• DTS,
• BCP,
• Log shipping,
• INSERT…SELECT,
• SELECT…INTO,
• Creating INSERT scripts to generate data.

Question 317 – What is Replication?

• SQL replication allows not only for copying data between databases, but also copying any database objects as well. Essentially replication performs synchronization between databases. By utilizing SQL replication, you can distribute data to as many remote network locations you need, and you can do that over different types of networks including LAN, WAN, and Internet to name a few.
• Database replication can be done in at least three different ways:
• Snapshot replication: Data on one server is simply copied to another server, or to another database on the same server.
• Merging replication: Data from two or more databases is combined into a single database.
• Transactional replication: Users receive full initial copies of the database and then receive periodic updates as data changes.

Question 318 – What is Union?

• UNION only selects distinct values.
• When using the UNION command all selected columns need to be of the same data type
• A UNION statement effectively does a SELECT DISTINCT on the results set.

Question 319 – What is Union ALL?

• UNION ALL selects all values.
• Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.
• If you know that all the records returned are unique, use UNION ALL instead, it gives faster results.

Question 320 – List some of the DBCC (Database Console Commands for SQL Server) Commands?

• DBCC CACHESTATS displays information about the objects currently in the buffer cache, such as hit rates, compiled objects and plans, etc.
  DBCC DROPCLEANBUFFERS: Use this command to remove all the data from SQL Server’s data cache (buffer) between performance tests to ensure fair testing. Keep in mind that this command only removes clean buffers, not dirty buffers.
• DBCC ERRORLOG: If you rarely restart the mssqlserver service, you may find that your server log gets very large and takes a long time to load and view.

Disclaimer – F5debug Interview Questions & Answers Series:

You may recopy extracts from these pages (“the material”) to individual third party websites or to any intranet websites, but only if:
You acknowledge www.f5debug.net as the source of the material. Such acknowledgment should include reference to www.f5debug.net in the copy of the material and should also include “© Karthikeyan Anbarasan, www.f5debug.net “. You inform the third party that these conditions apply to him/her and that he/she must comply with them.