Post reply

Create a Job to run CHECKPOINT

  • April 2, 2007 at 8:16 am

    #61293

    Hi all,

    As I am new to SQL DBA Administration, Kindly provide the information, how I go forward to create a job to run the CHECKPOINT to write all the dirty buffers to disk.

    Thanks in Advance.

    Regards

    Kumar

  • April 2, 2007 at 8:40 am

    #698102

    you just execute a "CHECKPOINT" command. But these execute every minute or so, so I'm not sure you need to. Is there something you are concerned about?

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • April 2, 2007 at 10:40 pm

    #698210

    Hi Steve,

    Thank you for your response

    We have a production server, SQL Server 2000 with SP4, in which the maintaince activities was not running before. Due to the performance issue, I started implemented the Maintenance activities like optimization, integrity check, checkDB, reindexing. while create the job, one of our client relation person, informed me to create a job to perform the Checkpoint and then to run the DBCC CLEARPROCCACHE & DBCC DROPCLEANBUFFERS.  That's Y I need to some help with SQL Guys to perform this task.

    I just ran the CHECKPOINT in the Query Analyzer, i just go the resutl "The command(s) completed successfully". I also just created the a job and ran it, I thought of need to know how this checkpoint works and what exactly it does, so outputed the text file and I dont see any information other than the job completed sucessfully.

    Can you please let me know, how I could go in depth and see what exactly it performs and what is the usage of the this.

    Thanks in advance.

    Best Regards

     

     

     

  • April 3, 2007 at 1:02 am

    #698226

    Checkpoint will only return the 'command completed successfully' There's no more details than that.

    You really don't want to run CLEARPROCCACHE & DBCC DROPCLEANBUFFERS on a production server, especially not on a regular basis. What you're forcing with that is for every proc to be recompiled next time it runs and every single piece of data to be cleared from memory and fetched again from the disk.

    Run on a prod server, they're very likely to cause a performance problem.

    I'd question the person who told you to do that to find out the easoning behind ttheir request.

    Checkpoint runs automatically on all databases, at a frequency related to the amount of activity in the db. There's usually no need to run it manually.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 3, 2007 at 1:57 am

    #698232

    For further information about CHECKPOINT, DBCC and other stuff you hear about, BOL (Books On Line) is a very good place to start where you can find explanations.

    /Kenneth

  • April 3, 2007 at 3:16 am

    #698243

    Thanks you so much.. I will look into it...

    Best Regards

     

     

  • April 3, 2007 at 7:55 am

    #698283

    The notes above make sense and I'm not sure why you'd run clear cache.

    SQL Server executes its own CHECKPOINT internally every 1-5 minutes. It doesn't make sense to regularly force your own and I've never done it in 12 years of SQL Server work. I have executed it on occassion for various reasons, like trying to quiesce the system, but never as a job.

    A checkpoint shouldn't matter for maintenance activities. If they're not working, it's sometnign else.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • April 8, 2007 at 10:39 pm

    #699264

    There is a bug where checkpoints are not issued automatically addressed

    in this KB: http://support.microsoft.com/kb/909369 , so as an alternative to obtaining the PSS hotfix or upgrading to 2187 level (which contains the fix),  the checkpoint can be scheduled to run on one minute intervals. Maybe that's what the "client relation person" was referring to...

  • April 10, 2007 at 4:07 am

    #699480

    Dasha,

    Thank you for the reference...

     

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply