January 7, 2009 at 1:42 pm
Perry Whittle (1/7/2009)
ALZDBA (1/7/2009)
Truncation usually occurs after each checkpoint but can be delayed under some conditionsthis is the key here, it should and generally does but there can be instances when it doesn't happen.
The advice also states
Therefore, under the simple recovery model, the backup intervals should be short enough to prevent the loss of significant amounts of data. However, the intervals should be long enough to keep the backup overhead from affecting production work. Including differential backups in the backup strategy can help reduce the overhead
as backup also issues the log truncation. Truncation occurs at a few different points (sql server shutdown for example)
am i reading too much into it?
BOL describes the scenarios and enviroments where one might use simple recovery mode.
The key part is, the transaction log file needs to be able to support at least a single full transactions length of log data (auto extend yes/no).
Cleanup of logfile content may occur at different moments or events from the sqlserver engine.
Still the log data is needed in case sqlserver crashes of in case of power failure of the server only to be able to restore the database into a consistent state (redo all transactions since the last system checkpoint (because a checkpoint writes dirty pages to disk) and undo any uncommitted transactons).
Regarding differential backups vs log backups:
- keep in mind differential backup also need full IO of your db/filegroup/file to which you issue the backup statement.
- another trade off is the time you need to recover all log-backups, vs the recover using the latest diff backup with applying the latest log-backups, both off course starting from your last full backup.
- you can trigger any backup job based on sqlserver conditions or events.
e.g. logfile 70%full ... launch log backup job
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 8, 2009 at 5:41 am
ALZDBA (1/7/2009)
Still the log data is needed in case sqlserver crashes of in case of power failure of the server only to be able to restore the database into a consistent state (redo all transactions since the last system checkpoint (because a checkpoint writes dirty pages to disk) and undo any uncommitted transactons).
indeed, and that is why BOL states
Truncation usually occurs after each checkpoint but can be delayed under some conditions
because some records inside the log may not be ready to be discarded.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 9, 2009 at 12:27 am
Besides backup issues maybe something is wrong about your application that lets the transaction log grow too fast. Maybe this article is of some use to you
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply