1) Check to see if BizTalk databases size has grown high.
If Yes you can follow these steps.
Step1:- Cleanup BizTalkDTA database
a) Run "dtasp_purgeAllcompletedtrackingdata" procedure from DTA db. (make a note that it will delete all tracked data)
b) Run command "BACKUP LOG BizTalkDTADb WITH TRUNCATE_ONLY" on DTA db.
c) Run command "DBCC shrinkdatabase (BizTalkDTADb)" on DTA db.
d) Run command "DBCC SHRINKFILE('BizTalkDtaDb');" on DTA db.
Step2:- Cleanup BizTalk MessageBox database.
a) Run "msgbox_cleanup_logic.sql" from "
b) Run "bts_CleanupMsgBox" procedure from MessageBox db.
c) Run command "DBCC shrinkdatabase (BizTalkMsgBoxDb)" on MsgBox db.
d) Run command "DBCC SHRINKFILE('BizTalkMsgBoxDb');" on MsgBox db.
2) Check if its Tracking related Issues.
If database size is small but still CPU is shooting high then check to see following
Step 1:- Tracking is enabled on multiple hosts. If Yes then i would suggest to keep tracking enabled on single host (possibly create a dedicated host for tracking). Issues have been observed on environments where tracking is enabled on multiple hosts.
Steps 2:- Check to see if the issue lies with tracking data not getting moved properly as suggested in http://www.biztalkbill.com/Home/tabid/40/EntryId/91/Issues-with-Tracking-causing-high-CPU-usage-on-BizTalk-SQL-Server.aspx
Imp Note:- Please make a note that the data in databases might get lost and the intention of this blog is to cleanse the system so that the development and testing can start freshly without any issues on server.
- Have a Great Time