Easy way of Tracing open Data Reader

9. February 2012 11:32 by Mrojas in SQL Server  //  Tags: , , , ,   //   Comments (0)

Finding DataReaders that are not being close in an .NET application can be something difficult.

Thats why I use this trick to detect which DataReaders where not close.

1. First add a new code file to your project.

2. Enter this code:

public static class SqlReaderTracer
	{
		static Dictionary<WeakReference,System.Diagnostics.StackTrace> traceInfo = new Dictionary<WeakReference,System.Diagnostics.StackTrace>();
		public static System.Data.SqlClient.SqlDataReader ExecuteReaderEx(this System.Data.SqlClient.SqlCommand command)
		{
			var stack = new System.Diagnostics.StackTrace(1, true);
			var reader = command.ExecuteReader();
			traceInfo.Add(new WeakReference(reader), stack);
			return reader;
		}
		public static void PrintTraceState()
		{
			Console.WriteLine("*******SQL Trace Results************");
			foreach (var reader in traceInfo.Keys)
			{
				if (reader.IsAlive)
				{
					var traceInfoFrame =  traceInfo[reader].GetFrame(0);
					var methodWhereItWasCreated =traceInfoFrame.GetMethod().DeclaringType.FullName + "." + traceInfoFrame.GetMethod();
					Console.Write("Reader alive created in " + methodWhereItWasCreated);
					if (traceInfoFrame.GetFileName() != null)
					{
						Console.Write(string.Format("at {0} ({1},{2})",traceInfoFrame.GetFileName(),traceInfoFrame.GetFileLineNumber(),traceInfoFrame.GetFileColumnNumber()));
					}
					Console.WriteLine();
						
				}
			}
		}

	}

And then to a Find /Replace All and change all the .ExecuteReader() for .ExecuteReaderEx(). 3. Ok Now. You are all set. To test it do something like this:

class Program
	{
		
		static void Main(string[] args)
		{
			var xxx = new System.Data.SqlClient.SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\MyProjects\Phonebook.mdf;Integrated Security=True");
			xxx.Open();
			var ccc = xxx.CreateCommand();
			ccc.CommandText = "SELECT * from Phonebook";
			var reader = ccc.ExecuteReaderEx();
			xxx.Close();
			SqlReaderTracer.PrintTraceState();
			return;
		}
	}

And you will see an output like:

Tracing on RoleEntryPoint

30. June 2011 09:36 by Mrojas in General  //  Tags: , , , , ,   //   Comments (0)

I had a hard time trying to get some diagnostics from a RoleEntryPoint.
I was doing some setup in this entry point and getting some errors.
So I tought, mmmm: this is a Task for the super Azure DiagnosticMonitor.

And I added a bunch of Trace statements and waited to get some output in my WADLogsTables,
but NOTHING!!! ZERO NILCH! NADA!!

What happenned!!!

I took me a while to get to it. So

This is the things. I had to do.

1. First add a file called WaIISHost.exe.config

2. Add the Azure Diagnostics Trace Listener

<?xml version="1.0"?>
<configuration>

  <system.diagnostics>
    <trace>
      <listeners>
        <add name="AzureDiagnostics" type="Microsoft.WindowsAzure.Diagnostics.DiagnosticMonitorTraceListener, Microsoft.WindowsAzure.Diagnostics, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
          <filter type="" />
        </add>

      </listeners>
    </trace>
  </system.diagnostics>

</configuration>

3. And very very important, you must go to Copy to Output Directory property for this file and set it to Copy Always.

4. And another thing that you need is a lot of patience. The Diagnostics infraestructure takes a while.
So you add a Thread.Sleep after the Start call

DiagnosticMonitor.Start(storageAccount, configuration);
Thread.Sleep(10000);

5. After you do that you will be able to collect some information from the WADLogsTables