There are only 2 ways the Transaction Log will truncate itself to reuse the internal space (Virtual log files) , 1 is through checkpoint process, on the simple recovery model when the log gets 70% full. The other, is after every Log Backup under Full recovery model. Maybe something is going on with these log backups. Perhaps they are completing in SQL Agent, but not doing anything at all? Try checking the backup history table in MSDB to make sure the log backups are happening and their physical file exist
SELECT TOP (10) a.database_name[DB],a.server_name[SQL INST],b.physical_device_name,a.backup_finish_date [BKP END DATE],CASE a.type
WHEN 'D' THEN 'FULL'
WHEN 'L' THEN 'LOG'
END AS 'Backup Type'
FROM dbo.backupset a
JOIN dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE database_name IN ('Your Database Here') AND type = 'L'
ORDER BY backup_finish_date DESC
Thanks
No comments:
Post a Comment