September 26, 2012 at 6:26 am
log reuse wait description values is CHECKPOINT?
When checkpoint is returned for log_reuse_wait_desc it means the No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file.
can we do run manually checkpoint command in production database?
use databasename
go
checkpoint
Does anything affect in database side for performance side or not? after fired this command
Thanks
September 26, 2012 at 6:56 am
You can run check point on any database.
The performance impact of using checkpoint_duration depends on the number of dirty pages, the activity on the system, and the actual duration specified. For example, if the checkpoint would normally complete in 120 seconds, specifying a checkpoint_duration of 45 seconds causes SQL Server to devote more resources to the checkpoint than would be assigned by default. In contrast, specifying a checkpoint_duration of 180 seconds would cause SQL Server to assign fewer resources than would be assigned by default. In general, a short checkpoint_duration will increase the resources devoted to the checkpoint, while a long checkpoint_duration will reduce the resources devoted to the checkpoint. SQL Server always completes a checkpoint if possible, and the CHECKPOINT statement returns immediately when a checkpoint completes. Therefore, in some cases, a checkpoint may complete sooner than the specified duration or may run longer than the specified duration.
September 26, 2012 at 7:18 am
ananda.murugesan (9/26/2012)
log reuse wait description values is CHECKPOINT?When checkpoint is returned for log_reuse_wait_desc it means the No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file.
can we do run manually checkpoint command in production database?
use databasename
go
checkpoint
Does anything affect in database side for performance side or not? after fired this command
Thanks
Let checkpoint run on it scheduled time. If you are worried about log file growing, check whether there is any open transaction that is running from a long time? If yes, contact the person who is running that & act accordingly. If there are long running open transactions, checkpoint is not going to mark VLFs as inactive(reusable) anyway.
September 26, 2012 at 7:34 am
Is there a problem?
If not, then just ignore this, especially if the DB is in simple recovery model where that is a very normal log reuse wait.
See: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply