SQL Server log file is too big

Question

We are using SQL Server 2008 with full recovery model, the database size is 10 GB and the log file is 172 GB, we want to clear up the space of the log file internally ,we did transaction log file backup it should be clear up, but it still 172 GB ,what to do?

Answer 1

The log file won't get smaller when doing transaction log backups, but it will clear up space internally.

The only way to make the physical file smaller is to shrink it and expand it to a suiting size again. I'm guessing you also have some VLF issues with that size too.

Edit: You should do regular backups to keep the log from expanding again.

You could run something like this to check how much of the log file is in use:

SELECT
   name
   ,type
   ,size/128 AS [size_mb]
   ,FILEPROPERTY(name, 'SpaceUsed') / 128  AS [size_used_mb]
FROM sys.database_files

Answer 2

You only have two methods for minimize these log:

• Shrink.
• Backup (Only on simple recover model).
• Transaction log backup.

The T-Log on MSSQL mantains transactions since the last backup. Because this, that's the only form (at least who I know) to reduce their size.

As I can read, you have a full recovery model, these model doesn't clear the log when you make backup. You need to make a log backup too. Also, this only leaves empty space on the log, preventing getting bigger.

You can read more information about this on Microsoft web page:

https://msdn.microsoft.com/en-us/library/ms189275.aspx

The proper method to "clear" your log with a backup are here:

https://msdn.microsoft.com/en-us/library/ms186289.aspx#DbBuRMf