locked
Need to shrink huge log file RRS feed

  • Question

  • Hi, 

    Have a database which is published using transactional replication.  The replication was broken yesterday due to a restore.  In order to try and fix this I issued  the "EXEC sp_replrestart" command and left it running, unfortunately it has now filled up the disk the log sits creating a 250GB file. 

    Getting this error: 

    Msg 9002, Level 17, State 6, Procedure sp_replincrementlsn_internal, Line 1 The transaction log for database 'RKHIS_Live' is full. To find out why space in the log

    cannot be reused, see the log_reuse_wait_desc column in sys.databases


    I really need to free up space on this disk and shrink the log, however I can't backup the database. 

    I've not tried shrinking the files yet as I can't do a full backup.

    Any ideas? 

    I don't care about replication at this point and will happily ditch it if it gets me out of this situation. 

    Thanks 

     

    Tuesday, March 24, 2015 8:22 AM

Answers

All replies

  • Tuesday, March 24, 2015 8:33 AM
  • Just in case the other code doesn't work, have you tried creating a secondary log file on another disk?

    This should let the log finish doing what it needs to, you can run a backup and then shrink as required after.

    Tuesday, March 24, 2015 8:38 AM
  • Select name, log_reuse_wait_desc from sys.databases
    If you run the above query now against the database in question then you shuold get 'Replication'

    If that is the case, As long as replication issue is not fixed you wont be able to shrink the box. Run the above command given by Dave_gona or just remove replication and then you could shrink the file.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, March 24, 2015 9:05 AM
  • I disabled replication at the subscriber and then the publisher and disabled all the agent jobs. 

    Then I shrank the database and files to 1GB.  Phew, database is functioning just fine.  

    Need a solution to this.  Problem is the published database (managed by a 3rd party) is backed up, worked on and then restored as part of their software upgrade procedure.  In this case there is bound to be discrepency between the transactions in the log.   

    Learned a valuable lesson regarding sp_replrestart today though :-( 

    Tuesday, March 24, 2015 9:07 AM
  • You had only 2 options either you use

    sp_repldone this would mark all pending transactions as completed in blocked log file thus releasing log space. Then could have reinitialize.

    or disable replication and now you will have to redo replication.

    Tuesday, March 24, 2015 9:17 AM
  • You will need to run:

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

    Hi, 

    Thanks for the info.  I issued the sp_repldone without any arguments which I think caused a problem, it tried to create log entries to catch up.  Looks like using the command with the arguments you have posted "fools" replication into thinking it's up to date. 


    Tuesday, March 24, 2015 10:28 AM
  • You had only 2 options either you use

    sp_repldone this would mark all pending transactions as completed in blocked log file thus releasing log space. Then could have reinitialize.

    or disable replication and now you will have to redo replication.

    Picked the wrong one, now I have to redo the replication.  Still, it's a lesson learned.  
    Tuesday, March 24, 2015 10:38 AM