1

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.

Bart De Vos
  • 17,761
  • 6
  • 62
  • 81
gaganHR
  • 121
  • 1
  • 2

2 Answers2

1

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.

enter image description here

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!

Bart De Vos
  • 17,761
  • 6
  • 62
  • 81
0

You won't be able to actually delete the file assuming SQL Server is running, and the file is actually the log file for a database in use by SQL Server. You've identified the name as ending in .bak which is typically reserved for SQL Server Backup files, not log files. Typically, the log file extension is .ldf. If it is indeed a backup file, you could delete it; however if SQL Server is making backups regularly to that file, it will of course re-appear the next time the backup occurs. You probably don't want to delete the backup file since it may be critical for disaster recovery.

SQL Server Log Files contain records for each and every modification made to the database itself. Backing up the log will typically mark portions of the file for re-use, so if you look at DBCC LOGPERF after taking a backup, you may wonder why the log file is "so big" when it is only 1% in use. You probably want to be certain about the required size of the file before you shrink it since SQL Server may simply grow the file back to that size the next time maintenance is ran against the database. There are many reasons the log file may grow, and when it does grow, it will temporarily slow response time a lot. See this question and the answers for more details over on the dba.stackexchange.com site.

Community
  • 1
Hannah Vernon
  • 185
  • 4
  • 17