How to purge BizTalkMsgDb?

If the BizTalk Message Box database (BizTalkMsgDb) grows too large, performance of the database subsystem will reduce. As a rule of thumb, the BizTalkMsgDb should never grow larger than 5 Gb for large systems with long running transactions. For high-volume environment with no long running transactions, the BizTalkMsgDb size should be much smaller than 5Gb.

Would your message box have already grown too large and the system become unresponsive, you might want to clean up the BizTalkMsgDb database manually but keep in mind that:

  • The BizTalk Server must be taken down during the procedure.
  • All existing messages will be purged and lost.

There is an article in the MSDN documentation that explains in details how to manually purge data from the Message Box Database.

To make a long story short, here is step by step summary:

  • Stop ALL BizTalk service host instances from the Services console.
  • Restart IIS by running IISRESET from the command prompt if you are running any adapters in isolated hosts (for example HTTP, SOAP, or WCF).
  • Execute the stored procedure bts_CleanupMsgbox on your message box database. If the stored procedure does not exist, create it by running the sql script “msgbox_cleanup_logic.sql” found in the directory “\Schema\”.
    Important note: If you are running BizTalk Server 2006, you first have to get an updated version of the Msgbox_cleanup_logic.sql file to (re)create the bts_CleanupMsgbox stored procedure at http://support.microsoft.com/kb/924715. The hotfix is available for download directly without having to contact MS. If you are running BizTalk Server 2006 R2 then the updated version of the Msgbox_cleanup_logic.sql file is already installed and you do not need to download the hotfix.
  • Execute the stored procedure bts_PurgeSubscriptions on your message box database. If the stored procedure does not exist, you can create it by inspecting the sql script “msgboxlogic.sql” found in the directory “\Schema\”. Copy paste only the part of the script that creates this specific stored procedure and run it.

As deleting data in a database does not reduce the size the database files on the disk; you need to shrink the database files if you want to reduce its physical size. There are 2 simple ways to do it:

  • Through SQL Server Management Studio, right click on the BizTalkMsgDb database, click on Tasks > Shrink > Database
  • Through T-SQL, using the DBCC SHRINKDATABASE command: DBCC SHRINKDATABASE (BizTalkDTADb);

Microsoft does not support this procedure on production systems simply because this will purge production data. So, as long as you keep in mind that all existing messages will be purged, it is safe to run it on a production environment.

Truncating the Database Log File:
There is also another useful command to truncate the database logfile, would it be necessary (such as running out of disk space):
BACKUP LOG BizTalkDTADb WITH TRUNCATE_ONLY (*)
Note that you are not supposed to do this on production but emergency scenarios can call for the necessity.

(*) Edit:
The T-SQL BACKUP command does not support the TRUNCATE_ONLY parameter since SQL Server 2008. See T-SQL BACKUP Reference.
As explained in this post, it was discontinued because it breaks the ability to recover from a certain point in time until a full backup is run again. It was intended to be used only in case of emergency such as when we run out of disk space but apparently it was regularly misused by DBAs and maybe the reason was that it was a misunderstood option of the BACKUP command. It can indeed be a little misleading that an option of the BACKUP command can “break” the backup.
The way to truncate the log file on SQL Server 2008 and above in case of emergency is the following:

  1. Set the Database in Simple Recovery mode.
  2. Shrink the log file.
  3. Set the Database back into Full recovery mode.

Example taken from DBCC SHRINKFILE T-SQL reference:
USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO

How to Handle Unhandled Exception

When an unhandled exception occurs in the .Net Framework run-time 1.0 & 1.1, the Windows Process will terminate only if the exception occurred on the main application thread. Would an unhandled exception occur in a thread other than the main application thread, it will be caught by the runtime and therefore NOT cause the application to terminate. This means that unhandled exceptions in child threads disappear silently without anyone being able to know about it, implying that bugs are silently swallowed by the runtime without anyone being notified about it. The dangerous scenario is that a user might believe that a unit of work has executed successfully while it is actually not the case. This is in my opinion something not desirable and has been addressed in later version of the .Net Framework.

Indeed, from the .Net Framework 2.0 onwards, unhandled exceptions occurring on ANY thread (thus including any child threads being background threads or not) shuts down the application running the particular thread.

When the runtime terminates an application because of an unhandled exception, it writes an entry in the Windows Event Log which looks like the following:

