November 26, 2007 at 3:36 pm
In terms of raw performance is simple recovery mode any faster than full recovery mode? Let's not consider the extra time to backup the Tlog. My understanding is that the same amount of logging will take place in either mode. Your just going to use up more space.
November 26, 2007 at 3:49 pm
Which one you use (Simple or Full recovery mode) depends on your restore requirements. Using simple recovery mode, you can only complete full or differential backups. Using the full recovery mode, you add transaction log backups and the ability to restore your database to a specified point in time.
We have done this in our development environment to help identify and correct errors in our production systems. I havene't had to do this in a production environment yet, which I am glad of actually.
😎
November 26, 2007 at 4:38 pm
It is also worth remmebering that the mode you chose and the backup strategy you implement will affects how often checkpoints are issued. Which can affect how long SQL Server takes to recover in the event of a system failure.
Gethyn Elliswww.gethynellis.com
November 27, 2007 at 5:42 am
The answer to your questions is basically "No". Even in simple recovery mode, MS SQL writes to the transaction log. In simple recovery mode, it just truncates the log file every time there is a checkpoint so the transaction log file does not grow. All of the overhead of writing to the log file is still there.
It could be argued that as the transaction log grows,, the requests to the OS for more disk space takes additional processing power and drive access, but it is usually not enough to care about.
November 27, 2007 at 7:16 am
Michael Earl (11/27/2007)
The answer to your questions is basically "No". Even in simple recovery mode, MS SQL writes to the transaction log. In simple recovery mode, it just truncates the log file every time there is a checkpoint so the transaction log file does not grow. All of the overhead of writing to the log file is still there.It could be argued that as the transaction log grows,, the requests to the OS for more disk space takes additional processing power and drive access, but it is usually not enough to care about.
Agreed
November 27, 2007 at 4:38 pm
Rob (11/27/2007)
Michael Earl (11/27/2007)
The answer to your questions is basically "No". Even in simple recovery mode, MS SQL writes to the transaction log. In simple recovery mode, it just truncates the log file every time there is a checkpoint so the transaction log file does not grow. All of the overhead of writing to the log file is still there.It could be argued that as the transaction log grows,, the requests to the OS for more disk space takes additional processing power and drive access, but it is usually not enough to care about.
Agreed
On the other hand, since full DOESN't truncate the minute it's finished with something, you might have some options to find a "slow" time to do the truncate (i.e. in the middle of the night after the backup). So - you might end up with less disk activity at your busy time of the day, which is then "spread" to a slower time, especially if you have your logs sized correctly (so that they don't have to grow....).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 28, 2007 at 2:47 am
Simple recovery does not truncate the log at every checkpoint. SQL will always, regardless of recovery mode, keep track of the oldest active transaction. The LRSN of the oldest transaction will relate to a logical log file (see BOL for what this means).
When the current oldest active transaction ends, SQL establishes a new oldest LRSN. If the new LRSN is in a different logical log file to the previous LRSN, SQL releases the previous logical log file (and any intermediate logical log files) for further processing.
Now comes the difference between Simple and Full recovery...
If you are in Simple recovery, the released logical log files are put in the re-use list.
If you are in Full or Bulk Logged recover, the released logical log files are put in a backup-pending status.
Both of the above operations take next to no time to complete, so there is no processing overhead at this point between Simple, Bulk-logged and Full recovery.
Next, if you are in Bulk-logged or Full recovery, at some point you will do a log backup. As each logical log file in pending status is backed up, it is reset to re-usable. The only extra processing overhead for using Full compared to Simple is the time taken to do the log backups. With Bulk-logged, you save some processing time compared to both Full and Simple when you are doing bulk insert operations - BOL has details of this.
The log backup will process all logical log files that are in backup pending or in-use state. If you do multiple log backups during a time when the oldest LRSN has not changed, you will back up the active logical logs multiple times.
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
November 28, 2007 at 8:12 am
I am going to go with Matt here in that on a very busy system with off-hours slow time full recovery mode can allow optimal performance during the peak time by not having the tlog flushed automatically in Simple recovery mode. This assumes that you tlog is sized appropriately and you don't get autogrowths during peak time, btw. But everyone should already have that done on their systems anyway.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 30, 2007 at 6:35 pm
"Minimally logged operations" such as BULK INSERT, BCP.EXE and CREATE INDEX operate as fully logged operations in Full Recovery Mode, but are minimally logged in Simple and Bulk-Logged Recovery Mode. So if you have a read-only reporting database that is periodically bulk loaded, these types of operations will be much faster and use much less transaction log space.
If you go this route, I would recommend having a full backup of your database available before beginning the loading process. If something goes wrong during the process, your only real option is a full restore.
David Lathrop
DBA
WA Dept of Health
November 30, 2007 at 7:36 pm
Just a suggestion...
If you regularly load large amounts of data using BCP or Bulk Insert, consider making a "staging area" database. Because all data in a staging area should be considered expendable and reproducible from external sources, you should be able to set a "staging area" database to the Simple Recovery Mode without fear. Also, because the data is expendable, there is no need to do data-backups on "staging area" databases nor is there a need to do deletes. Truncates or even drops of tables would be the way to go.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply