Using Fiddler for viewing exchanged messages with BizTalk

I was trying to view the SOAP messages and headers that were actually exchanged with a certain webservice.
To do so, I used Fiddler2... available from http://fiddler2.com/.

Fiddler2 will allow you to monitor traffic when using HTTP, SOAP or WCF ports in BizTalk. However, Fiddler will not trace any messages sent to endpoints by BizTalk by default as it does not use WinInet. To overcome this issue, you'll need to configure a proxy to allow Fiddler to intercept the messages.
Note that these proxy settings will need to be removed when Fiddler is not running. Because otherwise all traffic through this proxy will not be received by anything.

In the send port of BizTalk go to the Configuration settings. Open the tab 'Proxy' to configure the necessary settings. Now you should change following settings:

• Server: 127.0.0.1
• Port: 8888

That's is all you need to do. Now open fiddler2 and process a message. If all is configured correctly, you should see the exchanged messages coming through fiddler.

Guide to a BizTalk High Availability Server Environment

I just discovered a series of blog post that I just had to share with all of you... It's a complete guide to set up a High available BizTalk environment.
A perfect walthrough from start to finish to obtain a BizTalk High availability environment.

The guide covers 7 blog posts in total:

• Part 1: Preparations
• Part 2: Domain Controller Installation
• Part 3: SQL & BizTalk Active Directory Accounts
• Part 4: Prepping our SQL & BizTalk Failover Clusters
• Part 5: SQL Server 2008r2 Failover Cluster
• Part 6: BizTalk 2010 Failover Cluster Creation
• Part 7: BizTalk 2010 Installation, Configuration and Clustering

Congrats to René Brauwers on the great guide for setting up a High available BizTalk environment.

Change SQL backup location

During the installation of a SQL instance, you'll have the ability to define some folders
These locations can be defined:

• User database directory
• User database lof directory
• Temp DB directory
• Temp DB log directory
• Backup directory



Within SQL Server Management Studio you have the ability to change the default location for your Data and Log files for all new databases. Just  right click on the server name and select properties, navigate to the Database Settings page. Here you can find a section Database default locations for changing the data and log directories.



But if you search through all of the pages under Database Settings you will not find anything that shows the default backup directory.  To find this we need to look in the registry.

Open the registry tool REGEDIT and navigate to following key:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer

Or something similar for your instance of SQL server. The registry key BackupDirectory is the one you'll need to change to set another default Backup Directory.



Changing the registry can also be done with a T-SQL query command. To do so, you'll be using the extended stored procedures XP_REGREAD and XP_REGWRITE.

Reading the falue in registry can be done by using this command:

DECLARE @BackupDirectory VARCHAR(100)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer',
  @value_name='BackupDirectory',
  @BackupDirectory=@BackupDirectory OUTPUT SELECT @BackupDirectory

This will result in something similar as this:

Changing the default folder can be done by using the following command

EXEC master..xp_regwrite
     @rootkey='HKEY_LOCAL_MACHINE',
     @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer',
     @value_name='BackupDirectory',
     @type='REG_SZ',
     @value='C:\Program Files\Microsoft SQL Server\MSSQL10_50.BTSTPTST\MSSQL\Backup'

key file authentication with the BizTalk SFTP adapter

Authentication on an SFTP location can be done with simple username/password credentials… However another way to authenticate the sender is by using a Key Authentication file. But how can this last authentication method be used inside the BizTalk SFTP adapter?

the BizTalk SFTP adapter we used to achieve this is the  freely available SFTP adapter on Codeplex. Just download the adapter and install it. And don’t forget to add the adapter in the BizTalk admin console of course.

Now then let’s set up the port in BizTalk. In my example I ‘ve configured a receive location, but obviously the same goes for the send port configuration.

First of all choose the created adapter in the biztalk admin console, as shown below.



To set up the proper configuration for the SFTP port, just press the ‘Configure’-button.



These parameters need to be set:

• Schedule: define a timely schedule how often you want to poll the SFTP location (receive location only)
• File Mask: set the correct file mask
• SSH Host: the host address of the SFTP location
• SSH Identyfile: Select the Key authentication file on disk
• SSH Identyfile Passphrase: the password of the selected key authentication file
• SSH Remote Path: the path on on the SFTP location
• SSH User: the user name to authenticate on the SFTP location

Seems pretty straight forward, doesn’t it?
Well there are some things you ‘ll need to take into account to make sure the connection can be made…

Make sure the identyfile type is supported

You’ll need to make sure the identyfile type is supported by the library. The supported versions can be found here. As this is the library which is used by the bizTalk SFTP adapter.

Make sure the identyfile can be recognized

In my case for example, I got an *.ppk file as authentication file. But as it turned out, this wasn’t recognized.
The error message you git is this:

invalid privatekey: D:\tempkey.ppk

Which in the end seemed a bit misleading, as the problem wasn’t the private key for the authentication file. It was just the *.ppk file that wasn’t supported.
To make the key authentication file working, I had to convert the file to an OpenSSH key file. This can be done by using PuttyGen for example, and exporting the authentication file as an OpenSSH key file.

Don’t use both password parameters

Also make sure you only use the intended parameter SSH Identyfile Passphrase, and leave the SSH password blank.
In case both password fields are filled, the SFTP adapter will try to authenticate by the username/password credentials stated. So it won’t offer the specified identyfile to authenticate.




I orignally posted this on my companies blog: http://blog.cnext.eu/2013/02/19/using-key-file-authentication-with-the-biztalk-sftp-adapter/

Exception handling in BizTalk orchestrations explained

When creating BizTalk orchestrations, we also have some building blocks available to do exception handling. In this blog I'll try to give a very brief explanation of the available building blocks for your orchestration. I won't describe everything in detail, because this would lead us to far (and would give one massive blog post).

For exception handling in orchestration there are 3 shapes available:

• Throw Exception
• Scope
• Compensate

I want go in much details for the Scope and Compensate shapes. If you need more detailed description on how Exception handling can be used with these 2 shapes I would suggest reading this blog from Richard Seroter. And if this still doens't answers all of your questions, let me know... than I can devote another post with a more detailed explanation on some aspects of the exception handling in orchestrations.

Throw Exception

