DEAD LOCK

What is a DEAD LOCK in SQL Server?

When two or more transactions conflict in such a way that each is waiting for the other before they proceed. For instance, Transaction A might have a lock on Record 1 while trying to write to Record 2, while Transaction B has a lock on Record 2 while trying to write to Record 1. The two transactions will wait for each other forever unless the deadlock is somehow resolved.

This can be understood by following example.

How to Keep track of DEADLOCK in SQL Server ?

How to TRACE DEADLOCK in SQL Server ?

By Default SQL Server doesn’t keep track of DEAD LOCKs, we as a DBA need to request SQL Server to keep track of DEAD LOCKs in SQL Server Error Log.

While placing a request to SQL Server, we need to specify

• What to Capture?
• Where to capture ?
• Define Scope, from where you want to capture ?

In this case, we wanted to capture DEADLOCK Information to SQL Server Log.

To TRACE all DEAD LOCK information to SQL Server Error Log with detailed information, we need to run the following statement. This will work till your SQL Server is running, once your services gets restarted, you need to do this again.

DBCC TRACEON (3605,1204,1222,-1)

/* 
-- where 
-- What to Capture?
--------------------
  -- (1204) = Capture Deadlock Events. 
  -- (1222) = Capture Deadlock Events with detailed Information (works only with SQL 2005 and higher version) 
-- Where to capture ?
-------------------
   -- (3605) = Capture the selected Events in the SQL Server error log. 
-- What is the Scope of Capture 
--------------------------------
    -- (-1) = Switches on the specified trace flags globally, that means do it globally, for all users/sessions
*/

This deadlock tracking will work till your SQL Server is running, once your services gets restarted, you need to do this again. If you wanted to enable this for permanently, then

• Create a SQL Agent job and put this code into and schedule that job to when the Agent starts  or
• You need to enable this trace flags at SQL Server startup parameter, by specifying  -T1222; -T3605;in the startup parameter.
  • SQL Server Configuration Manager >>> SQL Server Services>>> right-click SQL Server ()>>> Properties >>> Advanced TAB >>> Startup Parameters box >>> type the parameters separated by semicolons (;).
  • In SQL Server Denali (2011), this is very easy, check here to understand how to change startup parameters in SQL Server Denali (2011)

Best Practice – SQL Server Error Log

1. Ensure you Error log directory is backed up regularly, with windows OS backup
2. Increase the number of SQL Server Error Logs from the default value of six.
3. Make a schedule job to Recycle the Error on a specified schedule to ensure, you log size in control as large files takes time to read data
4. Check SQL Server Error log on daily basis as all important / critical messages and warnings are logged in SQL Server Error Log

TRUNCATE V/s DELETE

TRUNCATE V/s DELETE

Truncate	Delete
TRUNCATE is a DDL command	DELETE is a DML command
TRUNCATE TABLE always locks the table and page but not each row	DELETE statement is executed using a row lock,                                             each row in the table is locked for deletion
Cannot use Where Condition	We can specify filters in where clause
It Removes all the data	It deletes specified data if where condition exists.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.	Delete activates a trigger because the operation                                are logged individually.
Faster in performance wise, because it is minimally logged in transaction log.	Slower than truncate because, it maintain logs for every record
Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table	keeps object’s statistics and all allocated space. After a                       DELETE statement is executed,the table can still contain empty pages.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction lo	The DELETE statement removes rows one at a time                       and records an entry in the transaction log for each deleted row
If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column	DELETE retain the identity
Restrictions on using Truncate Statement 1. Are referenced by a FOREIGN KEY constraint. 2. Participate in an indexed view. 3. Are published by using transactional replication or merge replication.	Delete works at row level, thus row level constrains apply

Database Mirroring Enhancements in SQL Server 2008 from 2005

Database Mirroring Enhancements in SQL Server 2008 from 2005

Database mirroring is an alternative high-availability solution to failover clustering in SQL Server Enterprise. Database mirroring supports automatic failover, but does not require cluster-capable hardware, and can therefore provide a cost-effective alternative to failover clustering.

This article is not focused on to make you understand Database Mirroring Concept, rather this focuses onEnhancements, which are being done in SQL Server 2008 for Database Mirroring

Database Mirroring Enhancements in SQL Server 2008 from 2005

·         Page-level mirroring:

o    If a page on the principle or mirror server is corrupt, it is automatically replaced with corresponding copy on its partner

·         Automatic Page Repair on Mirror Servers

o    If a page on the principle or mirror server is corrupt, it is automatically replaced with the corresponding copy on its partner

o    Some page types cannot be automatically repaired:

§  File header pages

§  Database boot page

§  Allocation pages

o    I/O errors on the principle server may be fixed during the mirroring session

o    I/O errors on the mirror server require the mirroring session to be suspended

·         Compressed Data flow

o    Data Flow between the principle and mirror server is now compressed to improve performance

·         Manual Failover

o    Manual failover no longer require a database restart

·         Log Performance

o    Write-ahead on the incoming log stream on the mirror server

o    Improved use of log-send buffers

o    Page read-ahead during the undo phase after a failover

Database mirroring enables you to maintain two copies of a database. One copy is the principal server that client computers access. The other copy acts as a standby server. In the case of a failure of the principal server, the client computers can use the failover capability to connect to the standby computer with no loss of data. Mirroring can therefore increase the availability of a database and provide data protection. SQL Server 2008 offers several enhancements to the database-mirroring environment, including the following:

·         Page-level mirroring. This replaces corrupt pages on one server with the same page on the partner server.

·         Compressed data flow. This provides improved performance and reduces the network bandwidth that database mirroring uses.

·         Manual failover. This no longer requires a restart of the database

·         Log performance. This has the following improvements:

o    Write-ahead on the incoming log stream on the mirror server. This writes the incoming log records to disk asynchronously.

o    Improved use of log-send buffers. If the most recently used log cache contains enough free space for the current log records, they are appended to that log cache.

o    Page read-ahead during the undo phase after a failover. The new mirror server sends read-ahead hints to the principal server and the principal server puts those pages in its send buffer. This process improves the speed of the undo phase.

Before SQL Server 2008, data restore could occur at the file level only. A corrupt page may require a failover and then a restore of the file that contains the corrupted data. This procedure is expensive due to the resources and time that are used, and because more data is replaced than was actually corrupted.

SQL Server 2008 provides recovery at the page level. The database-mirroring environment automatically replaces corrupt pages on one server with the same pages from the partner server. The process does not require user intervention and does not interrupt the availability of the server. By using automatic page repair, the principal and mirror computers can recover from data page errors and from errors that prevent reading a data page.

If a mirror server finds a page with errors, it puts the mirroring session into the SUSPENDED state, logs the error, and then requests a copy of the page from the principal server. If the principal server can access the page, it sends a copy to the mirror server, which replaces the page and resumes the mirroring session. Otherwise, the mirroring session remains in a suspended state.

Automatic page repair is only available in SQL Server Enterprise. However, if you have one mirror running on Enterprise and one mirror on Standard, corrupt pages can be repaired on the Enterprise instance, but not on the Standard instance