January 29, 2007 at 6:46 am
Hi,
I was just reading a book 'SQL Server 2000 for Experienced DBAs' when I chanced upon this statement in the book about Simple Recovery Model:
The easiest to administer, but only allows you to perform complete and differential backups. No transaction logs are kept or can be backed up.
I am surprised about the statement marked in red.
I am of the opinion even though the transaction logs cannot be backed up in Simple recovery model but still transactions are logged in transaction log.
Pls advise.
Regards
RSingh
January 29, 2007 at 7:27 am
This is correct, but the log data is not retained, so it's a question of wording.
The log still exists in simple code, transactions are logged and once they commit, they are removed during the next checkpoint.
January 29, 2007 at 7:59 am
I hope the statement should read like this
no transactions are kept in the log file. i hope this would mean the exact sense of the phrase used.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
January 31, 2007 at 2:50 am
Steve is right.
In SQL2000, Simple recovery mode logs virtually the same amount of information as Full recovery mode. In SQL2005, Simple recovery mode logs virtually the same amount of information as Bulk Logged recovery mode.
For both versions, when the transaction commits, the log records become elibible for deletion. When all records in a logical log file become eligable for deletion, the next SQL Server Checkpoint process will truncate the log by marking that logical log file as reuseable.
SQL needs to write this level of information to the log so that transaction rollback can occur. If Simple mode meant nothing was written to the log, then Rollback would not be possible. Because data does get written to the log in Simple mode, you have to allow enough disk space to hold log information for your largest transaction, rounded up to a logical log file boundary.
For more details on logical log files, look in BOL.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply