Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

1
vote
2answers
51 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
51 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
37 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
288 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
60 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
43 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
670 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 ...
5
votes
4answers
3k 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'...
0
votes
0answers
24 views

Redoing an Aborted Transaction

When I have already performed an abort to transaction Ti in log file, for that transaction Ti, what does it mean to Redo(Ti) after that? What are the changes brought to Disk containing the data and ...
1
vote
2answers
61 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
567 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
62 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
112 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
504 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
118 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 ...
3
votes
2answers
258 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
242 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
508 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
205 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
516 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
11k 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.....
5
votes
3answers
6k 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 ...
7
votes
2answers
17k 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. ...
242
votes
4answers
245k 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 ...