Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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.

Filter by
Sorted by
Tagged with
0 votes
0 answers
68 views

Database log file is so big

My database log file is so big(400 giga) and data file is (50 giga) My recovery method is simple and backup plan is daily full backup Log file is configured to auto growth. I checked open transactions,...
Abdelrahman Mamdouh 's user avatar
0 votes
2 answers
84 views

What is a difference in failover time for SQL Server FCI when database is in Full vs Simple recovery mode

I have 2-node SQL Server 2019 FCI cluster, which takes up to 4 minutes to failover from one node to another. The cluster has 3 user databases, migrated from the older SQL server versions: 2 databases ...
Sergey Nudnov's user avatar
0 votes
2 answers
153 views

MS SQL full backup by partition, can be done without log?

The matter is that I really don't need t-tlog and i wish to avoid it. I'have looked in many topics but I haven't seek out the answer I need (the topic of MSSQL backup is very tricky to understand well ...
Radioleao's user avatar
  • 127
0 votes
0 answers
38 views

How do I use this script to find why my recovery model changed from full to simple?

I have found a post that has a script that will provide details for my question. When I execute the script no results return. What do I need to change within the script to return results for the ...
Thomas's user avatar
  • 37
0 votes
2 answers
139 views

Is there any issue with changing the model database to SIMPLE recovery model?

The model database is in FULL recovery model. I know that new dbs follow the model db template and so will be automatically in FULL recovery model. So that the new dbs are automatically in SIMPLE ...
variable's user avatar
  • 3,636
2 votes
1 answer
205 views

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 ...
Baig's user avatar
  • 23
2 votes
1 answer
232 views

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 ...
variable's user avatar
  • 3,636
0 votes
1 answer
115 views

Recovery_model for backupset table null sqlserver

Going through https://www.sqlshack.com/understanding-database-recovery-models/ I see that there are three types of recovery models: SIMPLE FULL BULK_Logged But for the table msdb.dbo.backupset I can ...
Sonali Gupta's user avatar
0 votes
1 answer
2k views

with (tablock) does not seem to make code faster for insert select [closed]

I read about minimal logging and that one should use with (tablock) for insert selects. I had high expectations when I added this but it does not seem to make a difference. I use it in ETL and tables ...
xhr489's user avatar
  • 807
1 vote
1 answer
136 views

RECOVERY MODEL changed on a database, no record in Default Trace [closed]

I have a Log backup job running every hour via SQL Agent. This morning it failed at 3:00 AM with this error: The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. But it ran fine ...
Todd Chittenden's user avatar
0 votes
1 answer
127 views

How to optimize and estimate DB recovery times?

Would like to optimize recovery time for db's after failover to passive node in high availability cluster. Assuming full recovery model... What are the steps to take to optimize DB recovery time for a ...
user2368632's user avatar
  • 1,097
0 votes
1 answer
813 views

How to use wal_level in Postgres to set the recovery mode to simple for a database like how Microsoft SQL Server does it?

In Microsoft SQL Server, ALTER DATABASE CURRENT SET RECOVERY SIMPLE WITH NO_WAIT; will set the recovery mode for a database to simple, so the transaction log file space can be re-used. In Postgres, ...
hunterex's user avatar
  • 147
0 votes
2 answers
501 views

Can you change recovery mode from simple to full with users working on the server?

Can you change recovery mode from simple to full with users working on the server? While the server is changing the mode is the work of the users interrupted? If so, for how long. Version: SQL server ...
felix's user avatar
  • 7
0 votes
1 answer
82 views

SQL Server and ASP.NET - Backup Log database with Application Pool in Always Running

I have a database on SQL Server Express used for 3 Asp.net applications. The Recovery Model is Full. Every day I execute a backup database and log with this code BACKUP DATABASE @databaseName TO DISK =...
S.A.'s user avatar
  • 1
2 votes
1 answer
327 views

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 ...
gregpakes's user avatar
  • 123
0 votes
1 answer
725 views

Why T-log is not truncated after T-log backup?

I have a database myDB in full recovery model. I took a full backup and restored it on the test server. Afterwards, I checked the size of T-log. The size of t-log was 15600 MB and used space out of ...
Rauf Asadov's user avatar
  • 1,273
0 votes
0 answers
140 views

Problem with calculating an approximate space required for online index rebuild

I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes ...
Rauf Asadov's user avatar
  • 1,273
0 votes
3 answers
157 views

Simple and Full recovery models

Not an SQL admin and new to ola-hallengren great scripts. Just wondering if people are mostly doing a blanket user database backup with his full/diff script regardless on if a DB is in Simple or ...
Daniel's user avatar
  • 1
1 vote
1 answer
4k views

Transaction log is full (due to NOTHING)... but this database is in simple recovery mode

I'm supporting an antedeluvian webapp (soon to be retired) that still uses "aspnetdb" for its auth system. I was doing some work in prep for its retirement on my test environment, when I found my ...
Pxtl's user avatar
  • 151
2 votes
1 answer
372 views

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 ...
Mr. K's user avatar
  • 141
5 votes
2 answers
1k views

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 ...
Ali Sheikhpour's user avatar
2 votes
1 answer
1k views

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 ...
Jesse Hallam's user avatar
0 votes
0 answers
23 views

Log file is growing up and is not releasing space [duplicate]

The database is under Full Recovery Method, the DBCC SQLPERF (LOGSPACE) shows 3 % used. Even after I take Log back ups frequently it is not releasing space. There is no active transaction and ...
SQL_NoExpert's user avatar
  • 1,061
