Enabling SQL trace for Deadlock and simulating deadlock

To Enable deadlock traces in global level in situations where we cannot predict when the deadlock will occur i have used the following methods after googling few sites.

We say “Trace flags” which denotes a logging key for a type of trace logging. Ex. Deadlock traces can be enabled by enabling flags “1204 and 1222″

To check the status of a flag, we use command as below,

GO

DBCC TRACESTATUS (1204);

GO

DBCC TRACESTATUS(1222);
GO

By running this, we will get an output similar to below,

TRACESTATUS

Here, If Global has value “1” it means trace is enabled in global level (Not only for the current session). If Session has value “1” it means trace is enabled for the current session. “0” implies that the trace is not enabled for Session/Global level respectively.

To Turn On Tracing:

For enabling trace at the Session level we can use the following command,

DBCC TRACEON (1204)

GO

DBCC TRACEON (1222)

To enable trace at the Global level which will be traced across sessions we can use the following command,

DBCC TRACEON (1204, -1)

GO

DBCC TRACEON (1222, -1)

Viewing SQL Traces:

Once the log has been enabled in Global/Session level we can view the logs in any of the following methods,

1. Through Management Studio:

In SSMS, Object Explorer -> Management -> Sql Server Logs

2. Through Search Command: 

Execute the following Command in SQL Query window, here “deadlock” is a search text. So that SQL Engine will result Error Log contents which has text “deadlock” in it.

exec xp_readerrorlog 0,1,’deadlock’

 3. Through Log files repository:

To find log files repository OR to view all the logs in SQL window, execute the following command

 sp_readerrorlog

This command will return all the log statements in Query Results window. To get folder path where the logs are written stop the execution when you get around 20 row(s). In the result window we will see a line similar to “Logging SQL Server messages in file ‘E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG’.” This is the folder path on which the logs are being written.

To Turn Off Tracing: 

As how we enabled Trace the same way we should Turn Off tracing. But the difference is, Instead of TRACEON statement we have to use TRACEOFF statement. If we have enabled Session level tracing it should be turned off as below,

DBCC TRACEOFF (1204);
GO
DBCC TRACEOFF(1222);
GO

If we have enabled in Global Level, it should be as follows

DBCC TRACEOFF (1204,-1);
GO
DBCC TRACEOFF(1222,-1);

Simulating a Deadlock to verify Tracing: 

Run the following statement once,

IF DB_ID(‘DeadLockTest’) IS NULL
CREATE Database DeadLockTest
GO

USE DeadLockTest
GO

CREATE TABLE dbo.codl(id int identity(1,1) primary key clustered, col1 char(10) default ‘abc’)

INSERT INTO dbo.codl DEFAULT VALUES

Then, run the following Query statement in two different SQL Management Studio Sessions simultaneously. This will raise a deadlock in any one of the sessions.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM dbo.codl WHERE ID = 1
WAITFOR DELAY ’00:00:05′
UPDATE dbo.codl
SET col1 = ‘xyz’ WHERE id = 1
ROLLBACK TRAN

Now using Trace which we have enabled, we can get details of deadlock occurrences.

Find a column in sql database

If you’re looking to find a specific column in a huge database.

Assume, you need to get the table name which holds a column called “BlogStatus” from the content database of an application.

Found the below query,

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%BlogStatus%’
ORDER BY schema_name, table_name;

SQL Where clause

In Stored Procedures, sometime we need to omit input parameters from Where Clause.

For an instance, If the parameter is null, we should not include the value in Where clause conditional criteria.

In such cases, we can use the query efficiently as below,

 

WHERE

(@StatusCode IS NULL OR [StatusCode] like ‘%’+ @StatusCode +’%’)
AND (@FromDate IS NULL OR (DATEDIFF(DD, @FromDate, [CreatedOn]) >= 0)

AND(@ToDate IS NULL OR DATEDIFF(DD, @ToDate, [CreatedOn]) <= 0))

Format data in GridView rows at runtime

We can bind a gridview rows at runtime by customizing its row value in serveral ways. For an instance,

If the row has a DateTime value, we can customize its format at runtime.

Similarly, if we need to concatenate any value as “prefix” or “suffix” we can follow the below given snippet.

For your reference.

Columns required in the data source are,

UserID, UserName, CreatedDate

 

<asp:GridView runat=”server” ID=”gridList” AutoGenerateColumns=”false”>
<Columns>