EventType clr20r3, P1 processname, P2 1.0.0.0, P3 485f85f0, P4 system, P5 2.0.0.0, P6 471ebf0d, P7 3832, P8 bf, P9 system.componentmodel.win32, P10 NIL.

“processname” being the name of the .Net executable.

1. How to resolve unhandled exceptions? The System.AppDomain.UnhandledException event.

If an unhandled exception occurs in a windows application (multi-threaded or not), a windows service named winservice.exe for example, the runtime will terminate the service and write an Event Log entry such as:
“EventType clr20r3, P1 winservice.exe, P2 1.0.0.0, P3 485f85f0, P4 system, P5 2.0.0.0, P6 471ebf0d, P7 3832, P8 bf, P9 system.componentmodel.win32, P10 NIL.”

While it is good to be notified that a service crashed, the information supplied is rather cryptic and has very little added value. It would be nicer to have a way to log unhandled exceptions with a more meaningful message so that it is possible to get enough information to be able to fix the source code and re-deploy the application.

Conveniently, the .Net Framework provides the System.AppDomain.UnhandledException event. This event fires whenever an application domain unloads because of an unhandled exception. Note that registering for the event does not cause unhandled exceptions to be “handled”. It simply notifies you that an exception has gone unhandled, so that it is possible to take some action such as logging the exception message and stack trace and eventually do some clean up before the application dies. The Exception is still unhandled and so still causes the application to shut down.

The discussion in this article is not a solution on how to handle exceptions. Exception handling is a strategy on how to handle exceptions and is a different discussion. This article explains how to act with unhandled exceptions so that details about the exception can be logged so that a proper solution can be found to resolve the issue/bug.

Ideally, all exceptions should be handled in the source code with try-catch-finally blocks so that unhandled exceptions do not occur. Nevertheless, there are always unforeseen cases and developer mistakes which, in my opinion, justify caring for unhandled exceptions across the board.

Here is an example on how to register for the event with an event handler that log exception details in the Windows Event Log:

// This event provides notification of uncaught exceptions. Write this in the entry point of your program, like in the OnStart() method of a Windows Service.
AppDomain currentDomain = AppDomain.CurrentDomain;
currentDomain.UnhandledException += new UnhandledExceptionEventHandler(UnhandledExceptionHandler);
static void UnhandledExceptionHandler(object sender, UnhandledExceptionEventArgs args)
{
Exception ex = (Exception)args.ExceptionObject;
EventLog.WriteEntry(“WinService.exe”, “Unhandled Exception caught: ” + ex.Message + ex.StackTrace, EventLogEntryType.Error);
}

2. Note for Windows Form Applications – Application.ThreadException event.

2.1 Application.ThreadException event

For Windows Form Applications, there is another event that is raised when an unhandled exception occurs, the System.Windows.Forms.Application.ThreadException. Nevertheless, this event fires only when unhandled exceptions happen in Window Forms threads (UI threads). This means when an exception is thrown from code that was ultimately called as a result of a Windows Message. Windows Messages are emitted by keyboard hits, mouse clicks or “paint” messages,… in short, nearly all code in a typical Windows Forms application.

While this works perfectly, it lulls one into a false sense of security that all exceptions will be caught by the central exception handler:

  • Exceptions thrown on worker threads are a good example of exceptions not caught by Application.ThreadException.
  • Exceptions thrown by the code inside the Main method of the Windows Forms application, including the main form’s constructor, executes before the Windows message loop begins and so is another example of exceptions that do not raise the Application.ThreadException event.

In this case, we said the Application.ThreadException event handler to be a “central exception handler” because it is still possible for the application to keep running when this event is raised, depending on what kind of logic is implemented in the handler.

As a reminder, Worker Threads are threads:

  • Created manually: Thread.Start()
  • Created by the ThreadPool: ThreadPool.QueueUserWorkItem()
  • Created by any kind of asynchronous call which internally uses a thread pool thread to execute: Delegate.BeginInvoke(), BeginXXX()

One must attach a handler to the Application.ThreadException event before instantiating the main form of the application by calling Application.Run(). Also, because this is a static event, you must detach the event handler(s) when the application is disposed or memory leaks will result.

The Application.ThreadException as a default event handler which behaves in the following way:

  • If an unhandled exception occurs in the main application thread, the default exception handler catches it and terminates the application.
  • If an exception occurs in a thread other than the main application thread, the thread exits, but the application continues to run.

