Never use the full recovery model without a backup

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.

By the way, this check is a part of our SQL Server Health Check service.

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)

Let our expert SQL Server Consulting team handle the technical complexities, so you can focus on driving your business forward with confidence.

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

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