Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2007
    Posts
    13

    Unanswered: how to check whether transaction log is getting truncated

    Hi,
    Whether anyone can provide a solution to check whether transaction log is getting truncated or not. I tried with the follwing commands :

    > sp_helpsegment logsegment

    The output showed :
    ---------------------------------------------------------------
    ---------------------------------------------------------------
    1> sp_helpsegment logsegment
    2>
    segment name status
    ------- ------------------------------ ------
    2 logsegment 0
    device size
    ------------------------------ ----------------------
    d_comlog0 200.0MB
    free_pages
    -----------
    101999
    table_name index_name indid
    ------------------------------ ------------------------------ ------
    syslogs syslogs 0
    total_size total_pages free_pages used_pages
    ---------------------- ----------- ----------- -----------
    200.0MB 102400 101999 401
    (return status = 0)

    ************************************************** *
    ************************************************** *

    I did some modifications to the database and again ran the same command. But there was no change in the output.

    So, please if anyone can provide with the soln.

    Regards,
    Goraksh

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I assume the following database option is set "trunc log on chkpt"
    Chances are that after your update a checkpoint truncated the log and you see the same free space.

    Other ways to check the log free space:
    select lct_admin("logsegment_freepages",db_id())
    dbcc checktable (syslogs)

    Check to see if the log pointer is moving on
    select logptr from master..sysdatabases where dbid=db_id())

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •