Biztalk server 2006 performance and get support from Microsft

Biztalk server 2006 performance and get support from Microsft

How to maintain and troubleshoot BizTalk Server databases

View products that this article applies to.

ON THIS PAGE

Expand all | Collapse all

SUMMARY

Microsoft BizTalk Server databases and the health of the databases are very important for a successful BizTalk Server messaging environment. This article discusses important things to consider when you work with BizTalk Server databases. These considerations include the following:

• You must disable the Auto Update Statistics and Auto Create Statistics Microsoft SQL Server options.
• You must set the Max Degree of Parallelism property correctly.
• Determine when you can rebuild BizTalk Server indexes.
• Locking, deadlocking, or blocking may occur.
• You may experience issues with large databases or tables.
• BizTalk SQL Server Agent jobs
• Service instances may be suspended.
• You may experience SQL Server and BizTalk Server performance issues.
• You should follow best practices in BizTalk Server.

Back to the top

INTRODUCTION

This article describes how to maintain BizTalk Server databases and how to troubleshoot BizTalk Server database issues.

Back to the top

MORE INFORMATION

Known issues

You must disable the Auto Update Statistics and Auto Create Statistics options

You must disable the Auto Create Statistics and Auto Update Statistics options on the BizTalkMsgBoxDb database. To determine whether these settings are disabled, execute the following stored procedures in SQL Server:

exec sp_dboption 'BizTalkMsgBoxDB', 'auto create statistics'
exec sp_dboption 'BizTalkMsgBoxDB', 'auto update statistics'

You should set the CurrentSetting setting to "off." If this setting is set to "on," turn it off by executing the following stored procedures in SQL Server:

exec sp_dboption 'BizTalkMsgBoxDB', 'auto create statistics', 'off'
exec sp_dboption 'BizTalkMsgBoxDB', 'auto update statistics', 'off'

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

