none
Logfile too big!!! RRS feed

  • Question

  • I have a database (Recovery Model - Simple) on SQLServer-2012. This database is replicated with Push subscription using the Snapshot Replication. The log file is 150GB whereas the database is 10GB. I tried various methods to truncate/shrink the logfile/database however none worked. 

    -- I took the full backup in Simple Recovery model and tried to truncate
    -- I changed the model from Simple to Full took both Full as well as Transaction log backup then tried to truncate the log

    Please suggest how I can reduce the size of the Logfile.

    R
    Tuesday, March 11, 2014 12:33 PM

Answers

  • Hi,

    In Simple recovery model logs are truncated when the check points occurs. They are few chances why the log file grows hugely and this might be because of bulk inserts. How ever to overcome this follow below steps:

    1) Keep the database recovery model to Simple before you shrink the log file.

    2) Execute "checkpoint" manually if necessary (Optional)

    3) use databasename
    go
    DBCC SHRINKFILE ('logicallogfilename', 4000 , TRUNCATEONLY)

    To get database logical log file name execute "sp_helpfile" on the required database. (Note: Use database Logical log file name)

    I believe, since you are using snapshot replication no replication set-up will be disturbed by shrinking the log file. But please avoid shrinking the log file as a routine task. And also please let us know the status once you are done with the above steps.



    Grateful to your time and support. Regards, Shiva


    Tuesday, March 11, 2014 12:59 PM
  • Use <dbname>

    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1


    I got 

    Command(s) completed successfully.

    • Marked as answer by SQLServer2012 Tuesday, March 11, 2014 1:45 PM
    Tuesday, March 11, 2014 1:25 PM

All replies

  • Hello,

    What is "log_reuse_wait_desc" returning for that database?

    SELECT name, log_reuse_wait_desc
    FROM sys.databases


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, March 11, 2014 12:48 PM
    Moderator
  • SELECT name, log_reuse_wait_desc FROM sys.databases

    Returns

    <dbname> Replication

    Tuesday, March 11, 2014 12:53 PM
  • Hi,

    In Simple recovery model logs are truncated when the check points occurs. They are few chances why the log file grows hugely and this might be because of bulk inserts. How ever to overcome this follow below steps:

    1) Keep the database recovery model to Simple before you shrink the log file.

    2) Execute "checkpoint" manually if necessary (Optional)

    3) use databasename
    go
    DBCC SHRINKFILE ('logicallogfilename', 4000 , TRUNCATEONLY)

    To get database logical log file name execute "sp_helpfile" on the required database. (Note: Use database Logical log file name)

    I believe, since you are using snapshot replication no replication set-up will be disturbed by shrinking the log file. But please avoid shrinking the log file as a routine task. And also please let us know the status once you are done with the above steps.



    Grateful to your time and support. Regards, Shiva


    Tuesday, March 11, 2014 12:59 PM
  • I ran the suggested command

    use databasename

    go

    DBCC SHRINKFILE ('logicallogfilename', 4000 , TRUNCATEONLY)

    I got this

    Cannot shrink log file 2 (******_log) because the logical log file located at the end of the file is in use.

    (1 row(s) affected)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Tuesday, March 11, 2014 1:16 PM
  • Returns Replication

    Maybe one of the subscription is broken and so the database engine wait with log release until the subscriber is up to date again; so check all subscribers.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, March 11, 2014 1:18 PM
    Moderator
  • When xactid is NULL, xact_seqno is NULL, and reset is 1, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log, for example:

    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

    According to your earlier post I believe the transactions are marked for replication so execute the above command on the required database (publishing database). And please let us know the status.


    Grateful to your time and support. Regards, Shiva

    Tuesday, March 11, 2014 1:20 PM
  • Use <dbname>

    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1


    I got 

    Command(s) completed successfully.

    • Marked as answer by SQLServer2012 Tuesday, March 11, 2014 1:45 PM
    Tuesday, March 11, 2014 1:25 PM
  • Now try to shrink the log file as suggested in the above steps earlier.

    Grateful to your time and support. Regards, Shiva

    Tuesday, March 11, 2014 1:42 PM
  • It worked...

    Thanks a lot

    Tuesday, March 11, 2014 1:45 PM
  • As you told you are running snapshot replication with push subscription. You can run the snapshot agent at your requirement.

    Grateful to your time and support. Regards, Shiva

    Tuesday, March 11, 2014 1:53 PM
  • As you told you are running snapshot replication with push subscription. You can run the snapshot agent at your requirement.

    Grateful to your time and support. Regards, Shiva

    Snapshot happens nightly. Thanks for your help Shiva. 
    Tuesday, March 11, 2014 1:54 PM