Firstly we have the 'Throw Exception'-shape. this one is the simplest of all shapes for exception handling.
Frankly, it does nothing more than it's name suggests... It just throws an Exception, nothing more fancy. To achieve this you'll need to follow these three simple steps:

1. Define a variable with the type set to any Exception class you prefer. In below example I've created an exception variable of type Microsoft.Biztalk.Message.Interop.BTSException




Note: The Report To Analyst property in the screenshot exists on many shapes. The property however has no influence on the behavior of your orchestration. I merely is a property used to integrate withe the Orchestration Designer for Business Analysts (ODBA) Visio plugin. If set to false, that shape will be considered as a low level detail that won't be visible in the Visio Designer.


2. Instantiate the variable and set any values according to the occured error
3. Now use the Throw Exception shape and configure it ro use the Exception object you've created

Scope

Scopes are used for three reasons

1. to configure transaction (long running or atomic)
2. to handle exceptions
3. to trigger compensating logic

For more info on the transaction types, I'll refer to this blog from Sandro Pereira.

A scope shape can have one or more Exception handling blocks and/or one Compensation Block. The Transaction type you've chosen for your scope will define which of these blocks can be added. An atomic scope can only have a compensation block, and a scope configured with the transaction type non can only have an Exception block. While a long running scope can have both blocks.
For an overview of the possibilities, check the below screenshot.

Note that there could be more than one Exception block, but only one Compensation block added to a Scope

Exception handling block

The Exception handling block is used to catch exceptions that occur inside the scope the block belongs to. The Exception Object Type in the properties of the exception block will define which Exceptions types will be handled by this Exception block.

When an exception occurs, the engine will check for the closest exception block that can handle this type of exception. The consideration of which exception block will be called is determined sequentially, just like in .NET. If no suitable exception handler is found, the default exception handles is triggered, and this means that the compensation blocks for all nested scopes will be called, after which the exception is rethrowed and the orchestration will become suspended.

Compensation block

Compensation is a way to correct or undo logical piece of work that have previously committed. An exception can occur in the orchestration afther succesfully executing all logic in the scope. In this case, the process might be in a state where it is logically incorrect. So the action already performed in a transactional scope need to be compensated... this can be achieved using a compensation block
If no compensation block is added, the default compensation will be performed. this means calling all compensation blocks of the nested scopes, starting with the most recently completed ones, just as with the default exception handler.

Compensate

The compensate shape can only be used inside of an exception handler. The explicit use of this compensate shape, allows us to specify the order of compensation execution ourselfs. So this can be used in cases the default order (starting from te most recently completed ones and working its way back) doesn't fit.
1 compensate shape can only trigger 1 compensation block of a transaction. So the order must be defined using multiple compensate shapes, calling the compensation blocks in the desired order.

Remarks

• Keep in mind that the default Exception handler, if triggered, will initiate the compensation by calling all compensation blocks for the nested scopes. Compensation blocks of the nested scopes will be called starting with the most recently completed scope and work its way back.
  
  But whenever you catch an exception using the Exception handler block, then you must explicitly do the compensation.



• If no custom compensation block has been added, the default compensation will be performed. Just like with the exception handler, this will call the compensation blocks of any nested scopes starting with the most recently completed scope.



• A Compensate shape can only be used inside an Exception handling block or another Comensation Block. When calling the compensate of the current scope, the default compensatino handler will be triggered instead of the compensation code defined by the compensation block.

BizTalk: using macros in file names

BizTalk allows you to use several macros for defining a file name in a biztalk send port.

The following table lists the supported macros and describes how the File send handler replaces them.

Macro name	Substitute value
%datetime%	Coordinated Universal Time (UTC) date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508).
%datetime_bts2000%	UTC date time in the format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (for example, 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds).
%datetime.tz%	Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).
%DestinationParty%	Name of the destination party. The value comes from the message context property BTS.DestinationParty.
%DestinationPartyQualifier%	Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier.
%MessageID%	Globally unique identifier (GUID) of the message in BizTalk Server. The value comes directly from the message context property BTS.MessageID.
%SourceFileName%	Name of the file from which the File adapter read the message. The file name includes the extension and excludes the file path, for example, Sample.xml. When substituting this property, the File adapter extracts the file name from the absolute file path stored in the FILE.ReceivedFileName context property. If the context property does not have a value—for example, if a message was received on an adapter other than the File adapter—the macro will not be substituted and will remain in the file name as is (for example, C:\Drop\%SourceFileName%). Note Correct implementation of this macro requires that the output message is the same message as the received message.
%SourceParty%	Name of the source party from which the File adapter received the message. Note Correct implementation of this macro requires that the output message is the same message as the received message.
%SourcePartyQualifier%	Qualifier of the source party from which the File adapter received the message. Note Correct implementation of this macro requires that the output message is the same message as the received message.
%time%	UTC time in the format hhmmss.
%time.tz%	Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).

NOTE

The File send handler does not replace the macros with a value if any of the following are true:

• The corresponding system property is not set.
• The macro is misspelled.
• The value for the macro contains symbols that are not valid in the file name.

Using Macros in SMTP Headers

There are however some restrictions when using the macros in SMTP headers.
See this post for more details on this matter.

Below is a short overview of the use of macros in SMTP headers.

Macro	Description	For use with To	For use with CC	For use with Subject
%MessageID%	Globally unique identifier (GUID) of the message in BizTalk Server. The value comes from the message context property BTS.MessageID.	No	No	Yes
%datetime_bts2000%	UTC date time in the format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (for example, 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds).	No	No	Yes
%datetime%	UTC date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508).	No	No	Yes
%datetime.tz%	Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).	No	No	Yes
%time%	UTC time in the format hhmmss.	No	No	Yes
%time.tz%	Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).	No	No	Yes
%SourceParty%	Name of the source party from which the File adapter received the message.	No	No	Yes
%SourcePartyQualifier%	Qualifier of the source party from which the File adapter received the message.	No	No	Yes
%DestinationParty%	Name of the destination party. The value comes from the message context property BTS.DestinationParty.	Yes	Yes	Yes
%DestinationPartyQualifier%	Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier.	No	No	Yes

Source: http://msdn.microsoft.com/en-us/library/aa578022(v=bts.20).aspx