917845  (http://kbalertz.com/Feedback.aspx?kbNumber=917845/ ) You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server 2006 or in BizTalk Server 2004

912262  (http://kbalertz.com/Feedback.aspx?kbNumber=912262/ ) The auto update statistics option, the auto create statistics option, and the Parallelism setting are turned off in the SQL Server 2000 or SQL Server 2005 database instance that hosts the BizTalk Server 2004 or BizTalk Server 2006 BizTalkMsgBoxDB database

You must set the Max Degree of Parallelism property correctly

On the computer that is running SQL Server and hosting the BizTalkMsgBoxDb database, set the Max Degree of Parallelism run_value and config_value properties to a value of 1. To determine the Max Degree of Parallelism setting, execute the following stored procedure against the Master database in SQL Server:

exec sp_configure 'max degree of parallelism'

If the run_value and config_value properties are not set to a value of 1, execute the following stored procedure in SQL Server:

exec sp_configure 'max degree of parallelism', '1'
reconfigure with override

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

899000  (http://kbalertz.com/Feedback.aspx?kbNumber=899000/ ) The Parallelism setting for the instance of SQL Server when you configure BizTalk Server

917845  (http://kbalertz.com/Feedback.aspx?kbNumber=917845/ ) You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server 2006 or in BizTalk Server 2004

Determine when you can rebuild BizTalk Server indexes

Most BizTalk Server indexes are clustered (index ID: 1). You can use the DBCC SHOWCONTIG statement to display fragmentation information for the BizTalk Server tables.

The BizTalk Server indexes are GUID-based. Therefore, fragmentation typically occurs. If the Scan Density value that is returned by the DBCC SHOWCONTIG statement is less than 30 percent, the BizTalk Server indexes can be rebuilt during downtime.

Many BizTalk Server tables contain columns that use DataType definitions. Online indexing cannot be performed in these columns. Therefore, you should never rebuild the BizTalk Server indexes while BizTalk Server processes data.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

917845  (http://kbalertz.com/Feedback.aspx?kbNumber=917845/ ) You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server 2006 or in BizTalk Server 2004

For more information about how to analyze the DBCC SHOWCONTIG statement output, visit the following Microsoft Web site:

http://technet.microsoft.com/en-us/library/cc966523.aspx (http://technet.microsoft.com/en-us/library/cc966523.aspx)

Locking, deadlocking, or blocking may occur

Typically, locks and blocks occur in a BizTalk Server environment. However, these locks or blocks do not remain for an extended time. Therefore, blocking and deadlocking indicate a potential problem.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

917845  (http://kbalertz.com/Feedback.aspx?kbNumber=917845/ ) You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server 2006 or in BizTalk Server 2004

You may experience issues with large databases or tables

We have seen that when the BizTalkMsgBoxDb database is larger than 5GB, problems can occur. Ideally, the BizTalkMsgBoxDb database should not be holding any data. The BizTalkMsgBoxDb database should be considered a buffer until the data is processed or moved to the BizTalkDTADb database.

An environment that uses a powerful SQL Server at the back end and many long-running orchestrations may have a BizTalkMsgBoxDb database that is larger than 5 GB.

A high-volume environment that uses no long-running orchestrations should have a BizTalkMsgBoxDb database that is much smaller than 5 GB.

The BizTalkDTADb database does not have a set size. However, if query performance decreases, the database is probably too large. Typically, 15 GB to 20 GB is considered too large. When you have large BizTalk Server databases, you may experience the following issues:

• The BizTalkMsgBoxDb database continues to grow. However, both the log file and the data size remain large.
• BizTalk Server takes a longer time than usual to process even a simple message flow scenario.
• Health and Activity Tracking (HAT) queries take a longer time than usual and may time out.
• The database log file is never truncated.
• The BizTalk SQL Server Agent jobs run slower than usual.
• Some tables are significantly larger or have too many rows compared to the usual table size.

Databases can become large for various reasons. These reasons may include the following:

• BizTalk SQL Server Agent jobs are not running
• Large number of suspended instances
• Disk failures
• Tracking
• Throttling
• SQL Server performance
• Network latency

Make sure that you know what is expected in your environment to determine whether a data issue is occurring.

By default, tracking is enabled on the default host. BizTalk requires that the Allow Host Tracking option be checked on a single HOST. When tracking is enabled, the Tracking Data Decode Service (TDDS) moves the tracking event data from the BizTalkMsgBoxDb database to the BizTalkDTADb database. If the tracking host is stopped, TDDS does not move the data to the BizTalkDTADb database and the TrackingData_x_x tables in the BizTalkMsgBoxDb database will grow.

We recommend that you dedicate one host to tracking. To allow for TDDS to maintain new tracking events in high-volume scenarios, you can create multiple instances of a single tracking host. No more than one tracking host should exist.

You can have too many rows in a table. There is no set number of rows that are too many. Additionally, this number of rows varies by what kind of data is stored in the table. For example, a dta_DebugTrace table that has more than 1 million rows probably has too many rows. A HostNameQ_Suspended table that has more than 200,000 rows probably has too many rows.

Use the correct BizTalk SQL Server Agent jobs

The BizTalk SQL Server Agent jobs are important for managing the BizTalk Server databases and for maintaining high performance.

The Backup BizTalk Server SQL Server Agent job is the only supported method to back up the BizTalk Server databases. This job requires you to set up all BizTalk Server databases to use a Full Recovery Model. You should configure this job for a healthy BizTalk Server environment. You can use the SQL Server methods to back up the BizTalk Server databases only if the SQL Server service is stopped and if all BizTalk Server processes are stopped.

The MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent job runs infinitely. Therefore, the SQL Server Agent job history never displays a successful completion. If a failure occurs, the job restarts within one minute and continues to run infinitely. Therefore, you can safely ignore the failure. Additionally, you can clear the job history. You should only be concerned if the job history reports that this job constantly fails and restarts.

The MessageBox_Message_Cleanup_BizTalkMsgBoxDb SQL Server Agent job is the only BizTalk Server job that you should not enable because it is started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job.

The DTA Purge and Archive SQL Server Agent job helps maintain the BizTalkDTADb database by purging and archiving tracked messages. This job reads every row in the table and compares the time stamp to determine whether the record should be removed.

All BizTalk SQL Server Agent jobs except the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent job should be running successfully.

For a description of all the BizTalk Server SQL Server Agent jobs, visit the following Microsoft Software Developer Network (MSDN) Web site:

http://msdn.microsoft.com/en-us/library/aa561960.aspx (http://msdn.microsoft.com/en-us/library/aa561960.aspx)

For more information about all the BizTalk Server 2004 SQL Server Agent jobs, click the following article number to view the article in the Microsoft Knowledge Base:

919776  (http://kbalertz.com/Feedback.aspx?kbNumber=919776/ ) Description of the SQL Server Agent jobs in BizTalk Server 2004

Service instances may be suspended

Service instances can be suspended (resumable) or suspended (not resumable). These service instances may be Messaging, Orchestration, or Port.

Both kinds of service instances can make the BizTalkMsgBoxDb database grow unnecessarily.These instances can be terminated. The following table lists what method can be used, depending on the BizTalk version:

Collapse this tableExpand this table

	Group Hub	HAT	Terminate.vbs
BizTalk Server 2009	Yes	No	Yes
BizTalk Server 2006 R2	Yes	Yes	Yes
BizTalk Server 2006	Yes	Yes	Yes
BizTalk Server 2004	No	Yes	Yes

For more information about the Terminate.vbs script, visit the following MSDN Web site:

http://msdn.microsoft.com/en-us/library/bb203857.aspx (http://msdn.microsoft.com/en-us/library/bb203857.aspx)

Caching instances do not appear in the Group Hub page, and you cannot suspend or terminate caching instances. This restriction is a common cause of table growth. To prevent new zombie messages for the cache service instances in BizTalk Server 2006, apply the hotfix that is described in Microsoft Knowledge Base article 936536. This issue is fixed in BizTalk Server 2006 R2 and later.

Note A zombie message is a message that was routed but not consumed.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

936536  (http://kbalertz.com/Feedback.aspx?kbNumber=936536/ ) FIX: You experience performance issues with BizTalk Server 2006 and throttling messages are logged in the performance log file

When a BizTalk Server host instance terminates, caching instances may not be removed. To resolve this behavior in BizTalk Server 2006 and in BizTalk Server 2006 R2, apply the hotfix that is described in Microsoft Knowledge Base article 944426. This issue is fixed in BizTalk Server 2009.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

944426  (http://kbalertz.com/Feedback.aspx?kbNumber=944426/ ) FIX: Orphaned cache instances may be built in the Instances table of the BizTalkMsgBoxDb database in BizTalk Server 2006

Back to the top

Another common issue is that Routing Failure Reports (RFRs) may build up in the BizTalkHostQ and BizTalkHostQ_Suspended tables. The RFRs are not removed, and this behavior may cause the BizTalkMsgBoxDb database to grow. To address this issue in BizTalk Server 2006, apply the hotfix that is described in Microsoft Knowledge Base article 941690. This issue is fixed in BizTalk Server 2006 R2 and later.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

941690  (http://kbalertz.com/Feedback.aspx?kbNumber=941690/ ) FIX: Routing failure reports are not removed from the Q_Suspended table on a BizTalk Server 2006 server

Back to the top

The terms "orphan messages" and "zombie messages" are frequently used interchangeably.

An orphan message is a message that does not have an associated instance. For example, a routing failure report is an orphan message.

A zombie message is a message that was routed but not consumed. For example, a message was delivered to a convoy orchestration. However, the convoy orchestration went down another code path. The orchestration instance finishes. The message is discarded and is now known as a zombie message.

For a description of zombie messages, visit the following MSDN Web site:

http://blogs.msdn.com/biztalk_core_engine/archive/2004/06/30/169430.aspx (http://blogs.msdn.com/biztalk_core_engine/archive/2004/06/30/169430.aspx)

You may experience SQL Server and BizTalk Server performance issues

BizTalk Server makes hundreds of short, very quick transactions to SQL Server within one minute. If the SQL Server cannot sustain this activity, you may experience BizTalk Server performance issues. Monitor theAvg. Disk sec/Read, Avg. Disk sec/Transfer and Avg. Disk sec/Write Performance Monitor counters in the PhysicalDisk performance object. The optimal value is less than 10 ms (milliseconds). A value of 20 ms or larger is considered poor performance

For more information about SQL Server performance, visit the following Microsoft Web site:

http://technet.microsoft.com/en-us/library/cc966540.aspx (http://technet.microsoft.com/en-us/library/cc966540.aspx)

For more information about BizTalk Server 2004 database high availability, visit the following MSDN Web site:

http://msdn.microsoft.com/en-us/library/ms942187.aspx (http://msdn.microsoft.com/en-us/library/ms942187.aspx)

For more information about BizTalk Server 2006 database high availability, visit the following MSDN Web site:

http://msdn.microsoft.com/en-us/library/aa559920.aspx (http://msdn.microsoft.com/en-us/library/aa559920.aspx)

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

298475  (http://kbalertz.com/Feedback.aspx?kbNumber=298475/ ) How to troubleshoot SQL Server performance issues

271509  (http://kbalertz.com/Feedback.aspx?kbNumber=271509/ ) How to monitor blocking in SQL Server 2005 and in SQL Server 2000

Best practices in BizTalk Server

You should start the SQL Server Agent on the SQL Server. When the SQL Server Agent is stopped, the built-in BizTalk SQL Server Agent jobs that are responsible for database maintenance cannot run. This behavior causes database growth, and this growth may cause performance issues. BizTalk Server database maintenance has greatly improved in versions of BizTalk Server that are later than BizTalk Server 2004 Service Pack 2 (SP2).

Put the SQL Server LDF and MDF files on separate drives. When the LDF and MDF files for the BizTalkMsgBoxDb and BizTalkDTADb databases are on the same drive, you may experience disk contention.

If you do not benefit from message body tracking, do not enable this feature. Frequently, you may want to enable message body tracking while you develop and troubleshoot a solution. If you do this, make sure that you disable message body tracking when you are finished. If you enable message body tracking, the BizTalk Server databases grow. If you have a business need that requires you to enable message body tracking, confirm that the TrackedMessages_Copy_BizTalkMsgBoxDb and DTA Purge and Archive SQL Server Agent jobs are running successfully.

Typically, smaller transaction logs cause better performance. To keep the transaction logs smaller, configure the Backup BizTalk Server SQL Server Agent job to run more frequently. For more information, visit the following MSDN Web site:

http://msdn.microsoft.com/en-us/library/bb743398.aspx (http://msdn.microsoft.com/en-us/library/bb743398.aspx)

You can use the BizTalk Server 2006 Best Practices Analyzer (BPA) to evaluate an existing BizTalk Server deployment. The BPA performs numerous database-related checks. For more information about the BPA, visit the following Microsoft Web site:

http://www.microsoft.com/downloads/details.aspx?FamilyID=93d432fe-1370-4b6d-aaa8-a0c43c30f5ab&displaylang=en (http://www.microsoft.com/downloads/details.aspx?FamilyID=93d432fe-1370-4b6d-aaa8-a0c43c30f5ab&displaylang=en)

Back to the top

Troubleshooting

The best troubleshooting steps for the BizTalk Server SQL Server databases depend on the kind of database issue, such as blocking or deadlocking. To troubleshoot a BizTalk Server database issue, follow these steps.

Step 1: Enable and run all required BizTalk SQL Server Agent jobs

All the BizTalk SQL Server Agent jobs except the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job should be enabled and running successfully. Do not disable any other job.

If a failure occurs, use the View History option in SQL Server to view the error information, and then troubleshoot the failure accordingly. Remember that the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent job runs infinitely. Therefore, you should only be concerned if the job history reports that the job constantly fails and restarts.

Step 2: Use the MsgBoxViewer tool

Collect MsgBoxViewer data while you reproduce an issue.

The MsgBoxViewer tool is useful for troubleshooting because it provides an HTML report that has detailed information about table sizes and the row count. The report can also help determine whether BizTalk Server is throttling. Additionally, the tool provides a snapshot of the BizTalk Server databases and the BizTalk Server configuration.

When you use the MsgBoxViewer tool, make sure that you click to select all queries on the Optional Queries tab for a complete analysis.

For more information about how to download the MsgBoxViewer tool, visit the following Microsoft Web site:

http://blogs.technet.com/jpierauc/pages/msgboxviewer.aspx (http://blogs.technet.com/jpierauc/pages/msgboxviewer.aspx)

For more information about throttling in BizTalk Server, visit the following MSDN Web site:

http://msdn.microsoft.com/en-us/library/aa559893.aspx (http://msdn.microsoft.com/en-us/library/aa559893.aspx)

When BizTalk Server is running slower than usual, run the MsgBoxViewer tool, click to select all queries on the Optional Queries tab, and then review the generated HTML report for any problems. The Summary Report section lists warnings in yellow and potential problems in red.

Additionally, you can use the MsgBoxViewer tool output to determine which tables are the largest and have the most records. The following table lists the BizTalk Server tables that typically grow the largest. You can use this data to determine where a potential problem may exist.

Collapse this tableExpand this table

Table	Description
HostNameQ_Suspended	This table contains a reference to messages in the Spool table that are associated with suspended instances for the particular host. This table is in the BizTalkMsgBoxDb database.
HostNameQ	This table contains a reference to messages in the Spool table that are associated with the particular host and are not suspended. This table is in the BizTalkMsgBoxDb database.
Spool Parts Fragments	These tables store actual message data in the BizTalkMsgBoxDb database.
Instances	This table stores all instances and their current status in the BizTalkMsgBoxDb database.
TrackingData_x_x	This table stores the tracked events in the BizTalkMsgBoxDb database for TDDS to move the events to the BizTalkDTADb database.
Tracking_Fragmentsx Tracking_Partsx Tracking_Spoolx	Two of each of these tables are in the BizTalkMsgBoxDb and BizTalkDTADb databases. One is online, and the other is offline. In BizTalk Server 2004 SP2 and in later versions, the TrackedMessages_Copy_BizTalkMsgBoxDb SQL Server Agent job moves tracked message bodies directly to these tables in the BizTalkDTADb database. In BizTalk Server 2004 Service Pack 1 (SP1) and in earlier versions of BizTalk Server, the TrackedMessages_Copy_BizTalkMsgBoxDb SQL Server Agent job copies tracked message bodies into these tables in the BizTalkMsgBoxDb database.The TrackingSpool_Cleanup_BizTalkMsgBoxDb SQL Server Agent job purges the offline tables and makes the tables online while the job also takes the online tables offline.
dta_ServiceInstances	This table stores tracked events for service instances in the BizTalkDTADb database. If this table is large, the BizTalkDTADb database is probably large.
dta_DebugTrace	This table stores the Orchestration debugger events in the BizTalkDTADb database.
dta_MessageInOutEvents	This table stores tracked event messages in the BizTalkDTADb database. These tracked event messages include message context information.
dta_ServiceInstanceExceptions	This table stores error information for any suspended service instance in the BizTalkDTADb database.

Consider the following scenarios.

HostNameQ_Suspended tables

If the HostNameQ_Suspended tables have many records, the tables could be valid suspended instances that appear in the Group Hub page or in HAT. You can terminate these instances. If these instances do not appear in the Group Hub page or in HAT, the instances are probably caching instances or orphaned routing failure reports. When you terminate suspended instances, you clean up the items in this table and their associated rows in the Spool and Instances tables.

HostNameQ tables

If the HostNameQ tables have many of records, the following kinds of instances may exist:

• Ready-to-run instances
• Active instances
• Dehydrated instances

BizTalk Server needs time to "catch up" and process the instances. This table can grow when the incoming rate of processing outpaces the outgoing rate of processing. This scenario can occur when another problem occurs, such as a large BizTalkDTADb database or SQL Server disk delays.

Spool, Parts, and Fragments tables

If the Spool, Parts, and Fragments tables have many records, many messages are currently active, dehydrated, or suspended. Depending on the size, the number of parts, and the fragmentation settings in these tables, a single message may spawn all these tables. Each message has exactly one row in the Spool table and at least one row in the Parts table.

Instances table

The BizTalk Administrator should not allow for many suspended instances to remain in the Instances table. Many dehydrated instances should only remain if the business logic requires long-running orchestrations. Remember that one service instance can be associated with many messages in the Spool table.

TrackingData_x_x tables

If the TrackingData_x_x tables are large, the Tracking host (TDDS) is not running or is not running successfully. If the tracking host instance is running, review the event logs and the TDDS_FailedTrackingData table in the BizTalkDTADb database for error information.

Tracking_Spool1 or Tracking_Spool2 tables

If the Tracking_Spool1 or Tracking_Spool2 tables become large in BizTalk Server 2004 SP1 and in earlier versions of BizTalk Server, confirm that the TrackingSpool_Cleanup_BizTalkMsgBoxDb SQL Server Agent job is enabled and running.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

907661  (http://kbalertz.com/Feedback.aspx?kbNumber=907661/ ) The Tracking_Spool1 or Tracking_Spool2 tables in the BiztalkMsgBoxDb database become very large in BizTalk Server 2004

In BizTalk Server 2004 SP1 and in earlier versions of BizTalk Server, we recommend that you move the tracked message bodies to the BizTalkDTADb database instead purging the tracked message bodies from the BizTalkMsgBoxDb database.

For more information, visit the following MSDN Web site:

http://msdn.microsoft.com/en-us/library/ms966372.aspx (http://msdn.microsoft.com/en-us/library/ms966372.aspx)

dta_DebugTrace table

If the dta_DebugTrace table has many records, orchestration debugging is being used or was being used. If orchestration debugging is not required for regular operations, disable orchestration debugging. If orchestration debugging is disabled or if a backlog exists in the BizTalkMsgBoxDb database, the dta_DebugTrace table may continue to grow because TDDS continues to move this data into the dta_DebugTrace table.

By default, global tracking is enabled. If global tracking is not necessary, it can be disabled. For more information, visit the following Microsoft Web site:

http://technet.microsoft.com/en-us/library/bb203858.aspx (http://technet.microsoft.com/en-us/library/bb203858.aspx)

If the dta_DebugTrace table and the dta_messageInOutEvents table in the BizTalkTrackingDb database are too large, you can truncate the tables manually after you stop the tracking host. In BizTalk 2004, the dtav_FindMessageFacts view in the BizTalkDTADb database prevents the dta_messageInOutEvents table from truncating. To work around this behavior, follow these steps:

1. Stop the tracking host and the DTA Purge and Archive job.
2. If you want to truncate the dta_messageInOutEvents table, save and then delete the dtav_FindMessageFacts view. To do this, follow these steps:
   1. In SQL Server, access the dtav_FindMessageFacts view in the BizTalkDTADb database.
   2. Right-click the dtav_FindMessageFacts view, click All Tasks, and then click Generate SQL Script. When the Generate SQL Scripts dialog box opens, make no changes, and then click OK.
   3. Name the file dtav_FindMessageFacts.sql, and then click Save.
   4. Right-click the dtav_FindMessageFacts view, and then click Delete. Click Drop All.

You can now truncate the table or the tables. If you truncate the dta_messageInOutEvents table, you must also truncate the dta_url table. The dta_url table only exists in BizTalk Server 2004.

When you are finished, follow these steps to re-create the dtav_FindMessageFacts view:

1. Open a new query in SQL Server.
2. In the Available Databases list, select the BizTalkDTADb database.
3. Execute your saved dtav_FindMessageFacts.sql script. This will re-create the view in the BizTalkDTADb database.

Restart the tracking host and the DTA Purge and Archive job.

Back to the top

For more information about tracking database sizing guidelines, visit the following MSDN Web site:

http://msdn.microsoft.com/en-us/library/aa559162.aspx (http://msdn.microsoft.com/en-us/library/aa559162.aspx)

dta_ServiceInstanceExceptions table

The dta_ServiceInstanceExceptions table typically becomes large in an environment that regularly has suspended instances.

Step 3: Investigate deadlock scenarios

In a deadlock scenario, enable DBCC tracing on the SQL Server so that the deadlock information is written to the SQLERROR log.

In SQL Server 2005 and in later versions, execute the following statement:

DBCC TRACEON (1222,-1)

In SQL Server 2000, execute the following statement:

DBCC TRACEON (1204)

Additionally, use the PSSDiag utility to collect data on the Lock:Deadlock event and theLock:Deadlock Chain event.

The BizTalkMsgBoxDB database is a high-volume and high-transaction Online Transaction Processing (OLTP) database. Some deadlocking is expected, and this deadlocking is handled internally by the BizTalk Server engine. When this behavior occurs, no errors are listed in the error logs. When you investigate a deadlock scenario, the deadlock that you are investigating in the output must be correlated with a deadlock error in the event logs.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

830232  (http://kbalertz.com/Feedback.aspx?kbNumber=830232/ ) PSSDIAG data collection utility

Back to the top

Step 4: Look for blocked processes

You can use Activity Monitor in SQL Server to obtain the server process identifier (SPID) of a locking system process. Then, run SQL Profiler to determine the SQL statement that is executing in the locking SPID.

To troubleshoot a locking and blocking issue in SQL Server , use the PSSDiag utility to capture all the Transact-SQL events that have the blocking script enabled.

In SQL Server 2005 and in later versions, you can specify the blocked process threshold setting to determine which SPID or SPIDs are blocking longer than the threshold that you specify.

For more information about the PSSDiag utility, click the following article number to view the article in the Microsoft Knowledge Base:

830232  (http://kbalertz.com/Feedback.aspx?kbNumber=830232/ ) PSSDIAG data collection utility

For more information about the blocked process threshold, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/ms181150.aspx (http://msdn2.microsoft.com/en-us/library/ms181150.aspx)

Note When you experience a locking or blocking issue in SQL Server, we recommend that you contact Microsoft Customer Support Services. Microsoft Customer Support Services can help you configure the correct PSSDiag utility options.

Step 5: Install BizTalk Server 2004 SP2

BizTalk Server 2004 SP1 has no built-in purging and archiving functionality for the BizTalkDTADb database. This functionality is included with BizTalk Server 2004 SP2. Depending on the size of the BizTalkDTADb database, installing BizTalk Server 2004 SP2 may take hours because the Setup program purges the BizTalkDTADb database.

For more information about known issues that may occur when you install BizTalk Server 2004 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:

940519  (http://kbalertz.com/Feedback.aspx?kbNumber=940519/ ) Known issues in BizTalk Server 2004 Service Pack 2 that are not documented in the ReadmeS.htm file

Back to the top

When you install BizTalk Server 2004 SP2, we recommend that you follow these steps:

1. Download and apply the hotfix that is described in Microsoft Knowledge Base article 894253. Follow the steps in this Knowledge Base article to execute the Bts_tracking_shrinkexistingdatabase.sql script in SQL Server 2000.
   
   For more information, click the following article number to view the article in the Microsoft Knowledge Base:
   894253  (http://kbalertz.com/Feedback.aspx?kbNumber=894253/ ) FIX: The dtasp_PruneTrackingdatabase() stored procedure may take many hours to clean up the DTA database in BizTalk Server 2004
2. Install BizTalk Server 2004 SP2.
   
   For more information, click the following article number to view the article in the Microsoft Knowledge Base:
   888751  (http://kbalertz.com/Feedback.aspx?kbNumber=888751/ ) How to obtain the latest BizTalk Server 2004 service pack

Back to the top

Delete all the data

If the databases are too large and if the preferred method is to delete all data, you can delete the data.

Caution Do not use this method in any environment where the data is business critical or if the data is needed.

BizTalkMsgBoxDb database purging steps

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

924715  (http://kbalertz.com/Feedback.aspx?kbNumber=924715/ ) FIX: Message data is not deleted from the tracking database after you run the bts_CleanupMsgbox stored procedure in a BizTalk Server 2006 test environment

To delete all data from the BizTalkMsgBoxDb database , follow these steps:

Note This action deletes all messages. Do not follow these steps in a production environment.

1. Back up all BizTalk Server databases.
2. Copy the Msgbox_cleanup_logic.sql script from Drive:\Program Files\Microsoft BizTalk 200x\schema to the SQL Server.
3. Execute this SQL script against the BizTalkMsgBoxDb database to update the bts_CleanupMsgbox stored procedure.
4. Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
5. Execute the bts_CleanupMsgbox stored procedure on all the BizTalkMsgBoxDb databases.
6. Restart all hosts and BizTalk Server services.

BizTalkDTADb database purging options

To delete all data from the BizTalkDTADb database , you can use one of the following methods:

Note Both methods delete all messages.

• Method 1:
  1. Back up all BizTalk Server databases.
  2. Execute the dtasp_PurgeAllCompletedTrackingData stored procedure. For more information about the dtasp_PurgeAllCompletedTrackingData stored procedure, visit the following MSDN Web site:
     http://msdn.microsoft.com/en-us/library/aa561918.aspx (http://msdn.microsoft.com/en-us/library/aa561918.aspx)
     Note This action deletes all completed messages.
• Method 2:
  1. Back up all BizTalk databases.
  2. Execute the dtasp_CleanHMData stored procedure. Only use this option if the BizTalkDTADb database contains many incomplete instances that must be removed.
     
     To do this, follow these steps:
     1. Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
     2. Execute the dtasp_CleanHMData stored procedure on the BizTalkDTADb database.
     3. Restart all hosts and BizTalk Server services.

BizTalk Server 2004-only steps

To delete all data from the BizTalkDTADb database in BizTalk Server 2004, follow these steps:

Note This action deletes all completed messages.

1. Back up all BizTalk Server databases.
2. Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
3. Download and apply the hotfix that is described in Microsoft Knowledge Base article 894253. Follow the steps in this Knowledge Base article to run the Bts_tracking_shrinkexistingdatabase.sql script in SQL Server 2000.
   
   For more information, click the following article number to view the article in the Microsoft Knowledge Base:
   894253  (http://kbalertz.com/Feedback.aspx?kbNumber=894253/ ) FIX: The dtasp_PruneTrackingdatabase() stored procedure may take many hours to clean up the DTA database in BizTalk Server 2004
4. Restart all hosts and BizTalk services.

Note If you must have the tracking data, back up the BizTalkDTADb database, restore the database to another SQL Server, and then purge the original BizTalkDTADb database.

If you want help analyzing the MsgBoxViewer data or PSSDiag output, contact Microsoft Customer Support Services. For a complete list of Customer Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support (http://support.microsoft.com/contactus/?ws=support)

Note Before you contact Customer Support Services, compress the MsgBoxViewer data, the PSSDiag output, and the updated event logs (.evt files). You may have to send these files to a BizTalk Server support engineer.