Never Use Except Full Recovery Model When Backup

Never Use Except Full Recovery Model When Backup

Category: Reliability

Why should you care about this?

If you are using a Full Recovery Model or Bulk Logged Recovery Model database, SQL Server does not empty the log file when you are done with your transactions.

If your SQL Server database is left to “auto-grow” without a maximum limit, you may run out of disk space on your server.

How Can We Check If There Is a Backup Log?

You can use the following T-SQL query to see if there has been a recent log backup for the 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 several 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 SQL Server Agent. For more detailed reading, see our online article on Backing Up and Restoring SQL Server Databases.
2. You can put databases into 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 in case of any problems.