Using System Colors in C#

When choosing a color in the properties window in visual studio, there is the possibility to choose from so called System Colors. An example for this is the color 'Window', which is the default for a textbox.
Now, I tried to set the BackColor of a Textbox programmatically back to this system color 'Window'. Turned out not to be as easy as I thought it would be.

I tried things like Color.? ... I hoped to find the color in this namespace, or even a System subsection for the systemcolors. Nothing to be found however.
Nor was there any namespace like System.Color to be found.

But how can these system colors be set from C# code than?

Well, one way turned to be using this command:

System.Drawing.Color.FromKnownColor(KnownColor.Window)

And, as you'd expect, the KnownColor enumeration contains all of the other user-definable colors.

Or even an easier method to achieve this is

System.Drawing.SystemColors.Window

SQL Fragmentation explained

Found an interesting article on fragmentation in SQL.

It explains the two types of fragmentation that can occur, namely internal and external fragmentation.
Internal fragmentation means that the pages are not completely full. While external fragmentation refers to the lack of correlation between the logical sequence of an index and its physical sequence.

For more details I'll refer to this excellent blog post.

Source: Stairway to SQL Server Indexes: Level 11, Index Fragmentation - SQLServerCentral

Difference between 2 dates in C#

I tried to do a simple DateTime calculation in C#. What I wanted to achieve is to know how many years, months and days where between 2 DateTime variables. Or at least I thought this would be easy. Turns out there is know real out-of-the-box functionality available to handle this in C#.

The only thing available out-of-the-box in C# is using TimeSpan. Unfortuanately the only calculations TimeSpan can return is the number of days, hours, minutes, seconds and milliseconds. But how about the years, months and days than?

Here's a simple method to show the possibilities of the TimeSpan:

public void PrintDateDiffUsingTimeSpan(DateTime begin, DateTime end)
        {
            TimeSpan span = end - begin;

            StringBuilder str = new StringBuilder();
            str.Append("Total span: ");
            str.Append(span.Days + " days, ");
            str.Append(span.Hours + " hours, ");
            str.Append(span.Minutes + " minutes, ");
            str.Append(span.Seconds + " seconds, ");
            str.Append(span.Milliseconds + " milliseconds");

            Console.WriteLine(str.ToString() + "\n");


            Console.WriteLine("Total Days: " + span.TotalDays);
            Console.WriteLine("Total Hours: " + span.TotalHours);
            Console.WriteLine("Total Minutes: " + span.TotalMinutes);
            Console.WriteLine("Total Seconds: " + span.TotalSeconds);
            Console.WriteLine("Total Milliseconds: " + span.TotalMilliseconds);
        }

I ended up using a freely available .NET library called 'Time Period'. The source code can be found here. There's also an on line demo and documentation available.
Here's a simple method to show some the possibilities of the TimeSpan:

  public void PrintDateDiff(DateTime begin, DateTime end)
        {
            DateDiff diff = new DateDiff(begin, end);
            
            StringBuilder str = new StringBuilder();
            str.Append("Total difference: ");
            str.Append(diff.ElapsedYears + " years, ");
            str.Append(diff.ElapsedMonths + " months, ");
            str.Append(diff.ElapsedDays + " days");

            Console.WriteLine(str.ToString() + "\n");

            Console.WriteLine("Total Days: " + diff.Days);
            Console.WriteLine("Total Weeks: " + diff.Weeks);
            Console.WriteLine("Total Weekdays: " + diff.Weekdays);
            Console.WriteLine("Total Quarters: " + diff.Quarters);
            Console.WriteLine("Total Hours: " + diff.Hours);
        }

There are much more possibilities in the DateDiff class of the Time Period library. There are even much more classes available in the library. To know more of the capabilities of this library I again refer to the documentation.

Source: http://www.itenso.com/en/

Identify Performance Bottlenecks in BizTalk

I found a series of blogs on how to identify Performance bottlenecks in BizTalk Environments.
Also look at this article on finding and eliminating bottlenecks

Here's an short overview of all the steps, including a link to the more detailed explanation:

Step 1: Monitoring BizTalk Host Instances via Windows Performance Counters

 The first place to measure the BizTalk performance can be done using Performance Counters. These counters let you monitor all components like the message box, orchestrations, adapters, etc… .

Step 2: Analyzing BizTalk Adapters

A deeper look at the adapter performances, again using some performance counters.

Step 3: Analyze Pipelines

It is important to understand the impact of Pipeplines that are used. Because each of the pipelines have a significant impact on the overall BizTalk performance as they perform actions on every single message that gets through BizTalk
One way to analyze what is really going on in pipelines is using an Application Performance Management Solution with Transactional Tracing capabilities, like dynaTrace for example.

Step 4: Analyzing Orchestration

Orchestration can get quite complex sometimes, but how to analyze the performance of these orchestrations?

The actual logic behind the Orchestration Definition is compiled into an assembly, loaded into BizTalk, and gets executed when messages are processed by that Orchestration Definition. A more detailed overview of the execution of such an orchestration can be generated with PurePath for example.

Another way to analyze the orchestration performance can again be done using performance counters.  BizTalk provides a set of counters for the Orchestration Engine as well as for the MessageBox.

Step 5: Analyzing external service calls (SendPorts)

Often BizTalk Orchestration makes a call to an external web service. PurePath can also give you an idea of the time consumed for this external web service call.

FINAL NOTE

As a final note I would like to point out to use the MessageBox Viewer Tool. The tool gives an overview of what might be wrong in your installation.
This tool runs a set of tests and queries against the MessageBox database an creates a nice report. The result can be viewed using the tool or it can generate an HTML report.

Best practices on Biztalk Tracking

I announced in a previus post that I joined the blogging team on biztalkadminsblogging.com.
However it took untill today before my first blog post was created.

I gave it a long thought what my first blog post should cover. the result is a post on some best practices for the usage of tracking in BizTalk... hope you like it!

The actual post can be found here http://www.biztalkadminsblogging.com/index.php/item/83-best-practices-for-tracking

Calling Pipelines with custom property settings from an orchestration

In my previous blog post I explained how to execute pipelines from an orchestration using the default pipeline property settings.
Now I would like to show you how to call pipelines from an orchestration while configuring the pipeline with custom property settings. To achieve this the standard methods wouldn't suffice.

