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

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.