Well, this is a classical problem: The transaction log has been growing since who knows when and is now taking up almost all space on the server. You need to shrink the transaction log in order to free space on the server.
There are several solutions to this problem and it all depends on your database. This time I used the following script to get rid of all data in the transaction log and worth mentioning is that I didn’t care about the data in the transaction log; my goal was only to get rid of the unnecessary data and to reduce the size of the transaction log.
USE [MyDatabase] GO ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE; GO DBCC SHRINKFILE ([MyDatabase_Log], 1); GO ALTER DATABASE [MyDatabase] SET RECOVERY FULL; GO
The script above worked fine for me and you will find similar solutions on the internet. If you analyze the script you will see that it changes the recovery model from FULL to SIMPLE which means that the database will not longer log transactions that occur in the database. After the change we execute the DBCC SHRINKFILE command that will try to shrink the file stated by the logical filename (MyDatabase_Log) to the desired size in megabytes (the second parameter to the DBCC command). When the file has been shrunk we restore the recovery model to FULL that was the original setting for the database; of course this all depends on your scenario, but my guess if you read this is that you are using the FULL recovery model.
While looking around I found another older solution that were used earlier to accomplish sort of the same thing, however it’s recommended not to use the approach mentioned below.
USE [MyDatabase] GO DBCC SHRINKFILE([MyDatabase_Log], 1) BACKUP LOG [MyDatabase] WITH TRUNCATE_ONLY DBCC SHRINKFILE([MyDatabase_Log], 1) GO