First of all you'll need to add some extra functionalities. Here's my code to achieve this:

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data.SqlClient;
using System.Runtime.Serialization;

using Microsoft.XLANGs.Pipeline;
using Microsoft.BizTalk.PipelineOM;
using Microsoft.XLANGs.BaseTypes;
using System.IO;
using System.Reflection;
using System.Collections;
using System.Data;
using System.Text.RegularExpressions;
using Microsoft.XLANGs.RuntimeTypes;
using Microsoft.XLANGs.Core;
using MIME;
using System.Net;

namespace TP.BTS.GlobalFunctions
{

public class PipelineInstance
    {
        internal Microsoft.BizTalk.PipelineOM.Pipeline pipeline = null;

        public static PipelineInstance CreateInstance(Type type)
        {
            PipelineInstance instance = new PipelineInstance();
            instance.pipeline = (Pipeline)Activator.CreateInstance(type);
            return instance;
        }

        public void ApplyInstanceConfig(String config)
        {
            try
            {
                using (Stream stream = new MemoryStream(Encoding.Unicode.GetBytes(config)))
                    LoadPerInstanceConfig(pipeline, stream);
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine(String.Format("{0}", e));
                throw;
            }
        }

        #region Implementation

        private static void LoadPerInstanceConfig(Pipeline pipeline, Stream stream)
        {
            try
            {
                MethodInfo method =
                    typeof(Pipeline).GetMethod(
                        "LoadPerInstanceConfig"
                        , BindingFlags.NonPublic | BindingFlags.Instance);
                method.Invoke(pipeline, new object[] { stream });
            }
            catch (System.Exception e)
            {
                System.Diagnostics.Debug.WriteLine(e);
                throw;
            }
        }

        #endregion
    }

    public static class PipelineManager
    {
        #region receive pipeline
        public static ReceivePipelineOutputMessages ExecuteReceivePipeline(Type type, XLANGMessage message)
        {
            string EmptyConfig =
                        @"<Root
                        xmlns:xsd='http://www.w3.org/2001/XMLSchema'
                        xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
                        <Stages />
                    </Root>";

            return ExecuteReceivePipeline(type, EmptyConfig, message);
        }

        public static ReceivePipelineOutputMessages ExecuteReceivePipeline(Type type, String config, XLANGMessage message)
        {
            try
            {
                PipelineInstance pipeline = PipelineInstance.CreateInstance(type);
                pipeline.ApplyInstanceConfig(config);
                return ExecuteReceivePipeline(pipeline, message);
            }
            catch (TargetInvocationException e)
            {
                System.Diagnostics.Debug.WriteLine(e);
                ThrowPipelineException(e);
                throw;
            }
            catch (XLANGPipelineManagerException e)
            {
                throw;
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.Assert(false, String.Format("Unexpected exception: {0}", e));
                System.Diagnostics.Debug.WriteLine(e);
                throw;
            }
        }

        public static ReceivePipelineOutputMessages ExecuteReceivePipeline(PipelineInstance pipeline, XLANGMessage message)
        {
            Microsoft.BizTalk.PipelineOM.Pipeline pipelineOM = pipeline.pipeline;

            try
            {
                MethodInfo method =
                    typeof(XLANGPipelineManager).GetMethod(
                        "ExecutePipeline"
                        , BindingFlags.NonPublic | BindingFlags.Static
                        , null, new Type[] { typeof(ReceivePipeline), typeof(XLANGMessage) }
                        , null);
                return (ReceivePipelineOutputMessages)method.Invoke(null, new object[] { pipelineOM, message });
            }
            catch (TargetInvocationException e)
            {
                System.Diagnostics.Debug.WriteLine(e);
                ThrowPipelineException(e);
                throw;
            }
        }
        #endregion

        #region send pipeline
        public static void ExecuteSendPipeline(Type type, SendPipelineInputMessages pipelineinputmsg, XLANGMessage message)
        {
            string EmptyConfig =
                        @"<Root
                        xmlns:xsd='http://www.w3.org/2001/XMLSchema'
                        xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
                        <Stages />
                    </Root>";

            ExecuteSendPipeline(type, EmptyConfig, pipelineinputmsg, message);
        }

        public static void ExecuteSendPipeline(Type type, String config, SendPipelineInputMessages pipelineinputmsg, XLANGMessage message)
        {
            try
            {
                PipelineInstance pipeline = PipelineInstance.CreateInstance(type);
                pipeline.ApplyInstanceConfig(config);
                ExecuteSendPipeline(pipeline, pipelineinputmsg, message);
            }
            catch (TargetInvocationException e)
            {
                System.Diagnostics.Debug.WriteLine(e);
                ThrowPipelineException(e);
                throw;
            }
            catch (XLANGPipelineManagerException e)
            {
                throw;
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.Assert(false, String.Format("Unexpected exception: {0}", e));
                System.Diagnostics.Debug.WriteLine(e);
                throw;
            }
        }

        public static void ExecuteSendPipeline(PipelineInstance pipeline, SendPipelineInputMessages pipelineinputmsg, XLANGMessage message)
        {
            Microsoft.BizTalk.PipelineOM.Pipeline pipelineOM = pipeline.pipeline;

            try
            {
                MethodInfo method =
                    typeof(XLANGPipelineManager).GetMethod(
                        "ExecutePipeline"
                        , BindingFlags.NonPublic | BindingFlags.Static
                        , null, new Type[] { typeof(SendPipeline), typeof(SendPipelineInputMessages), typeof(XLANGMessage) }
                        , null);
                method.Invoke(null, new object[] { pipelineOM, pipelineinputmsg, message });
            }
            catch (TargetInvocationException e)
            {
                System.Diagnostics.Debug.WriteLine(e);
                ThrowPipelineException(e);
                throw;
            }
        }
       
        #endregion

        #region Implementation

        private static void ThrowPipelineException(Exception innerException)
        {
            ConstructorInfo constructor =
                typeof(XLANGPipelineManagerException).GetConstructor(
                    BindingFlags.NonPublic | BindingFlags.Instance
                    , null
                    , new Type[] { typeof(String), typeof(Exception) }
                    , null);
            throw (XLANGPipelineManagerException)constructor.Invoke(
                    new object[] {
                    innerException != null ? 
                    innerException.InnerException.Message != null? 
                    innerException.InnerException.Message : innerException.Message: 
                    String.Empty, innerException });
        }

