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.
48
questions
0
votes
2
answers
68
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 ...
- 103
0
votes
2
answers
99
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 ...
- 125
0
votes
0
answers
37
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 ...
- 37
0
votes
2
answers
119
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 ...
- 3,442
2
votes
1
answer
197
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 ...
- 23
2
votes
1
answer
205
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 ...
- 3,442
0
votes
1
answer
102
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 ...
- 99
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 ...
- 795
1
vote
1
answer
120
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 ...
0
votes
1
answer
97
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 ...
- 1,087
0
votes
1
answer
666
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, ...
- 127
0
votes
2
answers
380
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 ...
- 7
0
votes
1
answer
78
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 =...
- 1
2
votes
1
answer
271
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 ...
- 123
0
votes
1
answer
493
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 ...
- 1,253
0
votes
0
answers
137
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 ...
- 1,253
0
votes
3
answers
153
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 ...
- 1
1
vote
1
answer
3k
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 ...
- 151
2
votes
1
answer
329
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 ...
- 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 ...
- 231
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 ...
- 121
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 ...
- 1,039
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 , ...
- 1,305
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 ...
- 3,442
0
votes
2
answers
437
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 ...
- 3,442
1
vote
2
answers
1k
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 ...
- 3,442
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: ...
- 3,442
0
votes
1
answer
234
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 ...
- 3,442
0
votes
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 ...
- 353
7
votes
3
answers
7k
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 ...
- 247
8
votes
4
answers
29k
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'...
- 1,928
1
vote
2
answers
146
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?
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 ...
- 123
1
vote
1
answer
152
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)&...
- 591
1
vote
1
answer
424
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.
...
5
votes
2
answers
982
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 ...
- 53
1
vote
1
answer
241
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 ...
- 13
2
votes
2
answers
501
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 ...
- 121
1
vote
2
answers
866
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 ...
- 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 ...
- 157
2
votes
1
answer
404
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 ...
- 23
2
votes
1
answer
884
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 ...
- 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 ...
- 331
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.....
- 217
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 ...
- 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 ...
- 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. ...
- 2,295
299
votes
4
answers
347k
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 ...
- 18.1k