SQL Server Performance

Log file fails to truncate

Discussion in 'SQL Server 2005 General DBA Questions' started by davidfarr, Feb 7, 2008.

  1. davidfarr Member

    I have a SQL Server 2005 instance that hosts a database for Microsoft Sharepoint used internaly by our company staff.
    The log file is approaching 11GB in size and appears to permanently be 99% full.
    The database is in simple recovery mode.
    Forcing a checkpoint appears to have no effect.
    There are no integrity errors and no open transactions.
    There are no file growth or size restrictions and plenty of available disk space for both files.
    At 99% full, I am unable to shrink the log file or find an explanation of why it never truncates.
    I would be happy if anyone has a suggestion to offer.
    A more detailed listing of my diagnostic queries and results are below:
    EXEC sp_helpdb SharepointContent
    GO
    Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Latin1_General_CI_AS_KS_WS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
    File Type Size Max Size Growth Usage
    Data 13470912 KB Unlimited 1024000 KB data only
    Log 11000064 KB 2147483648 KB 102400 KB log only

    USE SharepointContent
    GO
    CHECKPOINT
    GO
    DBCC UPDATEUSAGE (0)
    GO
    DBCC SQLPERF (LOGSPACE)
    GO
    Name Log Size (MB) Log Space Used (%) Status
    SharepointContent 10742.242 99.397751 0
    DBCC OPENTRAN
    GO
    Transaction information for database 'SharepointContent'.
    Replicated Transaction Information:
    Oldest distributed LSN : (0:0:0)
    Oldest non-distributed LSN : (21543:3418:1)
    DBCC CHECKDB
    GO
    CHECKDB found 0 allocation errors and 0 consistency errors in database 'SharepointContent'.


  2. satya Moderator

    The reason for not shrinking as the Sharepoint process is using the tlog file all the times as the virtual log space is not truncated. In this case better to stop the application & then try shrinking.
    Also state why the database recovery model has been kept in SIMPLE?
  3. davidfarr Member

    Thanx for the suggestion, I will see what I can achieve with the application stopped.
    I dont really have a good answer why the simple recovery model was implemented, I could ascribe it to "company politics". A full backup is made every 24 hours affording us at least some measure of recovery. Admittedly it would be both easy and preferable to have a full recovery model....

  4. satya Moderator

    Tell them that its a risk for having SIMPLE recovery model where the data recoverability is not possible with point in time restore, it can only achieve by performing periodic transaction log backups in this case.
    So unless you control the virtual log size it is hard to shrink or reduce the physical size, read through Books online for Transaction log architecture for more information.
  5. davidfarr Member

    I have been able to revisit this problem;
    The result from DBCC OPENTRAN below:
    Transaction information for database 'SharepointContent'.
    Replicated Transaction Information:
    Oldest distributed LSN : (0:0:0)
    Oldest non-distributed LSN : (21543:3418:1)
    This indicates that the database was previously configured for replication and that the log file will not truncate until outstanding transactions have been written to the distribution database.
    Since replication is not established or required on the current database and the distribution database does not exist; solving this dilemma required the following commands:
    Execute SP_ReplicationDbOption SharepointContent,Publish,true,1
    GO
    Execute sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
    GO
    DBCC ShrinkFile(<SharepointLogFileName>,0)
    GO
    Execute SP_ReplicationDbOption SharepointContent,Publish,false,1
    GO
    I hope the above solution may assist future members that encounter this problem.
  6. satya Moderator

    Glad to see this solution, thanks for sharing it.
  7. mareeds New Member

    Very helpful post David. It certainly solved my 6-week old problem, although I must say I know more about replication and fn_dblog than most of my friends.
    Mike
  8. sentitus New Member

    Thanks davidfarr, had the same problem....great post!!
  9. RamaUdaya.K New Member

    Hi,
    Check out the DBCC LOGINFO and find out is there any Virtual log file is active or not if active you can't shrink the log file at any time i,e you can see that output of dbcc loginf i,e you can see the status column,if the status cloumn is 2 then virtual log file is active and if 0 the virtual log file is inactive...
    Thanks,
    Your's
  10. satya Moderator

    Rama
    Appreciate your post here, but you see the solution has been posted by OP already.

Share This Page