        #endregion
    }
    }

Now, here's how to execute a receive pipeline using the custom property settings.
First create a variable of type TP.BTS.GlobalFunctions.PipelineInstance (called ReceivePipeline in my example). Also create a variable PipelineOutputMsg of type Microsoft.XLANGs.Pipeline.ReceivePipelineOutputMessages.
This would be the code to execute the pipeline then:

ReceivePipeline = 
TP.BTS.GlobalFunctions.PipelineInstance.CreateInstance(typeof(TC_FFRECEIVE.TC_FFRECEIVE));

ReceivePipeline.ApplyInstanceConfig(
@"<Root xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
  <Stages>
    <Stage CategoryId='9d0e4103-4cce-4536-83fa-4a5040674ad6'>
      <Components>
        <Component Name='TP.BTS.PIPELINES.CustomXMLRECEIVECOMPONENT'>
          <Properties />
        </Component>
      </Components>
    </Stage>
    <Stage CategoryId='9d0e4105-4cce-4536-83fa-4a5040674ad6'>
      <Components>
        <Component Name='Microsoft.BizTalk.Component.FFDasmComp'>
          <Properties>
            <DocumentSpecName vt='8'>FLATFILE.ORDERS.FF_ORDERS,FLATFILE.ORDERS, Version=1.0.0.0, Culture=neutral, PublicKeyToken=a8e07cee919011d4</DocumentSpecName>
          </Properties>
        </Component>
      </Components>
    </Stage>
  </Stages>
</Root>");



PipelineOutputMsg = TP.BTS.GlobalFunctions.PipelineManager.ExecuteReceivePipeline(ReceivePipeline,MsgICMTxt);

The XML assigned when calling the ApplyInstanceConfig is the same as you can find when exporting the binding file with the Biztalk Admin Console.
It is that simple :).

Calling a send pipeline with custom property settings would be done like this:
Create a variable SendPipelineInput from type Microsoft.XLANGs.Pipeline.SendPipelineInputMessages. I've also used a variable PipelineConfig of type System.String.
Here's the code I've used:

SendPipelineInput.Add(MsgINVOIC_EDI);

PipelineConfig = @"<Root xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
  <Stages>
    <Stage CategoryId='9d0e4101-4cce-4536-83fa-4a5040674ad6'>
      <Components>
        <Component Name='TP.BTS.PIPELINES.CUSTOMSENDEDIPARTYRESOLUTION.CUSTOMSENDEDIPARTYRESOLUTION'>
          <Properties>
            <OverrideRcvprn vt='8'>" + MsgINVOIC_GEN(ORDERS.ORDERSPropertySchema.RCVPRN) + @"</OverrideRcvprn>
            <OverrideRecipient vt='11'>-1</OverrideRecipient>
            <OverrideSndprn vt='8'>" + MsgINVOIC_GEN(ORDERS.ORDERSPropertySchema.SNDPRN) + @"</OverrideSndprn>
          </Properties>
        </Component>
      </Components>
    </Stage>
    <Stage CategoryId='9d0e4107-4cce-4536-83fa-4a5040674ad6'>
      <Components>
        <Component Name='Microsoft.BizTalk.Edi.Pipelines.EdiAssembler'>
          <Properties />
        </Component>
      </Components>
    </Stage>
    <Stage CategoryId='9d0e4108-4cce-4536-83fa-4a5040674ad6'>
      <Components>
        <Component Name='TP.BTS.PIPELINES.CustomXMLSENDCOMPONENT'>
          <Properties />
        </Component>
      </Components>
    </Stage>
  </Stages>
</Root>";

OrdersOutputInterchange = null;

TP.BTS.GlobalFunctions.PipelineManager.ExecuteSendPipeline(typeof
(CUSTOM_EDI_SEND.CUSTOM_EDI_SEND), PipelineConfig, SendPipelineInput,OrdersOutputInterchange);

And that should do the trick.

Hope you enjoyed this post.

Call pipelines from an orchestration

Calling a pipeline from an orchestration is quite easy.

First of all you need to add a reference to the Microsoft.XLANGs.Pipeline.dll and Microsoft.BizTalk.Pipeline.dll assemblies. When this is done you'll be able to execute a receive pipeline as follows:

ReceivePipelineOutput = Microsoft.XLANGs.Pipeline.XLANGPipelineManager.ExecuteReceivePipeline
(typeof(ShemasAndPipelines.ReceiveCustOrdres), OrdersInputInterchange);

To get all messages coming out the pipeline, you'll have to loop over the batch and get all messages like this:

 ReceivePipelineOutput.GetCurrent(OrdersInputMsg);

For the execution of a send pipeline you'll need to create a variable of type Microsoft.XLANGs.Pipeline.SendPipelineInputMessages. then call the method add on this variable which adds message of type Microsoft.XLANGs.BaseType.XLANGMessage.

SendPipelineInputMessages.Add(OrderSummary);

To execute the send pipeline, you'll have call the ExecuteSendPipeline method with the just created SendPipelineInputMessages and the output message as follows:

Microsoft.XLANGs.Pipeline.XLANGPipelineManager.ExecuteSendPipeline(typeof
(ShemasAndPipelines.SendCustOrdersPipeline), SendPipelineInput, OrdersOutputInterchange);

This way of executing pipelines will execute the pipelines using the default settings for all pipeline properties. How to execute pipelines from an orchestration with custom settings for the pipeline properties will be explained in my next blogs.

a more detailed explanation can be found here

Backup BizTalk: Don't make ordinary Full backups

Off course you make backups of your BizTalk databases! You have set the Recovery Model from the databases to Full and every single day your Maintenance Plans or (actually) the SQL Server Agent jobs make Full and Log backups. So in case you need to restore your BizTalk databases, you are safe...
Well, I've got some news for you... You're not so safe...

If you make BizTalk Backups like described above, you could be in deep trouble in case you need to restore. When it comes to making backups from your BizTalk databases, Microsoft supports only one way....

