Never use the full recovery model without a backup

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:

  1. select top 10 
  2. [backupsets].backup_finish_date, [backupsets].type
  3. from msdb.dbo.backupset [backupsets]
  4. join sys.databases [databases]
  5. on [backupsets].[database_name] = [databases].[name]
  6. and datediff ( ss, [databases].[create_date] , [backupsets].[database_creation_date] ) = 0
  7. where [backupsets].recovery_model = 'FULL'
  8. 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’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.
  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)
Microsoft – The transaction log may grow without a log backup for a database using the FULL recovery model

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

Your email address will not be published. Required fields are marked *