Author Archives: lohithkschalam

About lohithkschalam

SQL Server and BI DBA. Passionate biker and phtographer

Should we really need to setup monitoring to capture deadlocks in SQL Server ???

I’ll like to begin this blog  with short and usual conversation in DBA space !

———————-start conversation——————–

App team: Hi

DBA Team: Hello

App Team: we observe the following error in our application logs –“Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction”. Can you please provide the cause of the error?

DBA Team : Those errors are deadlock errors and We are sorry to say that deadlock information would be captured in SQL server only when we set up monitoring in place i.e. using Trace flags or enable SQL Sever profiler/ server side trace.

———————-end conversation——————–

I am not flabbergasted at the above conversation as most of us provide the same response in our own grammatical way. However, the above conversation holds good for Pre SQL 2008 era.

From SQL Server 2008 on-wards, we  can make use a new feature called System_Health Session which is based on Extended Events. It will be ‘On’ seamlessly and gets recreated every time SQL server restarts.

System_Health Session monitors various events and deadlock is one of the event which is captured whenever it occurs.

 

Below is small demo on how to gather data on SQL 2008 /SQL 20008 R2 and SQL 2012 on-wards. I used SQL 2008 R2 and SQL 2014 for this demo.

 

a) Using the random script on web simulated deadlock on a demo database called deadlock. The following error is received:

DD1

b) System_health session logs/writes a the data on a target called Ring Buffer (Bing for Ring Buffer to know internals of ring buffer). Using the following query we can read the details captured in ring buffer in XML format

select CAST(xet.target_data as xml) from sys.dm_xe_session_targets xet
join sys.dm_xe_sessions xe
on (xe.address = xet.event_session_address)
where xe.name = ‘system_health

The XML contains the complete deadlock report similar to Trace flag 1205. The analysis of the XML file is outside the scope of this blog-post.

DD2

 

DD3

Microsoft made our lives easier by adding system_health session to GUI. It is very easier to access the system health session data i.e. scroll through Management folder–>Extended Events –> system_health.

The system health session contain two targets -event file and ring buffer. Ring buffer is similar to one discussed above. Event file contain captures a event called xml_deadlock_report which proides the deadlock information in graphical format as shown in below screenshot.

 

DD4

 

Conclusion: Extended events and system_health session is one of the interesting subjects to dwell into hence recommend to ‘Bing’ on this subject to learn more. Indeed it is better to substitute sql profiler with extended events to capture respective details without much performance overhead on the server. I would like to thank all the SQL server stalwarts who inspired me to share my knowledge through this blog.

 

 

 

Advertisements

Leave a comment

Filed under Deadlock, sql server 2008, sql server 2012

“The SQL Server registry keys from a prior installation cannot be modified. To continue, see SQL Server Setup documentation about how to fix registry keys.”

Its been a long time that i wanted to blog about this error for the benefit of the DBA group and finally found time to blog this. 🙂

Error: “The SQL Server registry keys from a prior installation cannot be modified. To continue, see SQL Server Setup documentation about how to fix registry keys.”

error1

Synopsis:We received the above mentioned error while we were patching SQL server 2008 to SP4 on a server running Windows 2003. I tried out solutions provided in various posts on the internet i.e. gave explicit full permissions on registry keys, copied /replaced”FixSqlRegistryKey_x86.exe” (Path:C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Sqlserver2008\x64) from the SQL server setup but none of them helped to resolve the issue.

I referred the ‘Detail.txt’ log file which was generated in “setup bootstrap\log\” folder and found the following error:

error2

Solution: We followed the following procedure to resolve the issue and successfully patched the SQL Server 2008. Thanks to experts who suggested the workaround

a) Double click on the Service pack executable file and note down the temp path to which the service pack contents extracts to. In our case it was extracting to E drive[ Refer below screenshot]

SP_extract

b) The installation failed again with the above mentioned error and don’t close the window so that the above mentioned [refer step a)] temp location would still be accessible. [Refer below screenshot]

rules_validation

c) Go to the temp location [refer step a)] and rename “FixSqlRegistryKey_x86.exe” say something like “FixSqlRegistryKey_x86_old.exe” [Refer below screenshot]

3

d) Copy  “FixSqlRegistryKey_x64.exe” from the above mentioned temp location and paste it in same location. Rename “copy of FixSqlRegistryKey_x64.exe” to “FixSqlRegistryKey_x86.exe” . Click on “re-run” button on SQL server patching window and now the rules check validation would complete successfully [Refer below screenshot]  and continue with patching.

4

Happy Patching!!!

Leave a comment

Filed under SQL Server Patching