Read this very nice written blog on how to backup Biztalk Databases the right way: Backup BizTalk: Don't make ordinary Full backups

BizTalk Server 2010 Exam : How to prepare?

I'm planning to take the BizTalk Server 2010 Exam (Exam 70-595) this year.

A nice overview on how to prepare for the exam can be found here, by Steef-Jan Wiggers.

Also Saravana Kumar has a nice blog post on how he prepared for the Biztalk exam. You can find the blog post here.

Another option to prepare for the exam is to use the upcoming book '(MCTS): Microsoft BizTalk Server 2010 (70-595) Certification Guide'.
The book will be released in May 2012, it can however be pre-orderd already here.
UPDATE: Unfortunately, Packt has told me that the release of the book is postponed to the second week of June.

I 'll be using these to prepare for the exam later this year.

BizTalk: Filters on Send Ports and Send Port Groups

Each filter defines a subscription. Messages will be picked up from the MessageBox by each Send Port or Send Port group that ha a ;atching filter defined.

Now consider following situation:
We have a send port group with the filter Firstname = "Harry". The send port group has 2 send ports. One send port (SP1) has an own filter defined on it, namely LastName = "Smith". The other send port (SP2) has a filter LastName = "Jane".

What will happen when a message is received and has these promoted properties:

• Firstname = "Harry"
• LastName = "Smith"

In this case, 2 filters apply but the send port SP1 is also defined in the send port group.
Where will the message be routed to?

The answer is that the message will go twice to the send port SP1 AND once through the send port SP2.
Now, why is this the case?

The group has a subscription and any messages coming to the group will go to the send ports in the group, regardless of their filters. The send ports may get messages directly based on their own filters. It is possible that your send port will get the message twice, once from the group filter and once from its own filter.

Biztalk Best Practices

I found a neat blog about best practises for orchestrations in BTS.

Another great addition to this blog can be found here. This is a check list to review BizTalk code.

A third great blog has some tips and tricks on how to program BizTalk code.

Links:
BizTalk Server 2010 - Orchestration Best Practices - TechNet Wiki
My BizTalk Code Review Checklist - Richard Seroter's Architecture Musings
8 Tips And Tricks For Better BizTalk Programming - MSDN Magazine

Dynamic SQL top statement

I needed to have a dynamic query (stored procedure) where I could give the number of rows that has to be returned. I first tried:

declare @rows int
set @rows = 10

SELECT TOP @rows * FROM MyTable

That didn't work!
I got this error when executing the code:
Incorrect syntax near '@rows'.

There are 2 ways to return a certain dynamic recordset.

Solution 1

First of all you could use the ROWCOUNT statement.
This statement stops processing after a certain number of rows have been processed. It works for SELECT, UPDATE and INSERT.

Here's the syntax of how to use the statement:

declare @rows int
set @rows = 10

set rowcount @rows

select 
 * from MyTable

set rowcount 0

Always remember to use SET ROWCOUNT 0 to turn off the row limiter!

Solution 2

Another way to accomplish the dynamic recordset is to make the whole SQL query a variable.

Here's the syntax of how to use the SQL query as a variable:

declare @rows int
set @rows = 10

declare @query nvarchar(MAX)
set @query = 'select top ' + convert(nvarchar, @rows) + 
' * from MyTable'

Execute(@query)

The EXECUTE statement will run any valid SQL statement that you pass it. You can use this to dynamically generate SQL statements at run time. Keep in mind that SQL Server is providing no syntax checks of this statement until it actually runs so be very careful.

Versioning the BizTalk generated MSI files

For each application in Biztalk it's possible to generate a MSI file, which can be used to deploy an application elsewhere.
To create this MSI file we can use the BizTalk admin console.
Right-click the application for which you wish to generate the MSI file and select 'Export' -> 'MSI file...' as shown below.

Now just follow the simple wizard to create the MSI file on disk.

The file that is created will always have version 1.0.0.0 assigned to it. You can overwrite the version number (and other properties) manually with Microsoft Orca, but this is quite cumbersome.
An easier way of doing this can be found in this blog.


Source: http://blog.codit.eu/post/2012/01/23/Versioning-the-BizTalk-generated-MSI-files.aspx

Introduction to Transaction Logs

Introduction

The transaction log and how SQL uses it seems to be one of the most misunderstood topics among newcomers to the DBA role. I’m going to see if I can shed a little light on what the transaction log is, why SQL uses it, how to manage it and what not to do to it.

What is the Transaction Log?

At its simplest, the transaction log is a record of all transactions run against a database and all database modifications made by those transactions. The transaction log is a critical part of the database’s architecture.

The transaction log is not an audit log. It’s not there so that the DBA can see who did what to the database. It’s also not a data recovery tool. There are third-party tools that can get audit or data-recovery info from the log, but that is not its primary purpose.

The transaction log is predominantly used by the SQL engine to ensure database integrity, to allow transaction rollbacks and for database recovery.

How does SQL use the log?

When changes are made to a database, whether it be in an explicit transaction or an auto-committed transaction, those changes are first written (hardened) to the log file and the data pages are changed in memory. Once the record of the changes is in the log, the transaction is considered complete. The data pages will be written to the data file on disk at a later time either by the lazy writer or by the checkpoint process.

If transactions are rolled back, either by an explicit ROLLBACK TRANSACTION, by an error if XACT_ABORT is on, or due to a loss of connection to the client, the transaction log is used to undo the modifications made by that transaction and leave the database as though the transaction had never occurred. In a similar way, the log is used to undo the effects of single statements that fail, whether in an explicit transaction or not.

When a server is restarted, SQL uses the transaction log to see if, at the point the server shut down there were any transactions that had completed but whose changes may not have been written to disk, or any transactions that had not completed. If there are, then the modifications that may not have been written to disk are replayed (rolled forward) and any that had not completed are rolled back. This is done to ensure that the database is in a consistent state after a restart and that any transactions that had committed remain a part of the permanent database state (the Durability requirement of ACID)

Lastly, backups made of the transaction log can be used to recover a database to a point-in-time in case of a failure.

The transaction log is also used to support replication, database mirroring and change data capture. I won’t be going into how they affect the log here.

Recovery models and the Transaction Log

