Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [recovery-model]

The recovery model is a property of a Microsoft SQL Server database. It controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged.

The recovery model is a property of a Microsoft SQL Server database. It controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.

Three recovery models exist:

Simple

No log backups. Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. For information about database backups under the simple recovery model, see Full Database Backups (SQL Server). Operations that require transaction log backups are not supported by the simple recovery model. The following features cannot be used in simple recovery mode: Log shipping AlwaysOn or Database mirroring Media recovery without data loss Point-in-time restores

Full

Requires log backups. No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, prior to application or user error). For information about database backups under the full recovery model, see Full Database Backups (SQL Server) and Complete Database Restores (Full Recovery Model).

Bulk-logged.

Requires log backups. An adjunct of the full recovery model that permits high-performance bulk copy operations. Reduces log space usage by using minimal logging for most bulk operations. For information about operations that can be minimally logged, see The Transaction Log (SQL Server). For information about database backups under the bulk-logged recovery model, see Full Database Backups (SQL Server) and Complete Database Restores (Full Recovery Model).

A database can be switched to another recovery model at any time.

46 questions
297
votes
4 answers

Why Does the Transaction Log Keep Growing or Run Out of Space?

This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this: In SQL Server - What are some reasons the transaction log grows so large? Why is my log file so big? What…
Mike Walsh
  • 18,013
  • 6
  • 46
  • 72
11
votes
1 answer

SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc

While I'm doing my own investigation, does anyone know why a database in SIMPLE recovery model has LOG_BACKUP for the log_reuse_wait_desc? SQL Server 2012 SP1. Database created just a few weeks ago. No replication, no mirroring, no log shipping, and…
Travis
  • 2,275
  • 2
  • 19
  • 25
9
votes
2 answers

Set simple recovery mode and shrink log files for all user created databases

I hope you can point me in the right direction. I'm not a frequent user of T-SQL, but I did some googleing, and found the script below. I corrected the script a bit. I want the script to: To select all databases, except the system DBs. To set…
Arviddk
  • 353
  • 3
  • 4
  • 10
8
votes
3 answers

Why is Full Recovery model a requirement for an Availability Group?

We have all our production databases in simple recovery model, and are very happy about it because it fully satisfies our RPOs and RTOs. Now we want to implement AG DR solution for our databases and we found out that full recovery model is a…
Bit Cat
  • 97
  • 1
  • 1
  • 3
8
votes
4 answers

Why Can't I shrink log file in full recovery mode

I have a log file that is 302MB. I have done a log backup which has left the log file mostly free (I can see this through the Disk Usage standard report) If i try to run DBCC SHRINKFILE (N'AdventureWorks2014_Log' , 0, TRUNCATEONLY) or DBCC…
SE1986
  • 1,729
  • 2
  • 18
  • 47
7
votes
3 answers

Database enters in recovery mode every time the Transaction Log is full

I am facing a situation that it is being somewhat hard to address. I need help to understand what is happening. TL;DR: Every time the Transaction Log gets full in SQL Server it needs to shutdown the database to enter in Recovery Mode and rollback…
Diego Queiroz
  • 247
  • 3
  • 8
7
votes
2 answers

Switching to Simple Recovery - shrinking transaction logs

Standard disclaimer: I am an "involuntary DBA" (a nice phrase I saw in this article) and have done lots and lots and lots and lots and lots of reading on this subject, but am still confused/concerned... I need to change the recovery model of several…
freefaller
  • 217
  • 2
  • 4
  • 10
5
votes
2 answers

Do I need full recovery mode when I have multiple daily backup?

I have created 5 maintenance plans on my SQL server (say 50 !) which generate databse backup 5 times daily. As I read in this Q/A the full recovery is good when I need Point-in-time recovery. My exact question: Is generating multiple daily backup…
Ali Sheikhpour
  • 231
  • 2
  • 9
5
votes
2 answers

Setting DB to Simple Recovery then back to Full Recovery

I have recently inherited all of our company's SQL databases without much warning or experience, and was hoping to make a good impression and score a few quick wins by completely reviewing backups across the board. I've implemented various full and…
squizz
  • 53
  • 4
5
votes
2 answers

Restore database to point in time from full backup only

I have a database with recovery mode set to FULL. It's a dev database and I have two full backups dated 19th and 27th i.e. latest. One of the developer did some changes on 26th and I want to rollback those changes and want to restore database at a…
Shantanu Gupta
  • 321
  • 1
  • 3
  • 11
2
votes
1 answer

Transaction log file size growing, what will be the consequences of setting recovery mode to simple

Recovery mode is set to FULL, and FULL backup is taken on alternate days. No log/differential backups. Log file size is growing too big. I've shrank log file (googled) a week back and was fine. Now the file is growing again. What if we set Recovery…
Baig
  • 23
  • 2
2
votes
1 answer

Does simple recovery model not log bulk inserts?

I have a db in full recovery model. There is a planned insert of a million rows that needs to be done next week. In order to not increase the log size, I am told there are 2 options. Option 1 is switch to bulk model, perform the insert and then…
variable
  • 3,141
  • 1
  • 16
  • 44
2
votes
1 answer

SQL Server Installer appears to be resetting MSDB Recovery Mode to Simple

Everytime we upgrade a SQL Server installation (service pack or cumulative update), msdb is changed from FULL recovery mode to SIMPLE recovery mode. I understand that the msdb database doesn't change much and it is common to have it in SIMPLE, but…
gregpakes
  • 123
  • 3
2
votes
1 answer

SQL Server Replication and Recovery Model

In SQL Server for Replication when recovery model is not very important then how it saves the transactions and also keeps the secondary instance in sync? I tried to check on MSDN and found the following: Replication functions properly using any of…
Mr. K
  • 141
  • 5
2
votes
1 answer

Why does my database using the simple recovery model have such a large transaction log?

Not a duplicate with: Transaction Log is big! But simple recovery mode is chosen! Why is my transaction file growing when db is in simple mode and there are no waiting transactions Reason: My log is not big, but empty. It's big, but in use. The…
Jesse Hallam
  • 121
  • 2
1
2 3 4