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
3answers
55 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
vote
1answer
98 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 ...
1
vote
1answer
33 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 ...
5
votes
2answers
991 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 ...
2
votes
1answer
98 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 ...
0
votes
0answers
22 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 ...
0
votes
0answers
28 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
vote
2answers
599 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 ...
0
votes
2answers
94 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 ...
0
votes
2answers
48 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 ...
2
votes
1answer
492 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: ...
0
votes
1answer
78 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 ...
0
votes
2answers
135 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 ...
7
votes
3answers
2k 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 ...
6
votes
4answers
9k 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
vote
2answers
72 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
2answers
956 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 ...
1
vote
1answer
70 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)&...
2
votes
1answer
142 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. ...
4
votes
2answers
694 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 ...
1
vote
1answer
187 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 ...
2
votes
2answers
311 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 ...
1
vote
2answers
349 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 ...
2
votes
1answer
705 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 ...
2
votes
1answer
283 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 ...
2
votes
1answer
588 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 ...
5
votes
2answers
5k 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 ...
5
votes
2answers
13k 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.....
6
votes
3answers
8k 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 ...
8
votes
2answers
18k 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 ...
11
votes
1answer
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. ...
263
votes
4answers
273k 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 ...