The database recovery model does not (with the exception of bulk operations) affect what is written to the transaction log. Rather it affects how long log entries remain in the log. This is just a very basic look at the recovery models. For more detail, see my article “Recovery Models".

Simple Recovery Model

In the simple recovery model, the transaction log entries are kept only to allow for transaction rollbacks and crash recovery, not for the purpose of restoring a database. Once the data pages have been written to disk and the transaction is complete; then, in the absence of replication or other things that need the log, the log records are considered inactive and can be marked as reusable. This marking of portions of the log as reusable is done by the checkpoint process.

This is the simplest recovery mode in terms of log management as the log manages itself. The downside of simple recovery is that (because transaction log backups cannot be made) a restore of the database can only be done to the time of the latest full or differential database backup. With a busy database, this can result in unacceptable data loss.

Full Recovery model

In full recovery model transaction log entries cannot be overwritten until they have been backed up by a transaction log backup. Simply having the transaction committed and data pages written to disk is not enough

Full recovery can be more difficult to manage as the log can grow beyond what is expected if transaction log backups don’t occur, or if there’s an increase in the amount of database activity that occurs between log backups.

Without any log backups running (an all-too common occurrence seeing that new databases will default to full recovery model unless the recovery model of the Model database has been changed), the transaction log will grow until it reaches its configured maximum file size (2TB unless otherwise specified) or until it fills the disk. No amount of full or differential backups will allow the log space to be reused as neither marks log space as reusable.

What can be even worse is that a database in full recovery model does not behave like this from the moment created. When created, a database in full recovery model will behave in a manner sometimes called pseudo-simple recovery model. This occurs because no database backup has yet been taken, and a database backup is needed to start a log chain. While in pseudo-simple recovery, the database behaves as though it really is in simple recovery model, truncating the log (marking space as reusable) every time a checkpoint occurs. This state remains until the first full backup is taken of the database. That full backup starts the log chain and from that point on the log will no longer be marked reusable by the checkpoint process and, if there are no log backups, the log will begin to grow.

Because log records are not overwritten until they have been backed up, a database in full recovery mode can be recovered to any time using a combination of full, differential and log backups, assuming a starting full backup exists and none of the log backups since have been deleted.

Bulk-logged recovery model

Bulk-logged is very similar to full recovery, except that in bulk-logged, bulk operations are minimally logged. When operations are minimally logged, much less information is written to the transaction log compared to when the operation is fully logged.

The advantage of bulk-logged recovery is that if there are bulk operations occurring, the impact those operations have on the transaction log is less than it would be if the database was in full recovery mode. However the transaction log backups may be much larger than the transaction log itself since the log backups include all data pages modified by bulk operations since the previous log backup.

I'm not going to discuss bulk-logged recovery model further than this in the current article. For the purposes of log management, bulk-logged recovery model can be treated much like full recovery.

Managing transaction logs

Managing your transaction log requires you to think about your recovery model, log backups, and various other details regarding the log files.

Picking a recovery model

The key to effectively managing transaction logs is to know what the availability and recovery requirements are for the database. The choice of recovery model should not be chosen because of performance issues or space concerns.

If there is no requirement for point-in-time recovery and it is acceptable, in the case of a disaster, to restore the database to the last full/differential backup, then simple recovery model can be used. In reality, it’s not that common to have a database where the loss of several hours of data is acceptable, so in general simple recovery model should be limited to development or testing environments or databases that can be completely recreated from a source if they fail.

If there is a requirement for point-in-time recovery and minimal or no data loss in the case of a disaster, then the database should be in full or bulk-logged recovery model. This, along with proper storage and retention for the backup files can allow the database to be restored to the point of failure or near to the point of failure in most situations.

If the database is in full or bulk-logged recovery model then log backups must be done. Without log backups the log entries will never be discarded from the log and the log file will grow until it fills the drive. Since one of the main reasons for having a database in full or bulk-logged recovery model is to allow the database to be restored without data loss in the case of a disaster, it’s important to have an unbroken log chain to allow a restore to the point of failure if such a restore becomes necessary. This means that no log backups can be lost or discarded and the database cannot be switched to simple recovery model.

Frequency of log backups

The frequency that log backups should be done is dependent on two considerations:

The maximum amount of data that can be lost in the case of a disaster The size to which the log can grow.

The first consideration is by far the most important.

When in full recovery model a disaster that destroys or damages the database can be recovered from without data loss, providing the transaction log file is available. If the log file is not available then the best that can be done, assuming that the log backups are stored separately from the database files and are available, is a restore to the last log backup taken, losing all data after that point. From this it should be clear that the interval between log backups should be carefully chosen based on the RPO (recovery point objective) requirements for that database. If the mandate is that no more than 20 minutes of data can be lost, then scheduling log backups hourly is a risk, as a disaster can result in losing up to 60 minutes of data if the transaction log file is lost or damaged.