0 votes
0 answers
29 views

LDF file size is very huge even after doing Transaction log backup [duplicate]

I work on a SQL Server 2016 Database and in our production environment we have primary SQL server and a fail over SQL server. All the transactions are occurring on the primary SQL server. In primary , ...
user9516827's user avatar
  • 1,315
1 vote
2 answers
2k views

Why doesn't simple recovery model support log backup or tail log backup?

Lets say there is a crash then we can use the full backup and a tail log backup - because if there was checkpoint then data would already be written to the database data file and flushed from the ...
variable's user avatar
  • 3,636
0 votes
2 answers
448 views

Transaction log backup - Only full recovery model?

I am reading this book from Microsoft - it says: "A log backup cannot be performed unless the database is in full recovery model and an initial full backup has been performed" Is this correct? I know ...
variable's user avatar
  • 3,636
1 vote
2 answers
2k views

What is technical meaning of point in time recovery?

Please can I have a technical meaning of point in time recovery? Does it mean that given a backup file, we must be able to recover upto any point in time during the span of the time covered by the ...
variable's user avatar
  • 3,636
2 votes
1 answer
1k views

Log Shipping - Secondary Database(s) - Which recovery model?

In Log shipping configuration - the primary database must be either in Full or Bulk-Logged recovery model. However, is there any requirement of recovery model of the secondary database? Example: ...
variable's user avatar
  • 3,636
0 votes
1 answer
249 views

What is the technical meaning of "Bulk" in the “Bulk-Logged Recovery Model” and why this name?

With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... that are not fully logged in the transaction log and therefore do not take as much space in the ...
variable's user avatar
  • 3,636
1 vote
2 answers
2k views

Find out recovery model changes

I'd like to work around instances where a database were changed to simple and then reverted back to full or bulk logged recovery models. This is for my backup maintenance plan, as obviously any ...
Krishn's user avatar
  • 373
7 votes
3 answers
8k views

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 ...
Diego Queiroz's user avatar
8 votes
4 answers
31k views

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

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'...
SE1986's user avatar
  • 1,970
1 vote
2 answers
152 views

Does the recovery model affect growth of the .mdf file? [closed]

On my production database, the .mdf file is rapidly growing on a daily basis. If I change the recovery model from SIMPLE to FULL, will that help?
Mohamad Mahmoud Darwish's user avatar
2 votes
2 answers
3k views

Simple recovery mode with mirroring - is that possible?

(The usual 'I'm not a real DBA, but I need to handle this somehow' disclamer here) We have a database with Full recovery model that doesn't really need a Full recovery model... only it does. Some ...
Darth Veyda's user avatar
1 vote
1 answer
157 views

Full backup of Simple recovery model - how much log is backed up if there is an open transaction

I have a situation right now. There is a database in SIMPLE recovery model. Note: 1. Compression is not enabled which will be enabled pretty soon. 2. SQL Server 2012 SP3 BackupSize: 8GB (Usually)&...
Santhoshkumar KB's user avatar
1 vote
1 answer
446 views

SQL recovery model for ADFS deployment

We have "AdfsConfiguration" and "AdfsArtifactStore" databases for our ADFS deployment. I keep running into disk space issues due to the full and transaction log backups being taken on the server. ...
default_noob_network's user avatar
5 votes
2 answers
996 views

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 ...
squizz's user avatar
  • 53
1 vote
1 answer
248 views

Recovery model from Full to simple and Shrink transact log result on poor performance

I'm new mantaining databases, I'm more like programmer but you know in some jobs you must be multitools because theres no specific dba. My boss told me to change the recovery model from Full to ...
Robert K's user avatar
2 votes
2 answers
518 views

Concurrent backups (full and transaction) and testing recovery model

My intent is to test my recovery model and setup log shipping to replicate a database on another server. I have a database called AdventureWorks on a primary sql server. Presently SQL Backup performs ...
Mike Murphy's user avatar
1 vote
2 answers
979 views

Database in simple recovery to save space

This old SQL Server 2000 instance has a 14Gb disc for data and a 30Gb disc for backups. Even though it is old, it still has tons of users doing transactions so the log file of the only database that ...
A_V's user avatar
  • 1,364
2 votes
1 answer
1k views

What is the role of eager writer?

What is the role of eager writer? How this process works internally in SQL server? What is the major difference between eager writer and lazy writer/checkpoint? My understanding here is, that both ...
Sunil Kumar's user avatar
2 votes
1 answer
440 views

High Log Space Used for model System database

I was running DBCC SQLPERF(LOGSPACE) and noticed that while my user databases have nice, low Log Space Used percentages, my model system database is currently at 98%. I have it set to the default Full ...
Script Wolf's user avatar
2 votes
1 answer
899 views

Database in SIMPLE recovery model full using SSIS bulk insert task

I have a database in SIMPLE recovery model with 10GB free. The file I am trying to load through SSISs bulk load is 1GB. The table is empty (truncated prior to each loading), is a heap and has no ...
blobbles's user avatar
  • 1,621
5 votes
2 answers
7k views

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 ...
Shantanu Gupta's user avatar
7 votes
2 answers
17k views

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.....
freefaller's user avatar
8 votes
3 answers
15k views

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 ...
Bit Cat's user avatar
  • 97
9 votes
2 answers
23k views

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 ...
Arviddk's user avatar
  • 363
11 votes
1 answer
3k views

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. ...
Travis's user avatar
  • 2,315
300 votes
4 answers
351k views

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 ...
Mike Walsh's user avatar
  • 18.2k