Can't Shrink a .LDF file

Question

I have got an a partition that is 150GB I also have Microsoft SQL server on that volume, the problem is I got a couple different databases on that volume, but I can't shrink the log files because there is no space left.

I tried right click on a DB /tasks/shrink/files & also database.

I get this error message.

Microsoft SQL Server Management Studio

Shrink failed for Database 'cterm_across'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+Database&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The transaction log for database 'cterm_across' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (Microsoft SQL Server, Error: 9002)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=9002&LinkId=20476

How can I walk around this?

Thank you in advance

How did you try to shrink the log file? What options did you use?How much of the log file is free? If you are using Full recovery mode, backup the log file before trying to shrink it. You can't delete log entries that haven't been backed up. — Panagiotis Kanavos
What recovery model your database is using? If "full recovery", have you created full backup lately? — Arvo
Hello Panagiotis, I have used Shrink database - test I checked --> Reorganize files before releasing unused space. Selecting this option may affect performance. — XsiSec
Arvo How do I see the recovery model I use? sorry I have less expereicen in SQL server. it looks like the DB does a backup each day... — XsiSec
please tell us the output of this query: SELECT d.recovery_model, d.recovery_model_desc, d.log_reuse_wait, d.log_reuse_wait_desc FROM sys.databases d WHERE d.name = 'cterm_across' - first two columns describe the recovery model — CeOnSql

CeOnSql CeOnSql · Accepted Answer · 2015-07-20T09:12:04

This works for me:

-- Do a Backup of your database - save it anywhere:
BACKUP DATABASE cterm_across TO DISK = N'C:\Temp\cterm_across.bak'

-- Do a Backup of the transaction log - save it anywhere:
BACKUP LOG cterm_across TO DISK = N'C:\Temp\cterm_across.bak'
-- If you want to truncate the transaction log: save the backup on null device:
--BACKUP LOG cterm_across TO DISK='NUL:'

-- Change recovery model to simple for the call of Shrinkfile
ALTER DATABASE cterm_across SET RECOVERY SIMPLE
DBCC SHRINKFILE(logical_name_of_your_logfile)
ALTER DATABASE cterm_across SET RECOVERY FULL

You just have to replace the logical_name_of_your_logfile with the correct logical file name. You can find them here: Rightclick on database - Properties - Files - The column Logical Name... by default something like cterm_across_log maybe

Can't Shrink a .LDF file

1 Answers