asked
log reuse wait for 'checkpoint'
Hi, select log_reuse_wait_desc,* from sys.databases where log_reuse_wait_desc='CHECKPOINT' I ran above query in daily basis, today i found that one database was 'check point' But i did nt understood, what is this? how to resolve this? what are the steps to cultivate this? any one is there to teach me?? ![alt text][1] Note: I already studied but cant understood below article
http://msdn.microsoft.com/en-us/library/ms345414%28v=SQL.90%29.aspx [1]: /storage/temp/719-database+logreusewait+in+checkpoint.jpg
Can be caused by a large transaction that is running or has run that has used all of the space in the transaction log preventing a checkpoint from being written. Check the log file to see how much free space is available. If set to autogrow check if there is enough disk space. In the short term if the log is 100% it needs to be increased to allow the checkpoint to be added but you need to investigate why the transaction is taken up so much space. Hope this helps.
this case log_reuse_wait_desc show ACTIVE_TRANSACTION right? Log_reuse wait shows checkpoint when there is not enough transactions happened to flush the dirty pages to disk. If you are in simple recovery model and if you experience huge log file size then i Suggest you to issue CHECKPOINT command manually to truncate the inactive VLFs. Correct me if I am wrong.
The log file is split into a number of virtual logs files one of which is active. Normally SQL server will issue it's own automatic checkpoint based on a schedule it determines unless it's been overridden. When the checkpoint is running one or more VLF will be active as the data is written to the data file. At the end of the checkpoint either a new VLF or an existing VLF will be reused waiting for more transactions. If you run your own checkpoint it carries out this process immediately rather than waiting for the schedule.
14 People are following this question.
