Bookmark and Share
Check reasons of Inability to Truncate a Log
(Publish Date: 2014-5-23 1:37pm, Total Visits: 314, Today: 1, This Week: 1, This Month: 3)

-- Check size for a DB and LOG

SELECT name AS Name,
    size * 8 / 1024. as SizeinMB,
    FILEPROPERTY(name, 'SpaceUsed') * 8 /1024. as SpaceUsedInMB,
    CAST(FILEPROPERTY(name, 'SpaceUsed') as decimal(10,4))
      / CAST(size as decimal(10, 4)) * 100 as PercentSpaceUsed
FROM sys.database_files;
GO

--Check what is preventing the log truncation

SELECT name, log_reuse_wait_desc FROM sys.databases;
GO

-- If you see 'LOG_BACKUP' in log_reuse_wait_desc, do a log backup for that DB.

BACKUP LOG LogTest TO DISK = 'L:\LOG\LogTest_tr.bak' WITH INIT;
GO

-- CHECK the log size again

SELECT name AS Name,
    size * 8 / 1024. as SizeinMB,
    FILEPROPERTY(name, 'SpaceUsed') * 8 /1024. as SpaceUsedInMB,
    CAST(FILEPROPERTY(name, 'SpaceUsed') as decimal(10,4))
      / CAST(size as decimal(10, 4)) * 100 as PercentSpaceUsed
FROM sys.database_files
WHERE type=1;
GO