Category: Reliability
Item: Full Recovery Mode without backup logs
Why should you care about it?
If you are using a database is in Full Recovery Model or Bulk Logged Recovery Model, SQL Server do not free up the log file when your transactions finish.
Your server may run out of disk space if your SQL Server database is left to “auto-grow” without a maximum limit.
How can I check if there is a backup log?
You can use the following T-SQL query to see if you have a recent log backup for your database:
select top 10
[backupsets].backup_finish_date, [backupsets].type
from msdb.dbo.backupset [backupsets]
join sys.databases [databases]
on [backupsets].[database_name] = [databases].[name]
and datediff ( ss, [databases].[create_date] , [backupsets].[database_creation_date] ) = 0
where [backupsets].recovery_model = 'FULL'
order by [backupsets].backup_finish_date desc
How to fix the problem?
There are a couple of ways :
- You can set up transaction log backups using SQL Server’s built-in Maintenance Plans, or use free tools to customize jobs in the SQL Server Agent. For more detailed reading, see the online documentation for Backup, and Restore of SQL Server Databases.
- You can put the databases in the SIMPLE recovery model. This model does not require transaction log backups, but you will lose all data since the last full or differential backup if there’s any problem.
More information:
Microsoft – Transaction Log Backups (SQL Server)
Microsoft – The transaction log may grow without a log backup for a database using the FULL recovery model
