Hi friends many times as a BizTalk Administrator we fall into situations when we need to constantly monitor BizTalk Servers resources and services like Host Instances Status, any custom services like IIS, CPU usage, available disk space etc.
So, in this blog I have created a console application which can be scheduled to run continuously using Windows Scheduler. It will constantly monitor all resources and attempt to correct it and later drop an email to configured group or user (only if there is an issue).
Note – Proactive Monitoring ensures early resolutions, thus solving upcoming big future problems.
Currently I have focused on monitoring of below items –
1) Check ENTSSO, IIS And Other Custom Services
2) Enumerate And Start All Host Instances
3) Check If Host is in Hung State – “Start Pending” or “Stop Pending”
4) Check Available Drive Space
5) Check CPU Reading
6) Check All Suspended and Custom Orchestration Suspended instance count
7) Check Spool Count, Parts Count and row count for other tables if it is more than the configured value
8) Check BizTalkMsgBox Table Index Fragmentation
In multi-server BizTalk environment this tool needs to be scheduled in only in one environment and provide all other server names of this group in configuration file – “ServerNames” as shown below. It will remotely query all the servers.
<add key=”ServerNames” value=”BTSServer1;BTSServer2″/>
1) Check ENTSSO IIS And Other CustomServices –
In this function we can monitor important Services like ENTSSO, IIS(World Wide Web Publishing Service – w3svc) or any other custom service. You just need to mention these services in config file as shown below –
<add key=”AdditionalServicesToBeMonitored” value=”ENTSSO;W3SVC;RTI”/>
I have used “ServiceController” class to fetch list of all the services, check its status and finally start the same.
ServiceController[] scServices = ServiceController.GetServices(strServerName);
foreach (ServiceController service in scServices)
{
if ((service.ServiceName.ToUpper().Contains(strServiceName)) && service.Status != ServiceControllerStatus.Running)
{
service.Start();}
}
2) Enumerate And Start Host Instances –
In this function we are enumerating all the In-Process Host Instances using WMI Query and starting them.
Sample code -
//Create EnumerationOptions and run wql query
EnumerationOptions enumOptions = new EnumerationOptions();
enumOptions.ReturnImmediately = false;
//Search for all HostInstances of 'InProcess' type in the Biztalk namespace scope
ManagementObjectSearcher searchObject = new ManagementObjectSearcher("root\\MicrosoftBizTalkServer", "Select * from MSBTS_HostInstance Where HostType=1 And IsDisabled=False", enumOptions);
//Enumerate through the result set and start each HostInstance if it is already stopped
foreach (ManagementObject inst in searchObject.Get())
{
//Check if ServiceState is 'Stopped'
if (inst["ServiceState"].ToString() == "1")
{ inst.InvokeMethod("Start", null); }}
3) Check If Host is in “Start Pending” or “Stop Pending” State
Many times BizTalk Host Instances go in Hung State like “Stop Pending” or “Start Pending” and if you leave these processes for days they won’t come up. So, the only option left is to Terminate(End Process Tree) using Task Manager and start the host again. I totally agree that it’s not the best way as the service will not exit gracefully but in production environment we are left with very little option.
Here also I am using WMI queries to find if the Host is in Hung State i.e. Start or Stop Pending state.
//Search for all HostInstances of 'InProcess' type in the Biztalk namespace scope which is not Disabled and have Service State as 2(Start Pending) or 3(Stop Pending)
ManagementObjectSearcher searchObject = new ManagementObjectSearcher("root\\MicrosoftBizTalkServer", "Select * from MSBTS_HostInstance Where HostType=1 And IsDisabled=False and (ServiceState = 2 or ServiceState = 3)", enumOptions);
After waiting for some configured milliseconds I am performing again a WMI Query to check if the Host Status is changed or not. Because this issue occurs very rarely and mostly “Start Pending” or “Stop Pending” state will last for very less time when you have performed Start or Stop of Host Instances.
//Query again to check if the status is still the same i.e. Stopped/Start Pending
ManagementObjectSearcher searchObject1 = new ManagementObjectSearcher("root\\MicrosoftBizTalkServer", "Select * from MSBTS_HostInstance Where HostName='" + inst["HostName"].ToString() + "' and (ServiceState = 2 or ServiceState = 3)", enumOptions);
Once if it’s confirmed that Host is hung we will go ahead and Terminate it forcibly along with all other processes with same parentID.
var connectionOptions = new ConnectionOptions();
ManagementScope scope = new ManagementScope(@"\\" + strServerName + @"\root\cimv2", connectionOptions);
scope.Connect();
//Get Process ID of the service. BizTalk Host Instance Name is always like BTSSvc$ like BTSSvc$BizTalkServerApplication
var query = new SelectQuery("select ProcessID from Win32_Service where Name ='BTSSvc$" + strHostName + "'");
using (var searcher = new ManagementObjectSearcher(scope, query))
{
foreach (ManagementObject obj in searcher.Get())
{
uint processId = (uint)obj["ProcessId"];
//Hard Terminate(Terminate Related Processes) - Loop through all the processes on the machine and Kill it if it's Parent Process ID or ProcessID matches the Service Process ID
var parentIdQuery = new SelectQuery("Select * from Win32_process");
using (var vsearcher = new ManagementObjectSearcher(scope, parentIdQuery))
{
foreach (ManagementObject process in vsearcher.Get()) {
//Checking if Parent Process ID or ProcessID is same as Service ProcessID
if (processId == (uint)process["ParentProcessID"] || processId == (uint)process["ProcessID"])
{ process.InvokeMethod("Terminate", null); } } } } }
4) Check Available Drive Space –
Here too I am using WMI query to check Available Disk Space. This query will be performed remotely on all the servers.
For remotely connecting WMI Query, namespace will be –
string strNameSpace = @”\\” + strServerName + @”\root\cimv2″;
Query – select FreeSpace,Size,Name from Win32_LogicalDisk where DriveType=3″, enumOptions
string strNameSpace = @"\\" + strServerName + @"\root\cimv2"; //Get Fixed disk state. Check - http://www.csidata.com/custserv/onlinehelp/vbsdocs/vbs41.htm, DriveType = 2 - Fixed, Drive has fixed (nonremovable) media. This includes all hard drives, including hard drives that are removable. DriveType = 2 -Remote, Network drives. This includes drives shared anywhere on a network. ManagementObjectSearcher searchObject = new ManagementObjectSearcher(strNameSpace, "select FreeSpace,Size,Name from Win32_LogicalDisk where DriveType=3", enumOptions);
5) Check CPU Reading –
I am using Performance Counter – % Processor Time. Here we Sleep the thread to achieve average value for configured time.
PerformanceCounter totalProcessorTimeCounter = new PerformanceCounter("Processor", "% Processor Time", "_Total", strServerName);
totalProcessorTimeCounter.NextValue();
System.Threading.Thread.Sleep(intThreadSleepTimeInMilliSeconds);// seconds wait
double CPUPercentage = totalProcessorTimeCounter.NextValue();
if (CPUPercentage > doubleAcceptableCPUPercentage)
6) Check All Suspended and Custom Orchestration Suspended instance count
Here also I am using WMI query to fetch total suspended instances – ServiceClass=1(Orchestration) or ServiceClass=4(Messaging) and (ServiceStatus=4(Suspended (resumable)) or ServiceStatus=32(Suspended (non-resumable)) in the BizTalk namespace
//Search for all Service Instances for ServiceClass=1(Orchestration) or ServiceClass=4(Messaging) and (ServiceStatus=4(Suspended (resumable)) or ServiceStatus=32(Suspended (non-resumable)) in the Biztalk namespace scope
ManagementObjectSearcher searchObject = new ManagementObjectSearcher("root\\MicrosoftBizTalkServer", "Select * from MSBTS_ServiceInstance Where (ServiceClass=1 or ServiceClass=4) and (ServiceStatus=4 or ServiceStatus=32)", enumOptions);
It can be configured to get Suspended Instance for individual Orchestrations as well, which can be configured in app.config file.
WQL Query – string.Format(“SELECT * FROM MSBTS_ServiceInstance WHERE (ServiceClass=1 or ServiceClass=4) and (ServiceStatus=4 or ServiceStatus=32) AND ServiceName like \”%{0}%\””, strOrchName);
//Now checking for individual Orchestrations Only If Suspended Instance Count not zero
int count = 1;
foreach (string strOrchName in strOrchestrationNames.Split(';'))
{
string strWQL = string.Format("SELECT * FROM MSBTS_ServiceInstance WHERE (ServiceClass=1 or ServiceClass=4) and (ServiceStatus=4 or ServiceStatus=32) AND ServiceName like \"%{0}%\"", strOrchName);
7) Spool Count, Parts Count and other tables with Row Count if more than configured value –
Performing SQL Query on BizTalk Message box to get top 10 tables with row count more that the acceptable configured value. This information can be used to proactively prevent throttling and avoid slowness of BizTalk message processing.
//Checking top 10 tables with Row count more than intAcceptableMsgBoxRowCount
string queryString = “SELECT top 10 T.name TableName, I.Rows NumberOfRows FROM sys.tables T JOIN sys.sysindexes I ON T.OBJECT_ID = I.ID “
+ “WHERE indid IN (0,1) and I.Rows > ” + intAcceptableMsgBoxRowCount.ToString() + “ORDER BY I.Rows DESC,T.name”;
8) Check BizTalkMsgBox Table Index Fragmentation –
To check Fragmentation I am using below query. It checks if Fragmentation is more than the configured acceptable value. In case of high Fragmentation you will have to Rebuild Indexes to improve performance. Execute below stored procedures during down time.
1.bts_RebuildIndexes —- for BizTalkMsgBoxDb database
2.dtasp_RebuildIndexes— for BizTalkDTADb database
//Checking Index Fragmentation if the value is more than Acceptable Fragmentation Percentage, Selecting top 10 values
“SELECT top 10 dbtables.[name] as ‘Table’, dbindexes.[name] as ‘Index’, indexstats.avg_fragmentation_in_percent, indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()and avg_fragmentation_in_percent > ” + doubleAcceptableFragmentationPercentage.ToString() + ” “;
ORDER BY indexstats.avg_fragmentation_in_percent desc”;
Complete Solution Code can be found here –
App.config file - <?xml version="1.0" encoding="utf-8" ?> <configuration> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" /> </startup> <appSettings> <!--BizTalk Message Box Connection String--> <add key="BizTalkMsgBoxConnectionString" value="Data Source=WIN-4ACT86KFN44;Initial Catalog=BizTalkMsgBoxDb;Integrated Security=true;Pooling=false"/> <!--SMTP Details for Sending Emails--> <add key="IsEmailRequired" value="true"/> <add key="SMTPHost" value="smtp.gmail.com"/> <add key="FROMEmailID" value="youremailid@gmail.com"/> <add key="TOEmailID" value="toemailid@gmail.com,toemailid2@microsoft.com"/> <add key="SMTPServerPort" value="587"/> <!--<add key="Username" value="youremailid@gmail.com"/> <add key="Password" value="yourpassword"/>--> <add key="Subject" value="Monitoring BizTalk Resources"/> <add key="ServerNames" value="WIN-4ACT86KFN44"/> <add key="AdditionalServicesToBeMonitored" value="ENTSSO;W3SVC;RTI"/> <add key ="AcceptableAvailableDriveFreeSpace" value="80"/> <add key="AcceptableTotalSuspendedInstanceCount" value="1"/> <add key="OrchestrationNames" value="ProcessCandidateAgeIfno;ProcessOrders;DummyOrchName"/> <add key="AcceptableSuspendedInstanceCountForAnOrch" value="1"/> <add key="AcceptableCPUPercentage" value="1"/> <add key="AcceptableMsgBoxRowCount" value="1"/> <add key="AcceptableFragmentationPercentage" value="1"/> <add key ="ThreadSleepTimeInMilliSeconds" value="5000"/> </appSettings> </configuration>
C# Code -
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Management;
using System.Net.Mail;
using System.Configuration;
using System.ServiceProcess;
using System.IO;
using System.Diagnostics;
using System.Data.SqlClient;
namespace MonitoringBizTalkResources
{
class MonitoringBizTalkResources
{
static void Main(string[] args)
{
#region Get Values from Config File
string strEmailBody = "";
var appSettings = ConfigurationManager.AppSettings;
//Get Server Names for checking services
string strServerNames = "";
if (appSettings["ServerNames"] != null && !String.IsNullOrEmpty(appSettings["ServerNames"].ToString()))
{ strServerNames = Convert.ToString(appSettings["ServerNames"].ToString()); }
//Getting Additional Services To Be Monitored
string strAdditionalServicesToBeMonitored = "";
if (appSettings["AdditionalServicesToBeMonitored"] != null && !String.IsNullOrEmpty(appSettings["AdditionalServicesToBeMonitored"].ToString()))
{ strAdditionalServicesToBeMonitored = Convert.ToString(appSettings["AdditionalServicesToBeMonitored"].ToString()); }
//Getting connection string from Config File
string strConnectionString = "";
if (appSettings["BizTalkMsgBoxConnectionString"] != null && !String.IsNullOrEmpty(appSettings["BizTalkMsgBoxConnectionString"].ToString()))
{ strConnectionString = Convert.ToString(appSettings["BizTalkMsgBoxConnectionString"].ToString()); }
//If value of IsEmailRequired field is present in config file will populate the same else will keep it as default = false
bool IsEmailRequired = false;
if (appSettings["IsEmailRequired"] != null && !String.IsNullOrEmpty(appSettings["IsEmailRequired"].ToString()))
{ IsEmailRequired = Convert.ToBoolean(appSettings["IsEmailRequired"].ToString()); }
//Get ThreadSleepTimeInMilliSeconds
int intThreadSleepTimeInMilliSeconds = 5000;
if (appSettings["ThreadSleepTimeInMilliSeconds"] != null && !String.IsNullOrEmpty(appSettings["ThreadSleepTimeInMilliSeconds"].ToString()))
{ intThreadSleepTimeInMilliSeconds = Convert.ToInt32(appSettings["ThreadSleepTimeInMilliSeconds"].ToString()); }
//If value of AcceptableAvailableDriveSpace field is present in config file will populate the same else will keep it as default
double doubleAcceptableFreeDriveSpace = 80;
if (appSettings["AcceptableAvailableDriveFreeSpace"] != null && !String.IsNullOrEmpty(appSettings["AcceptableAvailableDriveFreeSpace"].ToString()))
{ doubleAcceptableFreeDriveSpace = Convert.ToDouble(appSettings["AcceptableAvailableDriveFreeSpace"].ToString()); }
//If value of AcceptableTotalSuspendedInstanceCount field is present in config file will populate the same else will keep it as default
int intAcceptableTotalSuspendedInstanceCount = 10000;
if (appSettings["AcceptableTotalSuspendedInstanceCount"] != null && !String.IsNullOrEmpty(appSettings["AcceptableTotalSuspendedInstanceCount"].ToString()))
{ intAcceptableTotalSuspendedInstanceCount = Convert.ToInt32(appSettings["AcceptableTotalSuspendedInstanceCount"].ToString()); }
//Get Orchestration Names from Config
string strOrchestrationNames = "";
if (appSettings["OrchestrationNames"] != null && !String.IsNullOrEmpty(appSettings["OrchestrationNames"].ToString()))
{ strOrchestrationNames = appSettings["OrchestrationNames"].ToString(); }
//Get AcceptableSuspendedInstanceCountForAnOrch from config
int intAcceptableSuspendedInstanceCountForAnOrch = 100;
if (appSettings["AcceptableSuspendedInstanceCountForAnOrch"] != null && !String.IsNullOrEmpty(appSettings["AcceptableSuspendedInstanceCountForAnOrch"].ToString()))
{ intAcceptableSuspendedInstanceCountForAnOrch = Convert.ToInt32(appSettings["AcceptableSuspendedInstanceCountForAnOrch"].ToString()); }
//Get AcceptableCPUPercentage from Config
double doubleAcceptableCPUPercentage = 80;
if (appSettings["AcceptableCPUPercentage"] != null && !String.IsNullOrEmpty(appSettings["AcceptableCPUPercentage"].ToString()))
{ doubleAcceptableCPUPercentage = Convert.ToDouble(appSettings["AcceptableCPUPercentage"].ToString()); }
//Get AcceptableFragmentationPercentage from Config
double doubleAcceptableFragmentationPercentage = 50;
if (appSettings["AcceptableFragmentationPercentage"] != null && !String.IsNullOrEmpty(appSettings["AcceptableFragmentationPercentage"].ToString()))
{ doubleAcceptableFragmentationPercentage = Convert.ToDouble(appSettings["AcceptableFragmentationPercentage"].ToString()); }
//Get Acceptable MsgBox RowCount for a table from config
int intAcceptableMsgBoxRowCount = 30000;
if (appSettings["AcceptableMsgBoxRowCount"] != null && !String.IsNullOrEmpty(appSettings["AcceptableMsgBoxRowCount"].ToString()))
{ intAcceptableMsgBoxRowCount = Convert.ToInt32(appSettings["AcceptableMsgBoxRowCount"].ToString()); }
#endregion
try
{
//Check if Enterprise Single Sign-On Service or IIS is stopped
checkENTSSOIISAndCustomServices(ref strEmailBody, ref strServerNames, ref strAdditionalServicesToBeMonitored);
//Check all the Host Instances
enumerateAndStartHostInstances(ref strEmailBody);
//Check if Host is in Start Pending or Stop Pending State
checkIfHostStartorStopPendingState(ref strEmailBody, ref intThreadSleepTimeInMilliSeconds);
//Check Drive Free Space
checkAvailableDriveSpace(ref strEmailBody, ref strServerNames, ref doubleAcceptableFreeDriveSpace);
//Check CPU Reading
checkCPUReading(ref strEmailBody, ref strServerNames, ref doubleAcceptableCPUPercentage, ref intThreadSleepTimeInMilliSeconds);
//Check All Suspended Instance Count
checkAllSuspendedInstances(ref strEmailBody, ref intAcceptableTotalSuspendedInstanceCount, ref strOrchestrationNames, ref intAcceptableSuspendedInstanceCountForAnOrch);
//Check Spool Count, Parts Count and other tables with Row Count more than intAcceptableMsgBoxRowCount
checkBizTalkMsgBoxTablesCount(ref strEmailBody, ref strConnectionString, ref intAcceptableMsgBoxRowCount);
//Check BizTalkMsgBox Table Index Fragmentation
checkBizTalkMsgBoxTablesIndexFragmentation(ref strEmailBody, ref strConnectionString, ref doubleAcceptableFragmentationPercentage);
if (IsEmailRequired && !String.IsNullOrEmpty(strEmailBody))
{
string strHTMLStyle = "";
strEmailBody = "<html>"+strHTMLStyle+"<body>" + strEmailBody + "</body></html>";
sendEmail(strEmailBody);
}
}
catch (Exception excep)
{
string strExceptionMessage = "MonitoringBizTalkResource - An exception occurred. " + excep.Message;
if (!String.IsNullOrEmpty(strEmailBody))
strExceptionMessage = strExceptionMessage + Environment.NewLine + "Operations performed so far - " + strEmailBody;
System.Diagnostics.EventLog.WriteEntry("BizTalk Server", strExceptionMessage, System.Diagnostics.EventLogEntryType.Error);
}
}
//Function to check if any Host is in Stop Pending or Start Pending State
private static void checkIfHostStartorStopPendingState(ref string strEmailBody, ref int intThreadSleepTimeInMilliSeconds)
{
try
{
//Create EnumerationOptions and run wql query
EnumerationOptions enumOptions = new EnumerationOptions();
enumOptions.ReturnImmediately = false;
//Search for all HostInstances of 'InProcess' type in the Biztalk namespace scope which is not Disabled and have Service State as 2(Start Pending) or 3(Stop Pending)
ManagementObjectSearcher searchObject = new ManagementObjectSearcher("root\\MicrosoftBizTalkServer", "Select * from MSBTS_HostInstance Where HostType=1 And IsDisabled=False and (ServiceState = 2 or ServiceState = 3)", enumOptions);
//ManagementObjectSearcher searchObject = new ManagementObjectSearcher("root\\MicrosoftBizTalkServer", "Select * from MSBTS_HostInstance Where HostType=1 And IsDisabled=False and (ServiceState = 1 or ServiceState = 4)", enumOptions); // Test Query for Stopped(1) and Running(4) State
int count = 1;
//Enumerate through the result set
foreach (ManagementObject inst in searchObject.Get())
{
if (count == 1) // Wait only for the first time
{
System.Threading.Thread.Sleep(intThreadSleepTimeInMilliSeconds);// seconds wait
}
//Query again to check if the status is still the same i.e. Stopped/Start Pending
ManagementObjectSearcher searchObject1 = new ManagementObjectSearcher("root\\MicrosoftBizTalkServer", "Select * from MSBTS_HostInstance Where HostName='" + inst["HostName"].ToString() + "' and (ServiceState = 2 or ServiceState = 3)", enumOptions);
//ManagementObjectSearcher searchObject1 = new ManagementObjectSearcher("root\\MicrosoftBizTalkServer", "Select * from MSBTS_HostInstance Where HostName='" + inst["HostName"].ToString() + "' and (ServiceState = 1 or ServiceState = 4)", enumOptions); //Test Query for Stopped(1) and Running(4) State
foreach (ManagementObject inst1 in searchObject1.Get())
{
//Terminate the Host Instance
terminateHungProcess(inst1["RunningServer"].ToString(), inst1["HostName"].ToString());
//Start the Host Instance
inst1.InvokeMethod("Start", null);
//Draft Email Subject
if (count == 1)
{
strEmailBody = strEmailBody + "Below Host Instances were in Stopped/Start Pending state, Terminated the process and attempted to Start<br>";
strEmailBody = strEmailBody + "<table><tr><th>Server Name</th><th>Host Instance Name</th></tr>";
++count;
}
strEmailBody = strEmailBody + "" + inst1["RunningServer"] + "" + inst1["HostName"] + "";
}
}
if (count > 1)
{ strEmailBody = strEmailBody + "</table>All above mentioned Host Instances have been Started Successfully<br><br>"; }
}
catch (Exception excep)
{
System.Diagnostics.EventLog.WriteEntry("BizTalk Server", "Exception Occurred in enumerateAndStartHostInstances fuction call. " + excep.Message, System.Diagnostics.EventLogEntryType.Error);
throw new Exception("Exception Occurred in enumerateAndStartHostInstances fuction call. " + excep.Message);
}
}
//Function to Terminate a Process in Hung State and also terminate all related processes(Having same ParentPID)
private static void terminateHungProcess(string strServerName, string strHostName)
{
try
{
var connectionOptions = new ConnectionOptions();
ManagementScope scope = new ManagementScope(@"\\" + strServerName + @"\root\cimv2", connectionOptions);
scope.Connect();
//Get Process ID of the service. BizTalk Host Instance Name is always like BTSSvc
lt;HostName> like BTSSvc$BizTalkServerApplication var query = new SelectQuery("select ProcessID from Win32_Service where Name ='BTSSvc$" + strHostName + "'"); using (var searcher = new ManagementObjectSearcher(scope, query)) { foreach (ManagementObject obj in searcher.Get()) { uint processId = (uint)obj["ProcessId"]; //Hard Terminate(Terminate Related Processes) - Loop through all the processes on the machine and Kill it if it's Parent Process ID or ProcessID matches the Service Process ID var parentIdQuery = new SelectQuery("Select * from Win32_process"); using (var vsearcher = new ManagementObjectSearcher(scope, parentIdQuery)) { foreach (ManagementObject process in vsearcher.Get()) { //Checking if Parent Process ID or ProcessID is same as Service ProcessID if (processId == (uint)process["ParentProcessID"] || processId == (uint)process["ProcessID"]) { process.InvokeMethod("Terminate", null); } } } } } } catch (Exception excep) { System.Diagnostics.EventLog.WriteEntry("BizTalk Server", "Exception Occurred in terminateHungProcess fuction call. " + excep.Message, System.Diagnostics.EventLogEntryType.Error); throw new Exception("Exception Occurred in terminateHungProcess fuction call. " + excep.Message); } } //Function to check and start ENTSSO Service private static void checkENTSSOIISAndCustomServices(ref string strEmailBody, ref string strServerNames, ref string strAdditionalServicesToBeMonitored) { try { int count = 1; foreach (string strServerName in strServerNames.Split(';')) { ServiceController[] scServices = ServiceController.GetServices(strServerName); foreach (string strServiceName in strAdditionalServicesToBeMonitored.Split(';')) { foreach (ServiceController service in scServices) { if ((service.ServiceName.ToUpper().Contains(strServiceName)) && service.Status != ServiceControllerStatus.Running) { if (count == 1) { strEmailBody = strEmailBody + "Below Services are not running, attempting to start.<br>"; strEmailBody = strEmailBody + "<table><tr><th>Server Name</th><th>Service Name</th></tr>"; ++count; } strEmailBody = strEmailBody + "" + strServerName + "" + service.DisplayName + ""; service.Start(); } } } } if (count > 1) { strEmailBody = strEmailBody + "</table>All above mentioned Services have been Started Successfully<br><br>"; } } catch (Exception excep) { System.Diagnostics.EventLog.WriteEntry("BizTalk Server", "Exception Occurred in checkENTSSOIISAndCustomServices fuction call. " + excep.Message, System.Diagnostics.EventLogEntryType.Error); throw new Exception("Exception Occurred in checkENTSSOServiceAndIIS fuction call. " + excep.Message); } } //Function to Enumerate all HostInstances of "InProcess" type and start them private static void enumerateAndStartHostInstances(ref string strEmailBody) { try { //Create EnumerationOptions and run wql query EnumerationOptions enumOptions = new EnumerationOptions(); enumOptions.ReturnImmediately = false; //Search for all HostInstances of 'InProcess' type in the Biztalk namespace scope ManagementObjectSearcher searchObject = new ManagementObjectSearcher("root\\MicrosoftBizTalkServer", "Select * from MSBTS_HostInstance Where HostType=1 And IsDisabled=False", enumOptions); int count = 1; //Enumerate through the result set and start each HostInstance if it is already stopped foreach (ManagementObject inst in searchObject.Get()) { //Check if ServiceState is 'Stopped' if (inst["ServiceState"].ToString() == "1") { if (count == 1) { strEmailBody = strEmailBody + "Below Host Instances are not running, attempting to start.<br>"; strEmailBody = strEmailBody + "<table><tr><th>Server Name</th><th>Host Instance Name</th></tr>"; ++count; } strEmailBody = strEmailBody + "" + inst["RunningServer"] + "" + inst["HostName"] + ""; inst.InvokeMethod("Start", null); } } if (count > 1) { strEmailBody = strEmailBody + "</table>All above mentioned Host Instances have been Started Successfully<br><br>"; } } catch (Exception excep) { System.Diagnostics.EventLog.WriteEntry("BizTalk Server", "Exception Occurred in enumerateAndStartHostInstances fuction call. " + excep.Message, System.Diagnostics.EventLogEntryType.Error); throw new Exception("Exception Occurred in enumerateAndStartHostInstances fuction call. " + excep.Message); } } //Function to check available drive space private static void checkAvailableDriveSpace(ref string strEmailBody, ref string strServerNames, ref double doubleAcceptableFreeDriveSpace) { try { int count = 1; foreach (string strServerName in strServerNames.Split(';')) { //Connection credentials to the remote computer, not needed if the logged account has access ConnectionOptions oConn = new ConnectionOptions(); //oConn.Username = "DummyUserName";//oConn.Password = "*********"; //Create EnumerationOptions and run wql query EnumerationOptions enumOptions = new EnumerationOptions(); enumOptions.ReturnImmediately = false; string strNameSpace = @"\\" + strServerName + @"\root\cimv2"; //Get Fixed disk state. Check - http://www.csidata.com/custserv/onlinehelp/vbsdocs/vbs41.htm, DriveType = 2 - Fixed, Drive has fixed (nonremovable) media. This includes all hard drives, including hard drives that are removable. DriveType = 2 -Remote, Network drives. This includes drives shared anywhere on a network. ManagementObjectSearcher searchObject = new ManagementObjectSearcher(strNameSpace, "select FreeSpace,Size,Name from Win32_LogicalDisk where DriveType=3", enumOptions); //Enumerate through the result set foreach (ManagementObject driveInfo in searchObject.Get()) { double percentFreeSpace = 100 * (System.Convert.ToDouble(driveInfo["FreeSpace"]) / System.Convert.ToDouble(driveInfo["Size"])); if (percentFreeSpace < doubleAcceptableFreeDriveSpace) { if (count == 1) { strEmailBody = strEmailBody + "Available Free Space for below drives is less than acceptable value of " + doubleAcceptableFreeDriveSpace.ToString() + "% "; strEmailBody = strEmailBody + "<table><tr><th>Server Name</th><th>Drive Name</th><th>Total Size</th><th>Available Free Space</th><th>Percentage Free Space</th></tr>"; ++count; } strEmailBody = strEmailBody + "" + strServerName + "" + driveInfo["Name"] + "" + driveInfo["Size"] + "" + driveInfo["FreeSpace"] + "" + percentFreeSpace + "%"; } } } if (count > 1) { strEmailBody = strEmailBody + "</table><br><br>"; } } catch (Exception excep) { System.Diagnostics.EventLog.WriteEntry("BizTalk Server", "Exception Occurred in checkAvailableDriveSpace fuction call. " + excep.Message, System.Diagnostics.EventLogEntryType.Error); throw new Exception("Exception Occurred in checkAvailableDriveSpace fuction call. " + excep.Message); } } //Function to check CPU Reading private static void checkCPUReading(ref string strEmailBody, ref string strServerNames, ref double doubleAcceptableCPUPercentage, ref int intThreadSleepTimeInMilliSeconds) { try { int count = 1; foreach (string strServerName in strServerNames.Split(';')) { PerformanceCounter totalProcessorTimeCounter = new PerformanceCounter("Processor", "% Processor Time", "_Total", strServerName); totalProcessorTimeCounter.NextValue(); System.Threading.Thread.Sleep(intThreadSleepTimeInMilliSeconds);// seconds wait double CPUPercentage = totalProcessorTimeCounter.NextValue(); if (CPUPercentage > doubleAcceptableCPUPercentage) { if (count == 1) { strEmailBody = strEmailBody + "CPU Usage on below servers is higher than the acceptable value of " + doubleAcceptableCPUPercentage.ToString() + "% "; strEmailBody = strEmailBody + "<table><tr><th>Server Name</th><th>CPU Percentage</th></tr>"; ++count; } strEmailBody = strEmailBody + "" + strServerName + "" + CPUPercentage.ToString() + "%"; } } if (count > 1) { strEmailBody = strEmailBody + "</table><br><br>"; } } catch (Exception excep) { System.Diagnostics.EventLog.WriteEntry("BizTalk Server", "Error Occurred in checkCPUReading function call. " + excep.Message, System.Diagnostics.EventLogEntryType.Error); throw new Exception("Error Occurred in checkCPUReading function call. " + excep.Message); } } //Function to Check Suspended Instances private static void checkAllSuspendedInstances(ref string strEmailBody, ref int intAcceptableTotalSuspendedInstanceCount, ref string strOrchestrationNames, ref int intAcceptableSuspendedInstanceCountForAnOrch) { try { //Create EnumerationOptions and run wql query EnumerationOptions enumOptions = new EnumerationOptions(); enumOptions.ReturnImmediately = false; //Search for all Service Instances for ServiceClass=1(Orchestration) or ServiceClass=4(Messaging) and (ServiceStatus=4(Suspended (resumable)) or ServiceStatus=32(Suspended (non-resumable)) in the Biztalk namespace scope ManagementObjectSearcher searchObject = new ManagementObjectSearcher("root\\MicrosoftBizTalkServer", "Select * from MSBTS_ServiceInstance Where (ServiceClass=1 or ServiceClass=4) and (ServiceStatus=4 or ServiceStatus=32)", enumOptions); if (searchObject.Get().Count > 0) { if (intAcceptableTotalSuspendedInstanceCount < searchObject.Get().Count) { strEmailBody = strEmailBody + "The total number of Suspended instances is higher than the acceptable value of " + intAcceptableTotalSuspendedInstanceCount.ToString() + " "; strEmailBody = strEmailBody + "<table><tr><th>Total Number of Suspended Instance</th></tr>"; strEmailBody = strEmailBody + "" + searchObject.Get().Count.ToString() + ""; strEmailBody = strEmailBody + "</table><br><br>"; } //Now checking for individual Orchestrations Only If Suspended Instance Count not zero int count = 1; foreach (string strOrchName in strOrchestrationNames.Split(';')) { string strWQL = string.Format("SELECT * FROM MSBTS_ServiceInstance WHERE (ServiceClass=1 or ServiceClass=4) and (ServiceStatus=4 or ServiceStatus=32) AND ServiceName like \"%{0}%\"", strOrchName); ManagementObjectSearcher searcherServiceInstance = new ManagementObjectSearcher(new ManagementScope("root\\MicrosoftBizTalkServer"), new WqlObjectQuery(strWQL), null); if (searcherServiceInstance.Get().Count > intAcceptableSuspendedInstanceCountForAnOrch) { if (count == 1) { strEmailBody = strEmailBody + "The total number of Suspended instances for below Orchestrations is higher than the acceptable value of " + intAcceptableSuspendedInstanceCountForAnOrch.ToString() + " "; strEmailBody = strEmailBody + "<table><tr><th>Orchestration Name</th><th>Number of Suspended Instances</th></tr>"; ++count; } strEmailBody = strEmailBody + "" + strOrchName + "" + searcherServiceInstance.Get().Count.ToString() + ""; } } if (count > 1) { strEmailBody = strEmailBody + "</table><br><br>"; } } } catch (Exception excep) { System.Diagnostics.EventLog.WriteEntry("BizTalk Server", "Error Occurred in checkAllSuspendedInstances function call. " + excep.Message, System.Diagnostics.EventLogEntryType.Error); throw new Exception("Error Occurred in checkAllSuspendedInstances function call. " + excep.Message); } } //Function to check Fragmentation of BizTalkMsgBoxDb Table Indexes private static void checkBizTalkMsgBoxTablesIndexFragmentation(ref string strEmailBody, ref string strConnectionString, ref double doubleAcceptableFragmentationPercentage) { try { if (!String.IsNullOrEmpty(strConnectionString)) { //Checking Index Fragmentation if the value is more than Acceptable Fragmentation Percentage, Selecting top 10 values string queryString = "SELECT top 10 dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count "; queryString = queryString + "FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id "; queryString = queryString + "WHERE indexstats.database_id = DB_ID()and avg_fragmentation_in_percent > " + doubleAcceptableFragmentationPercentage.ToString() + " "; queryString = queryString + "ORDER BY indexstats.avg_fragmentation_in_percent desc"; using (SqlConnection connection = new SqlConnection(strConnectionString)) { using (SqlCommand command = new SqlCommand(queryString, connection)) { connection.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { strEmailBody = strEmailBody + "Fragmentation Percentage for few indexes is more than the Acceptable Value of " + doubleAcceptableFragmentationPercentage.ToString() + "% <br>"; strEmailBody = strEmailBody + "<table><tr><th>Table Name</th><th>Index Name</th><th>Average Fragmentation in Percent</th><th>Page Count</th></tr>"; while (reader.Read()) { strEmailBody = strEmailBody + "<tr><td>" + reader["Table"] + "</td><td>" + reader["Index"] + "</td><td>" + reader["avg_fragmentation_in_percent"] + "</td><td>" + reader["page_count"] + "</td></tr>"; } strEmailBody = strEmailBody + "</table>" + Environment.NewLine + "<br><br>"; } } } } else throw new Exception("BizTalk Msg Box Connection String property is NULL"); } catch (Exception excep) { System.Diagnostics.EventLog.WriteEntry("BizTalk Server", "Exception Occurred in checkBizTalkMsgBoxTablesIndexFragmentation fuction call. " + excep.Message, System.Diagnostics.EventLogEntryType.Error); throw new Exception("Exception Occurred in checkBizTalkMsgBoxTablesIndexFragmentation fuction call. " + excep.Message); } } //Function to check Spool and Parts table Count and other tables with row count more than intAcceptableMsgBoxRowCount private static void checkBizTalkMsgBoxTablesCount(ref string strEmailBody, ref string strConnectionString, ref int intAcceptableMsgBoxRowCount) { try { int count = 1; if (!String.IsNullOrEmpty(strConnectionString)) { //Checking top 10 tables with Row count more than intAcceptableMsgBoxRowCount string queryString = "SELECT top 10 T.name TableName, I.Rows NumberOfRows FROM sys.tables T JOIN sys.sysindexes I ON T.OBJECT_ID = I.ID " + "WHERE indid IN (0,1) and I.Rows > " + intAcceptableMsgBoxRowCount.ToString() + "ORDER BY I.Rows DESC,T.name"; using (SqlConnection connection = new SqlConnection(strConnectionString)) { using (SqlCommand command = new SqlCommand(queryString, connection)) { connection.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { if (count == 1) { strEmailBody = strEmailBody + "The Row Count of Below Tables is more than the acceptable value of " + intAcceptableMsgBoxRowCount + " "; strEmailBody = strEmailBody + "<table><tr><th>Table Name</th><th>Current Row Count(Exceeded Value)</th></tr>"; ++count; } strEmailBody = strEmailBody + "<tr><td>" + reader["TableName"].ToString() + "</td><td>" + reader["NumberOfRows"] + "</td></tr>"; } if (count > 1) strEmailBody = strEmailBody + "</table><br><br>"; } } } } else throw new Exception("BizTalk Msg Box Connection String property is NULL"); } catch (Exception excep) { System.Diagnostics.EventLog.WriteEntry("BizTalk Server", "Exception Occurred in checkBizTalkMsgBoxTablesCount fuction call. " + excep.Message, System.Diagnostics.EventLogEntryType.Error); throw new Exception("Exception Occurred in checkBizTalkMsgBoxTablesCount fuction call. " + excep.Message); } } //Functoin to send email private static void sendEmail(string strEmailBody) { //Get SMTP details from config var appSettings = ConfigurationManager.AppSettings; try { //if ((appSettings["SMTPHost"] != null) && (appSettings["FROMEmailID"] != null) && (appSettings["TOEmailID"] != null) && (appSettings["Subject"] != null) && (appSettings["SMTPServerPort"] != null) && (appSettings["Username"] != null) && (appSettings["Password"] != null)) //{ MailMessage mail = new MailMessage(); SmtpClient SmtpServer = new SmtpClient(appSettings["SMTPHost"].ToString()); mail.From = new MailAddress(appSettings["FROMEmailID"].ToString()); mail.To.Add(appSettings["TOEmailID"].ToString()); mail.Subject = appSettings["Subject"].ToString(); mail.Body = strEmailBody; mail.IsBodyHtml = true; SmtpServer.EnableSsl = true; //SmtpServer.DeliveryMethod = SmtpDeliveryMethod.Network; //SmtpServer.UseDefaultCredentials = false; SmtpServer.Port = System.Convert.ToInt32(appSettings["SMTPServerPort"]); //SmtpServer.Credentials = new System.Net.NetworkCredential(appSettings["Username"].ToString(), appSettings["Password"].ToString()); SmtpServer.Send(mail); System.Diagnostics.EventLog.WriteEntry("BizTalk Server", "Email Sent", System.Diagnostics.EventLogEntryType.SuccessAudit); //} //else //{ // System.Diagnostics.EventLog.WriteEntry("BizTalk Server", "Error occurred while sending email - Some mandatory configuration values is missing. Please check if corresponding values are given in config file- SMTPHost, FROMEmailID, ToEmailID, Subject, SMTPServerPort, Username, Password"); // throw new Exception("Error occurred while sending email - Some mandatory configuration values is missing. Please check if corresponding values are given in config file- SMTPHost, FROMEmailID, ToEmailID, Subject, SMTPServerPort, Username, Password"); //} } catch (Exception expMailSend) { System.Diagnostics.EventLog.WriteEntry("BizTalk Server", expMailSend.Message, System.Diagnostics.EventLogEntryType.Error); throw new Exception("Error occurred while sending email - " + expMailSend.Message); } } } }
Sample Email –
Download the complete code from here
I will keep on adding new functionality so make sure you donwload the latest version of code.
Contact Me:-
@Gmail, @Facebook , @Twitter, @LinkedIn , @MSDNTechnet, @My Personal Blog
Check out my other blogs –
Logic Apps Secure SQL Connection String in Azure Key Vault
While using SQL Connector in Logic Apps we don’t get a straightforward way to fetch/store connection string in Key Vault or in config. By default, the connection string we provide goes and sits in the Configurations as shown in the pics below and is in plaintext format. The regular process to use SQL Connector -…
Save Full File in SQL table Column (varbinary) and read the contents back in t
What if you needed to save full file as-is to a SQL Server table column. I ran into a similar scenario and had to save CSV Files in a table. For this we created a table with a column of type “varbinary” CREATE TABLE [dbo].[TableWithCompleteFiles]([SNo] [int] IDENTITY(1,1) NOT NULL,[FileName] varchar NULL,[FileContent] varbinary NULL) ON [PRIMARY]…
Export SQL Table content as CSV File to a File Location
Recently, I got a request to save the contents of the table to a File. Wroking Command – Above commands will fail with below error – Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 43]SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off…
Prashant BizTalk And Azure Integration Blogs 