<asp:TemplateField HeaderText = “User”>
<ItemTemplate>
<asp:Label ID=”lblUserID” runat=”server”>
(<%# Eval(“UserID”)%>) <%# Eval(“UserName”)%>
</asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText = “Created Date”>
<ItemTemplate>
<asp:Label ID=”lblUserID” Text='<%# Eval(“CreatedDate”,”Created On: {0:MM/dd/yyyy}”) %>’ runat=”server”>
</asp:Label>
</ItemTemplate>
</asp:TemplateField>

</Columns>
</asp:GridView>

 

 

Handle Browser close event

Have read this wonderful article to handle events before the browser being closed by the user.

Ref: http://eureka.ykyuen.info/2011/02/22/jquery-javascript-capture-the-browser-or-tab-closed-event/

Given the code as below,

 

/**
* This javascript file checks for the brower/browser tab action.

*/
var validNavigation = false;

function wireUpEvents() {
/**
* For a list of events that triggers onbeforeunload on IE
* check http://msdn.microsoft.com/en-us/library/ms536907(VS.85).aspx
*
* onbeforeunload for IE and chrome
* check http://stackoverflow.com/questions/1802930/setting-onbeforeunload-on-body-element-in-chrome-and-ie-using-jquery
*/
var dont_confirm_leave = 0; //set dont_confirm_leave to 1 when you want the user to be able to leave withou confirmation
var leave_message = ‘You sure you want to leave?’
function goodbye(e) {
if (!validNavigation) {
if (dont_confirm_leave!==1) {
if(!e) e = window.event;
//e.cancelBubble is supported by IE – this will kill the bubbling process.
e.cancelBubble = true;
e.returnValue = leave_message;
//e.stopPropagation works in Firefox.
if (e.stopPropagation) {
e.stopPropagation();
e.preventDefault();
}
//return works for Chrome and Safari
return leave_message;
}
}
}
window.onbeforeunload=goodbye;

// Attach the event keypress to exclude the F5 refresh
$(‘document’).bind(‘keypress’, function(e) {
if (e.keyCode == 116){
validNavigation = true;
}
});

// Attach the event click for all links in the page
$(“a”).bind(“click”, function() {
validNavigation = true;
});

// Attach the event submit for all forms in the page
$(“form”).bind(“submit”, function() {
validNavigation = true;
});

// Attach the event click for all inputs in the page
$(“input[type=submit]”).bind(“click”, function() {
validNavigation = true;
});

}

// Wire up the events as soon as the DOM tree is ready
$(document).ready(function() {
wireUpEvents();
});

 

 

Group Validation using Jquery

To validate more than one control using same Reg Ex, I have tried as follows.

It may be simple. But i have tried this for Starting up validation methods using Jquery.

index.html

<style type=”text/css”>

td{
height:50px;
vertical-align : top ;
}
   
.inputError{
background-color: #FFFFD5;
border: 2px solid red;
color: red;
}
   
.normaltext{
background-color:white;
color : black ;
border: black 1px solid;
}
.errorMessage{
color: red;
display: block;
font-family: tahoma;
font-size: 12px;
height: 10px;
margin-top: 4px;
}

</style>

<table cellspacing=”5″ cellpadding=”5″>

<tr>

    <td> Text Only</td><td>

    <input name=”textonly” type=”text”>
    <span class=”errorMessage”></span>
    </td>

    </tr>

    <tr><td>Text Only 2</td><td>

    <input name=”textonly” type=”text”>
    <span  class=”errorMessage”></span>
    </td>

    </tr>

    <tr><td>Number Only</td>

    <td><input name=”numberonly” type=”text”>
    <span  class=”errorMessage”></span></td>
   
    </tr>

</table>

Script:

<script type=”text/javascript”>

$(document).ready(function(){

 var textonlyErrorMessage =”Name should only contain text”;

var numberonlyErrorMessage = “Numeric characters only allowed”;

$(“input:text”).addClass(“normaltext”);

    $(“input:text[name=’textonly’]”).keyup(function(){

    if(this.value.match(/^\s*[a-zA-Z]+\s*$/) == null && this.value.length>0)

    {
       $(this).removeClass(“normaltext”).addClass(“inputError”);
      $(this).parent().find(“.errorMessage”).html(textonlyErrorMessage);
    }

    else

    {

    $(this).addClass(“normaltext”);
    $(this).parent().find(“.errorMessage”).html(“”);
    }

    })

    $(“input:text[name=’numberonly’]”).keyup(function() {

    if(this.value.match(/^\d*[0-9]+\d*$/) == null && this.value.length>0){

    $(this).removeClass(“normaltext”).addClass(“inputError”);
  
    $(this).parent().find(“.errorMessage”).html(numberonlyErrorMessage);
    }

    else

    {

    $(this).addClass(“normaltext”);
    $(this).parent().find(“.errorMessage”).html(“”);
    }

    })

});

</script>

As given above, This code will group the controls to validate based on its Reg EX. Here i have used “Name” attribute as group identifier.

Note: Remember to include Jquery API Library in script src attribute to run these things without error. ( https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js )

Sorting an XML based on Element using Linq

Have given a simple example to sort an XDocument based on XElement in C# using Linq.

Problem: Given an XML which holds Player Name and Score details. We need to display the Top 5 Players based on their scores.

  1.  Assuming that, we are passing an input XML as string. 
  2. XDocument supports Stream/XML Path.
  3. So we’re converting the String –> Byte Array –> Stream –> XDocument as follows,

byte[] byteArray = Encoding.ASCII.GetBytes(“<SCORES>”+ 

“<ENTRY><NAME>RAT</NAME><SCORE>30</SCORE></ENTRY>”+ 

“<ENTRY><NAME>RABBIT</NAME><SCORE>45</SCORE></ENTRY>”+ 

“<ENTRY><NAME>SWORD</NAME><SCORE>38</SCORE></ENTRY>”+ 

“<ENTRY><NAME>DRAGON</NAME><SCORE>78</SCORE></ENTRY>”+ 

“<ENTRY><NAME>DEER</NAME><SCORE>48</SCORE></ENTRY>”+ 

“<ENTRY><NAME>LION</NAME><SCORE>98</SCORE></ENTRY>”+ 

“<ENTRY><NAME>TIGER</NAME><SCORE>68</SCORE></ENTRY>”+ 

“<ENTRY><NAME>GORILLA</NAME><SCORE>88</SCORE></ENTRY>”+ 

“</SCORES>”);

MemoryStream ms = newMemoryStream(byteArray);

 XDocument xDocument = XDocument.Load(ms);

 var orderedScores = xDocument.Descendants(“ENTRY”)

.OrderByDescending(s => (int)s.Element(“SCORE”)).Take(5);

 

foreach (var orderedScore in orderedScores)

{

 Console.WriteLine(orderedScore.Element(“NAME”).Value + ” –> “

 + orderedScore.Element(“SCORE”).Value);

}