Transaction Log Is Full Within A SQL Server Mirrored Database

I have found a quick and mostly painless way of dealing with a full transaction log that has grown to the point of taking up all of the actual on disk storage within SQL Server.

This method does involve some risk (as with any method of dealing with full transaction logs) you will lose point in time recovery. But I am willing to bet that the application team/user will not care for that since if the trans log and the disk are full the database is not working anyway.

Step 1. Validate the error.

Step 2. If the error is valid go ahead and break mirroring between the databases with the following command: ALTER

DATABASE database_name SET PARTNER OFF;

Step 3. Change the recovery model to simple. This action will essentially truncate the log. Use the following command to change the recovery model: ALTER DATABASE database_name SET RECOVERY SIMPLE;

Step 4. Change the recovery model back to full. This action will re-create a transaction log for the database. Use the following command to change the recovery model: ALTER DATABASE database_name SET RECOVERY FULL;

Step 5. Validate the size of the log and free space of the log.

Step 6. This step is optional but I would recommend it if your database is using shared resources. You can shrink the log file to give storage back to the disk.

Step 7. Finally re-enable mirroring between the databases.

I have had to use this method a couple of times over the past few days and am starting to feel better when it comes to addressing critical issues relating to the transaction logs. Hopefully this post reduces some stress when dealing with full transaction logs.

Originally published at vitamindba.com on October 13, 2017.