To backup a database, we could instruct SQL Server to truncate of the transaction log, using T-SQL command: BACKUP LOG db_name WITH TRUNCATE_ONLY. Log truncation remove transactions from the log file and the space remains allocated to the file. Sometimes as in my case below, you need to shrink the log file to reclaim the space.
In my case, I receive a backup file from SQL Server 2008, probably a full backup. After restoring the file and create a new database on my laptop, the used space is 160MB for the data and 52GB for the log file. My local disk free space 18 GB left, I need to shrinking the log file.
1. Check the database size property:
Select name, filename, convert(decimal(12,2), round(a.size/128.000,2)) as SizeMB,
convert(decimal(12,2), round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpcUsedMB,
convert(decimal(12,2), round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpcMB
from dbo.sysfiles a
name filename SizeMB SpcUsedMB FreeSpcMB
-----------------------------------------------------------------
Student D:\DATA\STUDENT.mdf 155.69 149.50 6.19
Student_log D:\DATA\STUDENT.ldf 50861.94 362.09 50499.84
The FreeSpcMB shows 51GB and I try to reclaim the space of the log file to OS.
2. Change the database recover mode to simple
ALTER DATABASE Student SET RECOVERY SIMPLE;
GO
3. Do the Shrink the log file to 1 MB
DBCC SHRINKFILE (Student_log, 1);
GO
3. Reset the database recovery model.
ALTER DATABASE Student SET RECOVERY FULL;
GO
SELECT name, recovery_model_desc FROM sys.databases;
GO
Now, the OS has 70GB free space.
