x

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

Note:

I already studied but cant understood below article

http://msdn.microsoft.com/en-us/library/ms345414%28v=SQL.90%29.aspx

more ▼

asked Mar 14, 2013 at 12:58 PM in Default

avatar image

askmlx121
2.5k 72 76 83

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Mar 14, 2013 at 02:06 PM

avatar image

tanglesoft
1.8k 4 6 11

yes tanglesoft & cyborg both are right........

Mar 14, 2013 at 02:38 PM askmlx121
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 14, 2013 at 02:15 PM

avatar image

Cyborg
10.8k 37 55 51

Hi I am using Full Recovery Model.

So can I issue CHECKPOINT command in SQL.

If I execute what would happen???????????

Mar 15, 2013 at 09:47 AM askmlx121
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 15, 2013 at 10:15 AM

avatar image

tanglesoft
1.8k 4 6 11

ok thankxxxxxxxx

Mar 15, 2013 at 11:54 AM askmlx121
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x8

asked: Mar 14, 2013 at 12:58 PM

Seen: 3808 times

Last Updated: Mar 15, 2013 at 11:54 AM

Copyright 2017 Redgate Software. Privacy Policy