question

Run the following command

CHECKPOINT

Then try backup log with truncate_only

Edit ->

If that doesn't work, a good option to try is something along these lines:

DECLARE @file_id [int]
SELECT @file_id = [file_id] FROM [sys].[database_files] WHERE [type] = 1
DBCC SHRINKFILE (@file_id, 1)

Run the following query to see what kind of available space is in the log:

SELECT  instance_name, cntr_value / 1024.0 [Log Space Used (MB)] 
FROM        sys.dm_os_performance_counters
WHERE       counter_name = 'Log File(s) Used Size (KB)'
ORDER BY    instance_name  

Also run this:

DBCC LOGINFO(YourDatabaseName)  

When there is a 2 in the status field it means the log won't shrink less than that portion - the active portion. Create a new table, insert data, delete data etc. then run the DBCC again. When there are status = 0 rows at the end of your log you have room to shrink the log.

Do you have other disks available? You could also set the log to not autogrow and add another log file elsewhere and then perform maintenance to get back to one log file when you can take SQL down.

Another option is to attempt what this article says: Empty Log File

Truncating and Shrinking are two very different things. You need to be looking at how much free space the log file indicates it has. To see this, use Management Studio. Right-click on the database and go to Tasks, Shrink File. Pick Log. Don't hit Ok, just read what it says the percentage of free space is.

Hopefully it's almost completely empty. If not, a truncate can help. The way you do this depends on the Recovery Model used by your database. If it's Simple, then you do it using the T-SQL command CHECKPOINT. If it's Full or Bulk Logged, you need to back up your log.

If you're not interested in keeping your log backup, then put the database into Simple and then do a CHECKPOINT. Then put it back into the Full recovery model and do a database backup.

If the file is largely empty, then shrinking the file will make it smaller on disk. Don't shrink it completely as it will only need to grow, and this is painful for logs (as they need to be zeroed out on disk).

Above all, NEVER USE AUTOSHRINK, as this will really hurt your system. Sounds good. Isn't.

It sounds to me like you may have an open transaction that is keeping the log from being truncated and then shrunk. Try running a query against the sys.dm_tran_database_transactions to see if there is an open transaction on your database.