Post reply

Best practice for truncating transaction log

  • March 27, 2006 at 8:21 am

    #168321

    Hi

    I am relatively new to SQL Server so please bear with me on this question.  I am looking to find out the best way to get the transaction log truncated when the active part of the log is shown via the DBCC LOGINFO & DBCC SQLPERF (LOGSPACE) is at the end of the file.

    I know I need to force a checkpoint but what's the best way to do this?

    Thanks

     

  • March 27, 2006 at 8:39 am

    #628950

    Ronnie

    Forcing a checkpoint isn't the same as truncating the transaction log.  A checkpoint is when data from the transaction log is committed to the database.  The transaction log will be automatically truncated at this point if the database is in Simple recovery mode.  If it's in Full or Bulk Logged mode then you need to do it manually - or automate the process.  You can do it via Enterprise Manager or with a query like this: BACKUP LOG YourDB TO DISK='path_to_backup_file' (or WITH TRUNCATE_ONLY if you don't want to keep the log backup).

    If you want to automate this process then you can create a job that runs when a certain performance condition is met, that condition being that the transaction log is more than 80%, say, full.  You should be able to find info on how to set up such a job in Books Online.

    John

  • March 28, 2006 at 2:04 am

    #629097

    John

    Thanks for the reply.  I have obviously not made the issue I have clear so here goes again.  If I have a transaction log that is 5GB in size and only shows via Enterprise Manager that there is 500mb used.  If I attempt to shrink the transaction log using DBCC Shrinkfile (<logfilename>,<new size&gt then I am unable to do so as the active portion of the transaction log is at the very end of the log.

    What I need to do is force a checkpoint so that the active portion of the log rolls over to the beginning of the file and this should hopefully allow me to resize the logfile.

    What is the best way to force a checkpoint to allow this to happen.

    Ronnie

  • March 28, 2006 at 2:32 am

    #629098

    Ronnie

    I see what you're asking.  You should use the CHECKPOINT command to issue a checkpoint.  This is more likely to be successful during a quiet period since there will be fewer long-running transactions (and obviously you don't want to do it while jobs such as reindexing or DTS transfers are running either).  The topic Checkpoints and the Active Portion of the Log in Books Online has some useful information.

    Hope that helps

    John

Viewing 4 posts - 1 through 3 (of 3 total)

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