Clean Up BizTalk Databases and Logs

Clean Up BizTalk Databases and Logs


Problem Statement: Usually on Development machine biztalk databases logs grows and fill most of the space of disk. 

Cause: This usually happens when the biztalk jobs either not configured or SQL Agent is not running.

Solution:
Note: This should not be applied on PRODUCTION Servers.

Execure below lines of code on SQL Sever where biztalk is configured.

Step 1:


=====Script Start==============
USE master
ALTER DATABASE BizTalkMgmtDb SET RECOVERY SIMPLE WITH NO_WAITALTER DATABASE BizTalkDTADb SET RECOVERY SIMPLE WITH NO_WAITALTER DATABASE BizTalkMsgBoxDb SET RECOVERY SIMPLE WITH NO_WAITGO

Use BizTalkMgmtDbDBCC SHRINKFILE (N'BizTalkMgmtDb_log' , 0, TRUNCATEONLY)GO

Use BizTalkDTADbDBCC SHRINKFILE (N'BizTalkDTADb_log' , 0, TRUNCATEONLY)GO

Use BizTalkMsgBoxDbDBCC SHRINKFILE (N'BizTalkMsgBoxDb_log' , 0, TRUNCATEONLY)GO

USE master
ALTER DATABASE BizTalkMgmtDb SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE BizTalkDTADb SET RECOVERY FULL WITH NO_WAITALTER DATABASE BizTalkMsgBoxDb SET RECOVERY FULL WITH NO_WAITGO

=======Script End=================

Step 2: 

a. Go to BizTalk installation directory example :
C:\Program Files (x86)\Microsoft BizTalk Server 2010\Schema\
b. run script msgbox_cleanup_logic.sql  on BizTalk MsgBox DB database.
c. Now execute bts_CleanupMsgbox from BizTalk MsgBox DB database.



References:

http://blogical.se/blogs/johan/archive/2011/12/22/truncate-biztalk-servers-database-log-files-to-clear-up-space.aspx

https://masteringbiztalkserver.wordpress.com/2011/01/10/reset-biztalk-server-msgbox-database-%E2%80%93-useful-for-all-developers/

Regards
Kundan