Thursday, November 29, 2012
stsadm restore Results in Access Denied 0x80070005
Restoring a content database using stsadm -o restore usually works pretty well. We use this command to restore content backups from our production environment to our testing environment.
If the production and testing environment is administered by a different user accounts then when you try to restore the site u get the error
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
The 12 Hive log file offers up the full stack trace but reveals nothing of any real interest. even if you have all privilages in the SP database and system administrator privilages you wont be able to restore unless given permission as site collection administrator
So from the Central Administration console and browse to the Application Management tab. Select the Site collection administrators link in the SharePoint Site Management section and the Secondary Site Collection Administrator.
check for the lock in the site by using this command before restore
stsadm -o getsitelock -url [url] if not none set it to none by using this command
stsadm -o setsitelock -url [url] -lock none
Tuesday, November 20, 2012
ManageRefCountLog Job Fails
BizTalk 2006 – ManageRefCountLog Job Fails in beta 2
If you have installed BizTalk 2006 with SQL Server 2005 you may have noticed that the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job is failing as shown in the job history capture below.
The detail of the error is as follows:
Executed as user: SomeUser. SQLServerAgent Error: Request to run job MessageBox_Message_Cleanup_BizTalkMsgBoxDb (from User SomeUser) refused because the job is already running from a request by User SomeUser. [SQLSTATE 42000] (Error 22022). The step failed.
There is nothing to be concerned about however. The error stems from this job attempting to start a secondary sql agent job. If that job is already running we get an error. This job is important so do not disable it.
Update: Microsoft have posted a more detailed description on one of their blogs here
The detail of the error is as follows:
Executed as user: SomeUser. SQLServerAgent Error: Request to run job MessageBox_Message_Cleanup_BizTalkMsgBoxDb (from User SomeUser) refused because the job is already running from a request by User SomeUser. [SQLSTATE 42000] (Error 22022). The step failed.
There is nothing to be concerned about however. The error stems from this job attempting to start a secondary sql agent job. If that job is already running we get an error. This job is important so do not disable it.
Update: Microsoft have posted a more detailed description on one of their blogs here
maintain and troubleshoot BizTalk Server databases
How to maintain and troubleshoot BizTalk Server databases
Article ID: 952555 - View products that this article applies to.
On This Page
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.
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 numbers to view the articles in the Microsoft Knowledge Base:
917845 You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server
912262 The auto update statistics option, the auto create statistics option, and the Parallelism setting are turned off in the SQL Server database instance that hosts the BizTalk Server 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'
exec sp_configure 'max degree of parallelism', '1'
reconfigure with override
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
899000 The Parallelism setting for the instance of SQL Server when you configure BizTalk Server
917845 You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server
Determine when you can rebuild BizTalk Server indexes
Most BizTalk Server indexes are clustered (index ID: 1). You can use the DBCC SHOWCONTIG SQL Server 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 You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server
For more information about how to analyze the DBCC SHOWCONTIG statement output, visit the following Microsoft website:
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 You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server
You may experience issues with large databases or tables
We have seen that when the BizTalkMsgBoxDb database is larger than 5GB, performance 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 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.
- BizTalk SQL Server Agent jobs are not running
- Large number of suspended instances
- Disk failures
- Tracking
- Throttling
- SQL Server performance
- Network latency
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, create multiple instances of a single tracking host. No more than one tracking host should exist.
There can be 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 all BizTalk Server databases use a Full Recovery Model. You should configure this job for a healthy BizTalk Server environment. The SQL Server methods can be used to back up the BizTalk Server databases only if SQL Server Agent is stopped and if all BizTalk Server host instances 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, the job history can be cleared. 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 should not be enabled because it is started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent 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 more information about all the BizTalk Server SQL Server Agent jobs, click the following article number to view the article in the Microsoft Knowledge Base:
919776 Description of the SQL Server Agent jobs in BizTalk Server
Service instances may be suspended
Service instances can be suspended (resumable) or suspended (not resumable). These service instances may be Messaging, Orchestration, or Port.These service instances can make the BizTalkMsgBoxDb database grow unnecessarily and can be terminated. The following table lists what method can be used, depending on the BizTalk version:
| Group Hub | HAT | Terminate.vbs | Terminator Tool | |
| BizTalk Server 2010 | Yes | No | Yes | Yes |
| BizTalk Server 2009 | Yes | No | Yes | Yes |
| BizTalk Server 2006 R2 | Yes | Yes | Yes | Yes |
| BizTalk Server 2006 | Yes | Yes | Yes | Yes |
| BizTalk Server 2004 | No | Yes | Yes | Yes |
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 Mirosoft Knowledge Base:
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, install the hotfix in Microsoft Knowledge Base article 944426. In BizTalk Server 2006 R2, install BizTalk 2006 R2 Service Pack 1. This issue is fixed in BizTalk Server 2009 and later versions.For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
974563 List of Microsoft BizTalk Server hotfixes that are included in BizTalk Server 2006 R2 Service Pack 1
944426 FIX: Orphaned cache instances may be built in the Instances and Hosts Queue tables of the BizTalkMsgBoxDb database in BizTalk Server 2006 and in BizTalk Server 2006 R2
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, install the hotfix in Microsoft Knowledge Base article 941690. This issue is fixed in BizTalk Server 2006 R2 and later versions.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
941690 FIX: Routing failure reports are not removed from the <BizTalkHostName>Q_Suspended table on a BizTalk Server 2006 server
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 website:
For more information about SQL Server performance, visit the following Microsoft website: For more information about BizTalk Server 2004 database high availability, visit the following MSDN website: For more information about BizTalk Server 2006 database high availability, visit the following MSDN website: For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
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, disk contention can occur.
If you do not benefit from message body tracking, do not enable this feature. However, it is a good idea 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. When message body tracking is enabled, the BizTalk Server databases grow. If there is a business need that requires enabling 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 about BizTalk Server optimization, visit the following MSDN website: The sp_ForceFullBackup stored procedure in the BizTalkMgmtDb database can also be used to help perform an ad-hoc full backup of the data and log files. The stored procedure updates the adm_ForceFullBackup table with a value 1. The next time theBackup BizTalk Server job runs, a full database backup set is created.
The BizTalk Server Best Practices Analyzer (BPA) can be used to evaluate an existing BizTalk Server deployment. The BPA performs numerous database-related checks. For more information about the BPA, visit the following Microsoft website:
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.
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.
For more information about how to download the MsgBoxViewer tool, visit the following Microsoft website: For more information about throttling in BizTalk Server, visit the following MSDN website: When BizTalk Server is running slower than usual, run the MsgBoxViewer tool, and then review the generated HTML report for any problems. The Summary 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.
Consider the following scenarios.
In this scenario, handle the suspended instances by resuming them or terminating them. The BizTalk Terminator tool can also be used.
If there is a large gap between the sequence numbers in the BizTalkMsgBoxDb TrackingData_x_x tables and the BAMPrimaryImport or BizTalkDTADb TDDS_StreamStatus tables, then TDDS may not move the data from the BizTalkMsgBoxDb database. To correct this, use the BizTalk Terminator tool to purge these tables and reset the sequence number.
On BizTalk Server 2006 R2, install BizTalk 2006 R2 Service Pack 1 to address a known issue with the tracking data. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
For more information about a Database Maintenance SDK sample, visit the following MSDN website:
The dta_MessageInOutEvents table is populated when Message send and receive is enabled on orchestrations and/or pipelines. If these tracking events are not needed, clear the check box for this option in the orchestration and/or pipeline properties.
If these trace events are disabled or if a backlog exists in the BizTalkMsgBoxDb database, these tables may continue to grow because TDDS continues to move this data into these tables.
By default, global tracking is enabled. If global tracking is not necessary, it can be disabled. For more information, visit the following Microsoft website: If the dta_DebugTrace table and/or the dta_messageInOutEvents table in the BizTalkDTADb database are too large, you can truncate the tables manually after you stop the tracking host. The BizTalk Terminator tool also provides this functionality.
In BizTalk Server 2004, the dtav_FindMessageFacts view in the BizTalkDTADb database prevents the dta_MessageInOutEvents table from truncating. To work around this behavior, follow these steps:
When you are finished, follow these steps to re-create the dtav_FindMessageFacts view:
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 website:
You may experience SQL Server and BizTalk Server performance issues
BizTalk Server makes hundreds of short, very quick transactions to SQL Server within a minute. If the SQL Server cannot sustain this activity, BizTalk Server may experience performance issues. In Performance Monitor, monitor the Avg. 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 website: For more information about BizTalk Server 2004 database high availability, visit the following MSDN website: For more information about BizTalk Server 2006 database high availability, visit the following MSDN website: For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
298475 How to troubleshoot SQL Server performance issues
271509 How to monitor blocking in SQL Server 2005 and in SQL Server 2000
271509 How to monitor blocking in SQL Server 2005 and in SQL Server 2000
Best Practices in BizTalk Server
Start 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 BizTalk Server 2004 Service Pack 2 (SP2) and later versions.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, disk contention can occur.
If you do not benefit from message body tracking, do not enable this feature. However, it is a good idea 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. When message body tracking is enabled, the BizTalk Server databases grow. If there is a business need that requires enabling 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 about BizTalk Server optimization, visit the following MSDN website: The sp_ForceFullBackup stored procedure in the BizTalkMgmtDb database can also be used to help perform an ad-hoc full backup of the data and log files. The stored procedure updates the adm_ForceFullBackup table with a value 1. The next time theBackup BizTalk Server job runs, a full database backup set is created.
The BizTalk Server Best Practices Analyzer (BPA) can be used to evaluate an existing BizTalk Server deployment. The BPA performs numerous database-related checks. For more information about the BPA, visit the following Microsoft website:
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.
For more information about how to download the MsgBoxViewer tool, visit the following Microsoft website: For more information about throttling in BizTalk Server, visit the following MSDN website: When BizTalk Server is running slower than usual, run the MsgBoxViewer tool, and then review the generated HTML report for any problems. The Summary 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.
| 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_0_x | These four tables store the Business Activity Monitoring (BAM) tracked events in the BizTalkMsgBoxDb database for TDDS to move the events to the BAMPrimaryImport database. |
| TrackingData_1_x | These four tables store 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 2004, 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. |
HostNameQ_Suspended tables
If the HostNameQ_Suspended tables have many records, the tables could be valid suspended instances that appear in Group Hub or in HAT. These instances can be terminated. If these instances do not appear in Group Hub or in HAT, the instances are probably caching instances or orphaned routing failure reports. When suspended instances are terminated, the items in this table and their associated rows in the Spool and Instances tables are cleaned up.In this scenario, handle the suspended instances by resuming them or terminating them. The BizTalk Terminator tool can also be used.
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
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. 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. If BizTalk is throttling with a state of 6 (large database), these tables can also be truncated by using the BizTalk Terminator tool.If there is a large gap between the sequence numbers in the BizTalkMsgBoxDb TrackingData_x_x tables and the BAMPrimaryImport or BizTalkDTADb TDDS_StreamStatus tables, then TDDS may not move the data from the BizTalkMsgBoxDb database. To correct this, use the BizTalk Terminator tool to purge these tables and reset the sequence number.
On BizTalk Server 2006 R2, install BizTalk 2006 R2 Service Pack 1 to address a known issue with the tracking data. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
969870 FIX: The tracking data is not moved as expected from the BizTalkMsgBoxDb database to the BizTalkDTADb database in BizTalk Server 2006 R2
974563 List of Microsoft BizTalk Server hotfixes that are included in BizTalk Server 2006 R2 Service Pack 1
974563 List of Microsoft BizTalk Server hotfixes that are included in BizTalk Server 2006 R2 Service Pack 1
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 2004, 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 The Tracking_Spool1 or Tracking_Spool2 tables in the BiztalkMsgBoxDb database become very large in BizTalk Server 2004
For more information about a Database Maintenance SDK sample, visit the following MSDN website:
dta_DebugTrace table and dta_MessageInOutEvents
The dta_DebugTrace table is populated when Shape start and end is enabled on an orchestration. If the dta_DebugTrace table has many records, these orchestration debugging events are being used or were being used. If orchestration debugging is not required for regular operations, clear the check box for the Shape start and end option in the orchestration properties.The dta_MessageInOutEvents table is populated when Message send and receive is enabled on orchestrations and/or pipelines. If these tracking events are not needed, clear the check box for this option in the orchestration and/or pipeline properties.
If these trace events are disabled or if a backlog exists in the BizTalkMsgBoxDb database, these tables may continue to grow because TDDS continues to move this data into these tables.
By default, global tracking is enabled. If global tracking is not necessary, it can be disabled. For more information, visit the following Microsoft website: If the dta_DebugTrace table and/or the dta_messageInOutEvents table in the BizTalkDTADb database are too large, you can truncate the tables manually after you stop the tracking host. The BizTalk Terminator tool also provides this functionality.
In BizTalk Server 2004, the dtav_FindMessageFacts view in the BizTalkDTADb database prevents the dta_MessageInOutEvents table from truncating. To work around this behavior, follow these steps:
- Stop the tracking host and the DTA Purge and Archive job.
- If you want to truncate the dta_messageInOutEvents table, save and then delete the dtav_FindMessageFacts view. To do this, follow these steps:
- In SQL Server, access the dtav_FindMessageFacts view in the BizTalkDTADb database.
- Right-click the dtav_FindMessageFacts view, click All Tasks, and then click Generate SQL Script. When theGenerate SQL Scripts dialog box opens, make no changes, and then click OK.
- Name the file dtav_FindMessageFacts.sql, and then click Save.
- Right-click the dtav_FindMessageFacts view, and then click Delete. Click Drop All.
When you are finished, follow these steps to re-create the dtav_FindMessageFacts view:
- Open a new query in SQL Server.
- In the Available Databases list, select the BizTalkDTADb database.
- Execute your saved dtav_FindMessageFacts.sql script. This will re-create the view in the BizTalkDTADb database.
For more information about tracking database sizing guidelines, visit the following MSDN website:
In SQL Server 2005 and later versions, execute the following statement:
In SQL Server 2000, execute the following statement:
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 about PSSDiag for SQL, click the following article number to view the article in the Microsoft Knowledge Base:
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 later versions, execute the following statement:
DBCC TRACEON (1222,-1)
DBCC TRACEON (1204)
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 about PSSDiag for SQL, click the following article number to view the article in the Microsoft Knowledge Base:
830232 PSSDIAG data collection utility
Step 4: Look for blocked processes
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 for SQL utility to capture all the Transact-SQL events that have the blocking script enabled.
In SQL Server 2005 and 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 PSSDiag for SQL, click the following article number to view the article in the Microsoft Knowledge Base:
830232 PSSDIAG data collection utility
For more information about the blocked process threshold, visit the following MSDN website:
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 the Latest BizTalk Server Service Pack and Cumulative Update
BizTalk Server 2006 R2 and later versions have moved to a Cumulative Update (CU) model. The cumulative updates will contain the latest hot fixes. BizTalk Server 2006 R2 Service Pack 1 is also available: 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 information about the known issues when you install BizTalk Server 2004 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
940519 Known issues in BizTalk Server 2004 Service Pack 2 that are not documented in the ReadmeSP2.htm file
When you install BizTalk Server 2004 SP2, we recommend that you follow these steps:
Caution Do not use this method in any environment where the data is business critical or if the data is needed.
Note This action deletes all messages. Be extremely cautious if you follow these steps in a production environment.
Note Both methods delete all messages. Method 2 is faster.
Note This action deletes all completed messages.
If you need help to analyze the MsgBoxViewer data or the 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 website: 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.
- Install the hotfix 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 FIX: The dtasp_PruneTrackingdatabase() stored procedure may take many hours to clean up the DTA database in BizTalk Server 2004
- Install BizTalk Server 2004 SP2. For more information, click the following article number to view the article in the Microsoft Knowledge Base:888751 How to obtain the latest BizTalk Server 2004 service pack
Delete all the data
If the databases are too large or if the preferred method is to delete all data, all the data can be deleted.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
To delete all data in the BizTalkMsgBoxDb database, you can use the BizTalk Terminator tool. Otherwise, follow these steps.Note This action deletes all messages. Be extremely cautious if you follow these steps in a production environment.
- Back up all BizTalk Server databases. Remember, the BizTalkMgmtDb.dbo.sp_ForceFullBackup stored procedure can be used to force a full backup of the data and log files. Execute this stored procedure, and then execute the Backup BizTalk Server SQL Agent job.
- Copy the Msgbox_cleanup_logic.sql script from Drive:\Program Files\Microsoft BizTalk 200x\schema to the SQL Server.
- Execute this SQL script against the BizTalkMsgBoxDb database to update the bts_CleanupMsgbox stored procedure.
- Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
- Execute the bts_CleanupMsgbox stored procedure on all the BizTalkMsgBoxDb databases.
- Restart all host instances and BizTalk Server services.
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
BizTalkDTADb database purging options
To delete all data from the BizTalkDTADb database, you can use the the BizTalk Terminator tool. Otherwise, use one of the following methods.Note Both methods delete all messages. Method 2 is faster.
- Method 1:
- Back up all BizTalk Server databases.
- Execute the dtasp_PurgeAllCompletedTrackingData stored procedure. For more information about the dtasp_PurgeAllCompletedTrackingData stored procedure, visit the following MSDN website: Note This action deletes all completed messages.
- Method 2:
- Back up all BizTalk databases.
- 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:- Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
- Execute the dtasp_CleanHMData stored procedure on the BizTalkDTADb database.
- 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.
- Back up all BizTalk Server databases.
- Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
- Install the hotfix 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 information about the dtasp_PruneTrackingdatabase stored procedure, click the following article number to view the article in the Microsoft Knowledge Base:894253 FIX: The dtasp_PruneTrackingdatabase() stored procedure may take many hours to clean up the DTA database in BizTalk Server 2004 - Restart all hosts and BizTalk services.
If you need help to analyze the MsgBoxViewer data or the 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 website: 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.
SQL Server Agent jobs in BizTalk Server
Description of the SQL Server Agent jobs in BizTalk Server
Article ID: 919776 - View products that this article applies to.
This article lists the Microsoft BizTalk Server SQL Server Agent jobs. This article also describes the SQL Server Agent jobs.
MORE INFORMATION
The following table lists the BizTalk Server SQL Server Agent jobs.
For a list of the SQL Server Agent jobs and their descriptions on BizTalk Server, visit the following Microsoft Developer Network (MSDN) Web site:
Note The BizTalk SQL Server Agent jobs must be owned by a user who has a System Administrator server role on SQL Server.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
| Job name | Description | Enabled by default |
|---|---|---|
| Backup BizTalk Server | Consists of three steps. Step 1 performs full database backups of the BizTalk Server databases. Step 2 backs up the BizTalk Server database logs. Step 3 specifies for how long the backup history is kept. | No |
| CleanupBTFExpiredEntriesJob_BizTalkMgmtDb | Deletes expired BizTalk Framework entries from the BizTalk Management (BizTalkMgmtDb) database. | Yes |
| DTA Purge and Archive | Automates the archiving of tracked messages and the purging of the BizTalk Tracking database to maintain a healthy system and to keep the tracking data archived for future use. Note On BizTalk Server 2004, this job is created after you install BizTalk Server 2004 Service Pack 2. | No |
| MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb | Detects when a BizTalk Server host instance (BTSNTSvc.exe) has stopped responding. The job then releases the work from the host instance so a different host instance can finish the tasks. | Yes |
| MessageBox_Message_Cleanup_BizTalkMsgBoxDb | Removes all messages that are not referenced by any subscribers in the BizTalkMsgBoxDb database tables. Important This job is also started by theMessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDbjob. Therefore, we recommend that you disable this job. Note On BizTalk Server 2004, this job is enabled by default. Therefore, we recommend that you disable this job. | No |
| MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb | Manages the reference count logs for messages and determines when a message is no longer referenced by a subscriber. This job runs in an infinite loop and deletes the entries from the two individual message reference count logs. This job also calls the MessageBox_Message_Cleanup_BizTalkMsgBoxDb job. Important At first, theMessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDbjob status icon displays a status of Success. However, there will be no corresponding success entry in the job history. If one of the jobs in theMessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDbjob fails, a failure entry appears in the job history and the status icon displays a status of Failure. The job will always display a status of Failure after the first failure. To verify that the other BizTalk Server SQL Server Agent jobs run correctly, check the status of the other BizTalk Server SQL Server Agent jobs. Note On BizTalk Server 2004, this job is created after you install BizTalk Server 2004 Service Pack 2. | Yes |
| MessageBox_Parts_Cleanup_BizTalkMsgBoxDb | Removes all message parts that are no longer referenced by a message in the BizTalkMsgBoxDb database tables. All messages are composed of one or more message parts that contain the message data. | Yes |
| MessageBox_UpdateStats_BizTalkMsgBoxDb | Updates the statistics for the BizTalkMsgBoxDb database. Note This job does not exist on BizTalk Server 2004. | Yes |
| Monitor BizTalk Server | Scans for any known issues with the BizTalkMgmtDb, BizTalkMsgBoxDb, and BizTalkDTADb databases. This includes orphaned instances. Note This job is created on BizTalk Server 2010. | Yes |
| Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb | Used for multiple BizTalkMsgBoxDb database deployment. It asynchronously performs operational actions. For example, it asynchronously performs bulk terminates on the master BizTalkMsgBoxDb database after those changes are applied to the subordinate BizTalkMsgBoxDb database. Note This job does not exist on BizTalk Server 2004. | Yes |
| PurgeSubscriptionsJob_BizTalkMsgBoxDb | Purges unused subscription predicates from the BizTalkMsgBoxDb database. | Yes |
| Rules_Database_Cleanup_BizTalkRuleEngineDb | Purges old audit data from the Rule Engine (BizTalkRuleEngineDb) database every 90 days. This job also purges old history data (deploy/undeploy notifications) from the Rule Engine (BizTalkRuleEngineDb) database every 3 days. Note This job is created on BizTalk Server 2009. | Yes |
| TrackedMessages_Copy_BizTalkMsgBoxDb | Copies the message bodies of tracked messages from the BizTalkMsgBoxDb database to the Tracking (BizTalkDTADb) database. | Yes |
| TrackingSpool_Cleanup_BizTalkMsgBoxDb | Purges inactive tracking spool tables to free database space. Note This job exists only on BizTalk Server 2004. | No |
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
907661 The Tracking_Spool1 or Tracking_Spool2 tables in the BiztalkMsgBoxDb database become very large in BizTalk Server 2004
Subscribe to:
Posts (Atom)