June 28, 2008 at 12:41 am
i have alter the recovery interval (min) time from default value to 15 min but it is not happening at specified interval . can anyone explain about this ?
June 28, 2008 at 12:47 am
Keep in mind :
(from BOL)
The recovery interval option does not affect the time it takes to undo long-running transactions. For example, if a long-running transaction takes two hours to perform updates before the server becomes disabled, the actual recovery takes considerably longer than the recovery interval value to roll back the long transaction.
SQL Server estimates how many data modifications it can roll forward in the recovery time interval. SQL Server typically issues a checkpoint in a database when the number of data modifications made in the database after the last checkpoint reaches the number SQL Server estimates it can roll forward in the recovery time interval. Sometimes SQL Server will issue the checkpoint when the log becomes 70 percent full, if that is less than the estimated number.
The frequency of checkpoints in each database depends on the amount of data modifications made, not on any time-based measure. A database used primarily for read-only operations will not have many checkpoints. A transaction database will have frequent checkpoints.
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
June 28, 2008 at 1:16 am
Auto matic check point doing the process frequently when am launching the big reports so i/o bottel neck problem coming ,to avoid such a problem i want stop Auto check point process. thats why i have modified the recovery interval time albeit its not working at specified interval time . Any way i am taking the backup for every 15 mintues that time it will do the check point process. can you give me suggestion whether will it increase performance or decrease porf ?
June 30, 2008 at 12:10 am
IMO having a higher checkpoint frequency will minimize its impact to the system.
It takes a checkpoint because it has a high update load (worktables, ... ??)
maybe (re) moving the worktables to a separate database can help solving your problems.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply