Never Use The Full Recovery Model Without A Backup

Full recovery mode without backup logs — why should you care about it?

If you use a database in the full or bulk-logged recovery model, SQL Server does 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 :

1. You can set up transaction log backups using SQL Server 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.

2. 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)