Shrinking SQL Server Log file

The logfile of the BizTalk Server Message Box database (BiztalkMsgBoxDb) is rapidly growing if you the recovery model is set to "full".

At first you should try this (taken from here):


ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE (BiztalkMsgBoxDb_log, 1);
GO

ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY FULL;
GO


In some cases you're not able to shrink this file.
This may refer to some dead processes and unfinished transactions.

Your can check for the oldest transactions using:

DBCC OPENTRAN ('BizTalkMsgBoxDb')

If you find a very old transaction there you may kill the PID with the Acitvity Monitor of the SQL Server Studio.
Repeat the last two steps until all old transactions are disappeared.
Finally do
BACKUP LOG BizTalkMsgBoxDb WITH TRUNCATE_ONLY

DBCC Shrinkfile ('BizTalkMsgBoxDb_log',2) (where "2" is the targeted Logfile size)

and enjoy the new log sizes.