How to free up disk space on a drive which has BiztalkMsgBoxDB_log file in it

Question

I have been asked to analyze a issue regarding one of the biztalk servers. I was asked to free up space on a particular drive, where I found the only file BiztalkMsgBoxDB_log.bak is taking up close 90% of the drive. Running the following query I later found out that the log space used is only 1.25%.

EXEC ('DBCC sqlperf(LOGSPACE) WITH NO_INFOMSGS')

**Database Name**   **Log Size (MB)**   **Log Space Used (%)**  **Status**
  BizTalkMsgBoxDb     24930.49            1.257622                0

currently the Recovery Mode is : FULL and the transaction log back up was taken an hour ago.

I have no clue as to why the log file was created so large. How can I free up data on this drive.

Solution

You should shrink your log. Don't delete it!

In SQL Server MGMNT Studio, right click your database, Tasks > Shrink > Files. Select your log as in the picture below (or shrink to a size that is OK for you) and click OK.

You might want to take a look at your autogrow settings afterwards and maybe set a limit, to make sure this doesn't happen in the future.

What I mostly recommend is putting the log files on a separate disk (or partition if you can't add extra disks). That way the log files can fill up the drive without interrupting anything else. (Same goes for the tempdb by the way).

If the log file stays this big after running this, it's possible there is still a transaction blocking the action. Try finding it with sp_who2 or sp_whoisactive and see if you can stop it. Bonus points for not just killing it.

Make sure it's not stuck on replication somewhere. You can try putting the DB in simple mode and back to full, but this should be a last resort. Don't forget to check your backups afterwards!

Comments

[womble]

Have you considered deleting the file, if it is unnecessary?

[Katherine Villyard]

Er. BiztalkMsgBoxDB_log.bak, and not BiztalkMsgBoxDB_log.ldf?