If (once the log backups have been setup and scheduled based on the database's RPO) the log is growing larger than is acceptable, then the log backup frequency can be increased in order to keep the size down. This is by no means guaranteed to have the desired effect as the log must be large enough to accommodate the largest single transaction run against the database (which is usually an index rebuild).

Log chains

A log chain is an unbroken set of log backups starting with a full or differential backup and reaching to the point where the database needs to be restored. When a log chain is broken, the database cannot be restored to any point past the point at which the log chain was broken until a full or differential backup is taken to restart the log chain.

Switching a database to simple recovery model immediately breaks the log chain and prevents further log backups. Log backups cannot be taken until the database is switched back to full or bulk-logged recovery and a full or differential backup is taken. The database can't be restored to any point between the last log backup prior to the switch to simple recovery and the full or differential backup that re-established the log chain.

Deleting or losing a log backup file breaks the log chain, although SQL doesn't know that the log chain is broken in that case and allows log backups to still be taken. Regardless, if a log backup file is missing the log chain is broken and point-in-time restores can't be done to any point within or after the time range covered by the missing log backup. As with the case of switching to simple recovery model, a full or differential backup must be taken to start a new log chain.

It is important to note that while a full or differential backup starts a log chain, full and differential backups don't break the log chain. If, for example, full backups were taken every 4 hours and transaction log backups hourly and a restore was needed to 5pm, then all of these would be valid, working options

Restore 4pm full backup, 5pm log backup Restore midday full backup, log backups from 1pm-5pm Restore 8am full backup, log backups from 9am-5pm Etc.

Log size

The question of how big a log file should be given a data file of a particular size is a common one, and one almost impossible to easily answer.

There is no single formula that calculates how large a log file should be based on the data file size. The size that the log file needs to be is based on activity (specifically database changes) and, in full and bulk-logged recovery, on the interval between log backups. This is not easy to calculate without performing a load-test of the database with both production-level data volumes and production-level transaction volumes.

At a minimum, the log must be large enough to hold the log records for the largest transaction that will be run against the database. In full recovery that will likely be the index rebuild of the largest clustered index in the database. So, with no other considerations, a reasonable place to start would be around 150% of the size of the largest table in the database with a reasonable growth increment based on the initial size, assuming that the largest table will have its clustered index rebuilt in full recovery model. This may need to be increased based on a number of considerations, including but not limited to

Largest size of the database mirroring send queue Transaction volume during a full or differential backup Latency and transaction volume of transactional replication or CDC.

A large log file will not cause performance problems for queries in the database. It is possible that a very large number of Virtual Log Files will result in slower than expected log backups and other operations that read the log, but that's not a problem of large log, that's a result of growing the log file in small intervals

Number of log files

The answer to the question of how many log files a database should have is a simple one. One log file only. SQL uses log files sequentially, not in any form of parallel or Round-Robin mechanism. Hence, if there are multiple log files, SQL will only ever be writing to one at a time.

The one time where there may be a use for a second log file is when unusual database activity needs a total amount of log space larger than what is available on any particular drive or larger than the 2TB limit in the size of a log file. In these circumstances it may be necessary to create a second log file to increase the amount of available log space. This is purely about available log space, not about performance.

Shrinking the log

In general, the transaction log should not be shrunk. It certainly should never be shrunk on a regular basis in a job or maintenance plan.

The only time a log should be shrunk is if some abnormal database activity (or failed log backups) has resulted in the log growing far beyond the size it needs to be for the database activity. In this situation, the log can be shrunk as a once-off operation, reducing it back to the size that it was before the abnormal activity.

Shrinking the log on a regular basis will have just one effect - the log growing again once regular activity on the database requires the old size. Transaction log grow operations are not fast, they cannot take advantage of instant initialisation and hence the new portion of the log will always have to be zeroed out. The other effect of the log repeatedly growing is that unless the auto-grow setting has been carefully chosen, the growth of the log will result in log fragmentation - excessive VLFs that can degrade the performance of backups, restores, crash recovery, replication and anything else that reads the log.

Don't shrink the log regularly. Shrink only if something has blown the size of the log far beyond what it needs to be.

Log fragmentation and VLFs

Internally, the log is divided into sections called Virtual Log Files (VLF). A log will always contain at least 2 VLFs and will usually contain far more. When the log is truncated (checkpoint in simple recovery or log backup in full recovery), only entire VLFs can be marked reusable. SQL can't mark individual log records or log blocks as reusable. A single log record that's part of an open transaction or otherwise needed prevents the entire VLF from being marked as reusable.

When the log is created or the log grows the specified size results in a specific number of VLFs of specific sizes. More details on the exact algorithm can be found on Kimberly Tripp's blog. If the log was improperly sized initially and auto-grew to a huge size, the log can have vast numbers of VLFs (tens of thousands have been seen in production systems). The problem with excessive VLFs is that it can have a massive impact on operations that read the log. These include, but are not limited to, database and log backups, restores, crash recovery, transactional replication, change data capture.

There's no exact number of VLFs that are good or bad. If there are thousands, it's probably bad. Tens of thousands is definitely very bad. Hundreds? That depends on the size of the log. The number of VLFs should be high enough that SQL doesn't have to keep huge portions of the log active but low enough that reading all the VLF headers doesn't take too long.

The fix for excessive VLFs is to shrink the log to 0 and then regrow it in reasonable chunks to its previous size. This obviously has to be done while the database is idle. The size of the grow increments determines how many VLFs will be in the new file, see Kimberly’s blog post on log throughput for details, as well as the previously mentioned blog post: http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx.

The growth increment must not be an exact multiple of 4GB. There's a bug in SQL if that exact size is used. Any other size works as expected.

Log mismanagement

There's a huge amount of advice on various forums and blogs as to management of logs. Unfortunately much of it is incorrect or outright dangerous. To end this article I'll touch on a couple of particularly bad forms and explain why they're so bad.

Detach the database, delete the log file, and reattach the database.

This one's particularly terrible as it can cause unexpected downtime and potentially could even result in the complete loss of the database. SQL cannot always recreate the log if a database is attached without one. A log can only be recreated if the database was shut down cleanly. That means no uncommitted transactions, sufficient time to perform a checkpoint and sufficient log space to perform a checkpoint.

If the database is detached and the log deleted and SQL could not cleanly shut the database down beforehand, the database will fail to reattach necessitating a restore from backup or hacking the database back in and doing an emergency mode repair. Said repair can fail and if it does with no available backup, the database is essentially lost without hope of recovery.

Set the recovery model to Simple, shrink the log to 0 and then set the recovery model back to full

This one's nasty for two reasons.

The switch to simple recovery breaks the log chain, which means that there is no possibility for point-in-time recovery past this point until another full or differential backup is taken to restart the log chain (something most people recommending this don't mention). Even if a full backup is taken immediately after switching back to full recovery, breaking the log chain reduces the options available for restoring. If a full backup taken after the point the log chain was broken is damaged, a full backup from before can't be used without potentially significant data loss.

The other reason is that shrinking the log to 0 will immediately force it to grow. When the log grows it has to be zero-initialised and hence can slow down all data modifications occurring. It can also result in a huge number of VLFs if the auto grow increments are not properly chosen.

Conclusion

The transaction log is a crucial piece of a database and a good understanding of basic log management is an essential skill for any DBA. In this article I've briefly covered what the log is used for and how the recovery models affect that. I've also touched on how the log should and shouldn’t be managed and some other considerations that exist for dealing with the transaction log of a SQL Server database.


Source: Managing Transaction Logs - SQLServerCentral