Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When should I use the full recovery model and when should I use the simple recovery model for databases?

I always used the full recovery model because it is the default, but today i encountered this error:

Microsoft OLE DB Provider for SQL Server (0x80040E14) The transaction log for database 'DATABASE NAME' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The specific database is actually one of the smallest and most inactive databases on my server, so I have no idea how the log can be full on this database, and not the others.

To shrink the log and making the database accessable again, I changed the recovery model from FULL to SIMPLE and shrinked the logical file log, with the following command

alter database DATABASE NAME recovery simple
go
dbcc shrinkfile('LOG FILE LOGICAL NAME', 100)
go

It helped, but now I need to understand WHY it helped, HOW this situation started and HOW to prevent this in the future?

EDIT:

Every night at 1 o'clock, we are doing a scripted backup of every database on the server. This is being done by a 31 line script where the most important part is

set @Filename = 'D:\backup\' + convert(varchar, getDate(), 112) + ' - ' + @DBName + '.bak'
set @Description = 'Full backup of database ' + @Filename
BACKUP DATABASE @DBName TO DISK = @Filename WITH INIT , NOUNLOAD , NAME = @Description, NOSKIP , STATS = 10, NOFORMAT

Is the new recovery model and databaseshrink going to be a conflict with this script?

We are not doing any other kind of backup of the databases, and therefore not the transaction logs, should we?

share|improve this question

1 Answer 1

up vote 16 down vote accepted

When should I use the full recovery model and when should I use the simple recovery model for databases?

You should use the full recovery model when you require point-in-time recovery of your database. You should use simple recovery model when you don't need point-in-time recovery of your database, and when the last full or differential backup is sufficient as a recovery point. (Note: there is another recovery model, bulk logged. For more information on the bulk-logged recovery model see this reference)

Microsoft OLE DB Provider for SQL Server (0x80040E14) The transaction log for database 'DATABASE NAME' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The reason you got that error (most likely) is because you haven't been backing up your transaction log. When it isn't backed up, then it will continue to physically grow the transaction log file (provided autogrowth is enabled and maxsize allows) because it can't reuse any of the "portions" of the transaction log (virtual log files). It can only mark those VLFs for reuse and allow the "wrap-around" nature of the transaction log when you do a transaction log backup (and a few other requirements, such as no active transactions, some replication aspects, etc.).

To shrink the log and making the database accessable again, I changed the recovery model from FULL to SIMPLE and shrinked the logical file log, with the following command

......

It helped, but now I need to understand WHY it helped, HOW this situation started and HOW to prevent this in the future?

This helped you because by setting your database to the simple recovery model you told SQL Server that you no longer care about point-in-time recovery, and the requirement to ensure that virtual log files no longer need to be preserved and marked as active, now a checkpoint process marks these VLFs as inactive.

Excerpt/quote taken from this MSDN reference:

Under the simple recovery model, unless some factor is delaying log truncation, an automatic checkpoint truncates the unused section of the transaction log. In contrast, under the full and bulk-logged recovery models, once a log backup chain has been established, automatic checkpoints do not cause log truncation.

Then you did a physical database file shrink and because there was free space in your transaction log now it was able to physically shrink the NTFS file.

Reading worth spending some time on:

  1. Recovery Models
  2. Managing Transaction Logs (Gail Shaw)
  3. Factors That Can Delay Log Truncation

EDIT after your Edit:

Is the new recovery model and databaseshrink going to be a conflict with this script?

That BACKUP DATABASE command will work with either recovery models. As for the routine database shrink... DON'T DO IT!!!! Seriously, size your database accordingly, and if you utilize the full recovery model then ensure that you are doing routine and frequent transaction log files, not just to keep the transaction log size at bay but to also meet recovery point objects.

We are not doing any other kind of backup of the databases, and therefore not the transaction logs, should we?

If your database is utilizing the full recovery model, then yes you should be doing transaction log backups. If your database is in simple recovery, then you physically can not do a transaction log backup.

As for what recovery model to use (simple vs. full), we can't make that decision for you. Only you, your business team, and your SLAs can.

share|improve this answer
    
Hi Thomas, great reading, thanks! I just edited the question with a follow-up question regarding the current backup-strategy, if You could please reply on this as well? :) –  Behrens Jun 19 '13 at 20:24
    
Thanks for the edit. We're not doing a routine shrink and don't intend to, what I meant was; could the database shrink I just did interfere with the daily backup job, and if I understand correct; it dont :) We'll do an intern analysis on wether the full recovery model is needed and if not, we'll switch to the simple model so we don't run into this problem again. Thanks for your help! :) –  Behrens Jun 19 '13 at 20:34
1  
Excellent write up @ThomasStringer. Definitely a +1. Erizias, in answer to your question. Yes you should be doing transaction log backups on any database that is not in Simple recovery. No, I don't believe your script will have any problems if the database is changed to simple recovery or shrunk. –  Kenneth Fisher Jun 19 '13 at 20:36
    
You might also want to add that if you are using database mirroring, you need to use Full Recovery Model as it uses the log file to keep the mirror updated. –  Holger Sep 17 at 12:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.