First: A Disclaimer. Do NOT attempt this in your production environment.You risk loosing data.
We had an issue in a test environment where the Backup BizTalk job was not configured and had caused the log files to fill the disk to it's limit. The data in the environment wasn't important and destroying a coherent backup chain and point in time restore was not a big deal. We just needed to get the environment back fast to allow test to continue.
I quickly wrote and ran the script below that does the job of truncating BizTalk Servers log files to make the environment functional again. Be aware that this will break the coherence of the log backup chain. To get a point in time that you can restore to you could opt to take a full backup immediatly following this operation if that is important to you. Before running this script you should stop all services that uses the database and make sure that all connections are closed if you want to make sure that your databases are left in a transactionally coherent state.
USE master
ALTER DATABASE BizTalkMgmtDb SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE BizTalkDTADb SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE BizTalkMsgBoxDb SET RECOVERY SIMPLE WITH NO_WAIT
GO
Use BizTalkMgmtDb
DBCC SHRINKFILE (N'BizTalkMgmtDb_log' , 0, TRUNCATEONLY)
GO
Use BizTalkDTADb
DBCC SHRINKFILE (N'BizTalkDTADb_log' , 0, TRUNCATEONLY)
GO
Use BizTalkMsgBoxDb
DBCC 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_WAIT
ALTER DATABASE BizTalkMsgBoxDb SET RECOVERY FULL WITH NO_WAIT
GO
Keep in mind that this is just a sample, add and deduct databases as needed, change the name of log files etc. Once done restart the BizTalk Services and you should be good to go.
You really should, as a first, second and third option, configure the Backup BizTalk job to stop unchecked log file growth. The backup job will make sure that log files don't grow out of control. You will however need to set something up to clear away the backup files created instead. Here is one option for doing that.
There is also something you can do to reduce the size of the log files – enable compression. You can read more about that here.
This log file growth happens because BizTalk Servers databases by default uses the FULL recovery mode. Setting the BizTalk Server databases in SIMPLE mode permanently is a so-so idea. If you really do not need and will never do backups and understand that restore or recover in this case will mean re-install and start from scratch, then ok. If that is not the case, then SIMPLE mode is a bad idea. You can read more about why here. I'll give a hint though – DTC transactions.
HTH
/Johan
Blog Post by: Johan Hedberg