2.2 Application.SetUnhandledExceptionMode

It is possible to instruct the application whether it should catch all unhandled exceptions thrown by Windows Forms components and terminate the application, or whether it should raise an event so that an event handler can be implemented; the event handler could halt execution and expose the unhandled exception to the user. This is setting is done by using the application configuration file or the Application.SetUnhandledExceptionMode() method.
It is possible to instruct the application whether it should catch all unhandled exceptions thrown by Windows Forms components and terminate the application, or whether it should raise an event so that an event handler can be implemented; the event handler could halt execution and expose the unhandled exception to the user. This is setting is done by using the application configuration file or the Application.SetUnhandledExceptionMode() method.

  • UnhandledExceptionMode.ThrowException never route exceptions to the Application.ThreadException event handler and so the default event handler will terminate the application when an unhandled exception occurs as explained earlier.
  • UnhandledExceptionMode.CatchException always route exceptions to the Application.ThreadException event handler.

Again, as a reminder, the Application.ThreadException event handler is only for unhandled exception occurring on UI threads and so the SetUnhandledExceptionMode() method affects only the way unhandled exceptions coming from UI threads are treated, it does not affect how non UI threads unhandled exceptions are treated. System.AppDomain.UnhandledException event handlers will always be called when non UI threads unhandled exceptions occur.

2.3 Sample

Hereunder is a code sample on how to register to the Application.ThreadException event in a Windows Form Application. As said earlier the event handler will only be called for unhandled exceptions occurring on the UI thread. Thus, the code sample also has an event handler for unhandled exception on non-UI threads by registering to the System.AppDomain.UnhandledException event:

static class Program
{
///

/// The main entry point for the application.
///

[STAThread]
static void Main()
{
// Add the event handler for handling UI thread exceptions to the event.
Application.ThreadException += new ThreadExceptionEventHandler(Form1_UIThreadException);

// Set the unhandled exception mode to force all Windows Forms errors on UI threads
// to go through our handler regardless of application settings.
Application.SetUnhandledExceptionMode(UnhandledExceptionMode.CatchException);

// Add the event handler for handling non-UI thread exceptions to the event.
AppDomain.CurrentDomain.UnhandledException +=
new UnhandledExceptionEventHandler(CurrentDomain_UnhandledException);

// Default generated code by Visual Studio for WinForms
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}

You can find a zip file here containing a complete Visual Studio 2008 solution demonstrating both type of unhandled exception event handler and their effect.

3. Note for ASP.NET applications – Application_Error in Global.asax.

Would an unhandled exception occurs in an ASP.NET application on a worker thread, the runtime will terminate the worker process (w3wpp.exe) and write an Event Log entry such as:
“EventType clr20r3, P1 w3wp.exe, P2 6.0.3790.1830, P3 42435be1, P4 app_web_7437ep-9, P5 0.0.0.0, P6 433b1670, P7 9, P8 a, P9 system.exception, P10 NIL.”

In ASP.Net, there is an Application_Error method in the Global.asax file that can be implemented so that an action can be taken when an unhandled exception occurs (logging the exception details, for example). Again, treating an unhandled exception in such a way is not handling an exception (that should be done in the try-catch-finally block in the code), the web application will still crash and display an error message on the browser but it lets the programmer have a way to log information about the exception and eventually do some other clean-up tasks.

Nevertheless, similarly to Windows Forms Application, the Application_Error will be called only for unhandled exception that occurred on the main thread. Unhandled exceptions occurring on worker threads will not be caught by the Application_Error event handler in the global.asax file. If we want to be notified of unhandled exception occurring on worker threads, we need to register an event handler to the System.AppDomain.UnhandledException event, as we did for Windows Services and Forms Applications. The event handler should at least log the exception and eventually do some clean tasks.

Microsoft has a KB on how to implement an event handler for the System.AppDomain.UnhandledException event within an HTTPModule.

You can find here a zip file with a Visual Studio 2008 solution using the HTTPModule defined in the KB. I registered the HTTP module in the web.config the following way:



.Net assembly is said to have a strong name when it is signed.

There are 2 ways to retrieve the Public Key Token from a strongly named assembly:

  • The lazy way is to add the assembly in the GAC and go to C:\WINDOWS\assembly, one of the column showed in Windows Explorer is the Public Key Token. Only strongly named assemblies can be stored in the GAC.
  • Run the command line “sn -T UnhandledExceptionModule.dll” which prints the Public Key token of a strongly named assembly.

Contrary to the instructions given in the KB, I did not GAC either NGEN the HTTP module; I just referenced the HTTPModule project from the web project so that the HTTP module library is automatically copied to the web application bin folder. All assemblies in the bin folder are automatically found by the run-time so that is not necessary to GAC all third party libraries.

SQL Server and .Net Data Type mapping

Today I would like to post a table listing the corresponding .Net Type to use with each SQL Data Type. This comes in handy when writing ADO.NET code.

The following link lists Microsoft SQL Server data types and their equivalent data type in .Net.
To be more exact it lists:

  • Every SQL Server data types
  • Their equivalent in the Common Language Runtime (CLR) for SQL Server in the System.Data.SqlTypes namespace
  • Their native CLR equivalents in the Microsoft .NET Framework

SQL Server Data Types and Their .NET Framework Equivalents

I have also copy-pasted the SQL Server and .Net Data Type mapping table hereunder for my own convenience:

SQL Server data type CLR data type (SQL Server) CLR data type (.NET Framework)
varbinary SqlBytes, SqlBinary Byte[]
binary SqlBytes, SqlBinary Byte[]
varbinary(1), binary(1) SqlBytes, SqlBinary byte, Byte[]
image None None
varchar None None
char None None
nvarchar(1), nchar(1) SqlChars, SqlString Char, String, Char[]
nvarchar SqlChars, SqlString SQLChars is a better match for data transfer and access, and SQLString is a better match for performing String operations. String, Char[]
nchar SqlChars, SqlString String, Char[]
text None None
ntext None None
uniqueidentifier SqlGuid Guid
rowversion None Byte[]
bit SqlBoolean Boolean
tinyint SqlByte Byte
smallint SqlInt16 Int16 (short in C#)
int SqlInt32 Int32 (int in C#)
bigint SqlInt64 Int64 (long in C#)
smallmoney SqlMoney Decimal (decimal in C#)
money SqlMoney Decimal (decimal in C#)
numeric SqlDecimal Decimal (decimal in C#)
decimal SqlDecimal Decimal (decimal in C#)
real SqlSingle Single (float in C#)
float SqlDouble Double (double in C#)
smalldatetime SqlDateTime DateTime
datetime SqlDateTime DateTime
sql_variant None Object
User-defined type(UDT) None Same class that is bound to the user-defined type in the same assembly or a dependent assembly.
table None None
cursor None None
timestamp None None
xml SqlXml None

Regarding CLR Data Types, Data Types are basically divided in 3 categories:

Furthermore, 3 types of CLR Data Type structures exist to hold numeric values:

See Data Types (C# Reference) for a complete reference and correspondance between CLR types and C# types.

Multi-Threading in C# tutorial

I have read the Threading in C# tutorial written by Joseph Albahari.

It is by far the most complete, yet easy to read tutorial I have read so far on threading in .Net. It can also serve as introductory material for anyone unexperienced with multi-threading concepts. Most .Net / C# books usually have a chapter or two on the topic but most often just scratch the surface of multi-threading in .Net.

I highly recommend anyone to read his paper.

T-SQL Weighted Round Robin Algorithm

In a previous post I was discussing of ways to load balance MSMQ messages to a group of servers by implementing a Weighted Round Robin Algorithm in a software module.

I chose to implement the load balancing feature in the database for 2 main reasons:

  • The application is heavily database driven; stored procedures have to run to retrieve the data that will be used to build each MSMQ message. Therefore, the cost of an additional statement or stored procedure to retrieve the MSMQ path the message should be sent to is much less than the existing cost.
  • All the application configuration information is also database driven. This is because our organization uses custom tools for administrators so that they can configure servers and applications centrally. In the case at hand, they need to be able to modify queue names, machine relative weights as well as to add and remove machines from the load balanced group of server (the cluster).

The Weighted Round Robin algorithm is implemented as a stored procedure returning the next MSMQ path a message should be sent to. The stored procedure could be bundled or merged with the other stored procedures that have to run before each MSMQ message is sent, so that it would only be one more parameter coming back from the database. This parameter will tell the .Net code to which queue the current MSMQ message build has to be sent to.

T-SQL Weighted Round Robin Algorithm Implementation.

Each machine of a cluster should receive a percentage of messages which is relative to its weight:
%age = ((weight of machine) / (sum of weights for all the machines in the cluster)) * 100
Moreover, the distribution of messages to each machine should be function of the weight in real time, not only in average.

To satisfy these conditions, one way to implement the weighted round robin algorithm and which suits well T-SQL capabilities is to calculate a ratio between the relative number of messages sent to a particular machine of a cluster and the relative weight of that machine within the cluster. The machine having the higher ratio will be the next machine a MSMQ message should be sent to in respect with its relative weight. An ORDER BY clause will easily implement that in T-SQL.

First of all, we need to create a table where we will hold the different server parameters:

Column name Data type Purpose
server_name [nvarchar](20) Machine name
cluster [char](10) Cluster name in which the machine belongs to
queue_path [nvarchar](100) MSMQ path
number_requests_sent [float] Number of request sent to this machine so far
weight_factor [float] Weight factor of this machine
enabled_status [bit] Says if the machine is active or inactive in the cluster

Here is the SQL script to create the table:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[msmq_machines](

[server_name] [nvarchar](20) NOT NULL,

[cluster] [char](10) NOT NULL,

[queue_path] [nvarchar](100) NOT NULL,

[number_requests_sent] [float] NOT NULL CONSTRAINT [DF_msmq_machines_number_requests_sent]DEFAULT ((0)),

[weight_factor] [float] NOT NULL CONSTRAINT [DF_msmq_machines_weight_factor]DEFAULT ((100)),

[enabled_status] [bit] NOT NULL,

CONSTRAINT [PK_msmq_machines] PRIMARY KEY CLUSTERED

(

[server_name] ASC,

[cluster] ASC

)WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE= OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS= ON, ALLOW_PAGE_LOCKS= ON) ON [PRIMARY]

) ON [PRIMARY]


GO

SET ANSI_PADDING OFF

Second we need to create a Stored Procedure that will actually implement the algorithm:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[GetLoadBalancedQueueName]

@cluster as char(10),

@serverName as nvarchar(20) out,

@queuePath as nvarchar(100) out

AS

DECLARE @totalClusterRequestSent as float

DECLARE @totalWeight as float

SELECT @totalClusterRequestSent = sum(number_requests_sent)+1, @totalWeight = sum(weight_factor)

FROM dbo.msmq_machines

WHERE cluster = @cluster

AND enabled_status = 1

SELECT top 1 @serverName = server_name, @queuePath = queue_path

FROM dbo.msmq_machines as A

WHERE A.cluster = @cluster

AND A.enabled_status = 1

order by (number_requests_sent/@totalClusterRequestSent)/(weight_factor/@totalWeight), weight_factor desc

UPDATE dbo.msmq_machines

SET number_requests_sent = number_requests_sent + 1

WHERE server_name = @serverName

AND cluster = @cluster

Note on the T-SQL implementation:

  • In the definition of the table msmq_machines, I have chosen data types to be float instead of integers (as they actually will hold only integers values) so that no data type conversion is needed when calculating ratios.
  • @totalClusterRequestSent is calculated as the sum of number_requests_sent + 1 so that if the sum of number_requests_sent is 0 (zero), no division by zero error occurs.

In addition of this, an SQL job will have to run on a regular basis to re-initialize the number_requests_sent column to 0 (zero) so that the value never overflows the data type.

T-SQL weighted Round Robin algorithm Testing

We can test if the algorithm works as expected by creating 3 machines in the msmq_machines table and give them different weights:
MSMQ Load Balancing Weighted Round Robin Configuration Table
If we send 100 messages to the cluster “Bangkok”:

  • DEV1 should receive (100 / 350) * 100 = 28.57 -> 29 messages
  • DEV2 should receive (200 / 350) * 100 = 57.14 -> 57 messages
  • DEV3 should receive (50 / 350) * 100 = 14.28 -> 14 messages

We can create an SQL script that calls the Stored Procedure 100 times and see if the result is what is expected.

declare @counter int

set @counter = 0

while @counter < 100

begin

set @counter = @counter + 1

DECLARE @server as nvarchar(20)

DECLARE @msmq as nvarchar(100)

EXEC [dbo].[GetLoadBalancedQueueName] ‘Bangkok’ , @serverName = @server out, @queuePath = @msmq out

print @server

print @msmq

end

Opening the msmq_machines table, we can see that the expected number of messages has been sent to each server of the cluster proving that the implementation works. MSMQ Load Balancing Weighted Round Robin Configuration Table Result