Link to home
Start Free TrialLog in
Avatar of Varshini S
Varshini S

asked on

Does the recovery model change create performance issue in SQL server 2017?

I have changed the recovery model to Full since the DB configured for mirroring. The DB size is 200GB. After changing to Full recovery model users feels the slowness during transaction and reports.

SQL Server version: 2017 standard

Does the recovery model change create performance issue?  I have scheduled only full back every day around 11 pm.  No transaction log back up.
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

only during the time of the backup
Since you are not performing a log backup at all, your transaction log file will grow fast and forever, and that of course hurts performance. Log backups are required in Full Recovery mode, only then old transactions get removed from it and the space reused.
Note that the log file is never truncated unless you explicitly do it. Which you shouldn't do often, unless in particular cases like after running the first log backup here, where the log file is probably way too big already.
Avatar of Varshini S
Varshini S

ASKER

Qlemo: Thank you for your suggestion. If I do a transaction log backup every 2 hrs will this problem solved? o rStill i need to truncate the log file explicitly
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
all three recovery model truncates logfiles. the difference is that only in full recovery model all transactionslogs are part of backup. that is required if you plan to recover a specified point other then the time of backups. in most cases simple recovery model is used. on this you can recover to the time of the backup only. no one of this models has side effects on operating performance. the size of logfile should be constant at required amount between two backups. it's not a good idea to reduce the size because logfiles will grow as required on normal filesystem. if this operation adds Segments to the file, logfile is fragmented and this reduces performance. you will get best Performance on continous logfiles files.
"truncate" is the wrong term, because it inckudes the reduction of size (versus just marking blocks in the file as free - for MSSQL, not the OS). File size will not change after a backup.

But yes, the recovery model in itself does not have direct impact on performance, but the side effects do.
Full recovery (vs.simple) does increase the amount of data that is logged (written to the log file).  Thus, yes, it can affect performance.

Log file growth could affect it more, but other SQL activities can also require additional logging when done in full mode.
Hi Scott sorry but this is not correct. during the transactional processing exact same data are writen to the logfile. the main difference is the processing during checkpoint. on simple recovery is no need to safe every Transaction and thats why all Transactions before the current checkpoint can be removed. this reduces amount of data inside logfile between two backups. but transactional load is absolut the same. the load on simple recovery during checkpoint Operation is a litlle bit higher but during backup Operation lower. but the difference is not remarkable high.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I've been a full-time DBA on SQL Server for 17+ years.  I know exactly how SQL's log file works.  What you're saying is not entirely accurate.

For example, this:
the checkpoint will be executed once per Minute and so you have only Transactions of max one Minute inside the transactionlog
is not all true.  A transaction will remain on the log file as long as it is active, i.e., until it is committed or rolled back, no matter how long that is.

How the checkpoint processing works has nothing to do with the volume of data written to the log by data modifications and/or index rebuilds.
Hi Scott,

it goes in wrong direction to discuss about years of expirience. i am SQL teacher and expert since 1996.

You're right that incomplete tranactions stay on transaction-log, sorry that i forgot to explain this exception. i only tried to clearify to users why load can created even of small logfiles, who do not know as much as experts.
So let us stop this discussion before we cause more confusion then help.

Many thanks and best regards.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial