locked
Can replication cause transaction log to grow unchecked? RRS feed

  • Question

  • In short, can replication cause a transaction log file to grow and not be shrinkable?  Specifically:

    My database (now SQL 2005) had done fine for several years.  The transaction log would occasionally get too large, and I'd back it up and shrink it.  The "initial size" was something less than 10 MB, and so the file could shrink to a small size.

    A few months ago I set up the database as a publisher and a distributor to replicate to another server.  It's a snapshot push replication (runs continiuously, replicates once daily).  I think it's around that time that the transaction log starting growing significantly, and the "initial size" increased.  Now the "initial size" is around 12 GB, over 1000 times larger than the actual original "initial size".  Since SQL doesn't allow the transaction log file to decrease in size less than the "initial size", it won't shrink.  It doesn't seem good to have an MDF size of 3 GB and an LDF size of 13 GB.

    I tried many things to resolve this, and got help from another Microsoft SQL forum, but no solution.

    When I run dbcc shrinkfile, it generates the message "Cannot shrink log file ... because all logical log files are in use".  Is it possible that the replication is what's keeping the logical log files in use?

    Is it possible that I need to somehow disable (or completely deconfigure) the replication on this database before shrinking it?

    Is there a way to decrease the "initial size", or else to make the transaction log completely update its contents to the MDF and then to delete and recreate the LDF transaction log file?

    Thanks for the help.

    Thursday, April 14, 2011 1:23 AM

Answers

  • The answer is Yes ....basically the transaction log cannot be shrunk if there are active transactons in between .....T-Log is logically divided in to VLFs and if a VLF is active it will show a status of 2 .When in active the status is 0..now these VLFs grow from bottom to top ...There might be a situation that you see a lot of 0s but the bottom most status of the VLF is 2 and hence shrinking is not possible ..

    If your database is in Simple recovery mode & transaction log is growing :This measn that there are open trasnsactions or some agent is running and doing some stuff . Do a DBCC open tran and see if you see transactions running (also check the SPIDs for that database via sysprocesses).Once the transactions are complete you should be able to shrink the log .

    If your database is in Full recovery mode & transaction log is growing :All the steps from above + the T-Log backup is required ..

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Marked as answer by JM6 Tuesday, May 10, 2011 8:39 PM
    Thursday, April 14, 2011 8:25 AM
  • On my test servers I set up new replications, expecting to see a point at which I create a distribution database for each database being replicated.  But in the Replication Wizard when I specify that the publication server will also be the distribution server, the wizard said it'll automatically create the distribution database.  There were no options for what to name it or where to put the database files.

    A single distribution db is usually sufficient.  If you require multiple distribution db's you can configure this in Distributor Properties (right-click Replication node -> Distributor Properties).  You can name your additional distribution db's as well as specify the folders for database and log files.

    Also, the Configure Distribution Wizard definitely has a page that lets us specify the name of the distribution db as well as the name and location of the distribution database and log files.  Are you sure there were no options?  I'm looking at them right now.

    But actually, I don't see any database that the wizard automatically created.  There's a "distrubution" system database, but it doesn't have any of the right tables, so I assume that database is just part of maintaining the replication.

    The distribution database is a system level db.  So the one that you saw.. that's it.

    If the wizard did its part correctly, would there be a distribution database visible within SQL Management Studio?

    It is visible, examine Databases -> System Databases.

    Hope this helps.


    sqlrepl.com
    • Proposed as answer by Alex Feng (SQL) Tuesday, May 3, 2011 7:48 AM
    • Marked as answer by JM6 Tuesday, May 10, 2011 8:44 PM
    Sunday, May 1, 2011 4:01 PM

All replies

  • The answer is Yes ....basically the transaction log cannot be shrunk if there are active transactons in between .....T-Log is logically divided in to VLFs and if a VLF is active it will show a status of 2 .When in active the status is 0..now these VLFs grow from bottom to top ...There might be a situation that you see a lot of 0s but the bottom most status of the VLF is 2 and hence shrinking is not possible ..

    If your database is in Simple recovery mode & transaction log is growing :This measn that there are open trasnsactions or some agent is running and doing some stuff . Do a DBCC open tran and see if you see transactions running (also check the SPIDs for that database via sysprocesses).Once the transactions are complete you should be able to shrink the log .

    If your database is in Full recovery mode & transaction log is growing :All the steps from above + the T-Log backup is required ..

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Marked as answer by JM6 Tuesday, May 10, 2011 8:39 PM
    Thursday, April 14, 2011 8:25 AM
  • Where do I look to see the 0 and 2 status for the VLFs?  My guess is there's only the active VLF, and it's 12 GB.

    I ran dbcc opentran, which gave this:

      Replicated Transaction Information:
        Oldest distributed LSN: (0:0:0)
        Oldest non-distributed LSN:  (252852:174:1)

    There were no open transactions.  But does this indicate that the replication isn't completing correctly, with the oldest non-distributed LSN not being 0:0:0?  The subscription database is up to date, having the correct number of table records and all current data.

    Thanks for the help.

    Thursday, April 14, 2011 10:11 AM
  • Where do I look to see the 0 and 2 status for the VLFs?  My guess is there's only the active VLF, and it's 12 GB.

    I ran dbcc opentran, which gave this:

      Replicated Transaction Information:
        Oldest distributed LSN: (0:0:0)
        Oldest non-distributed LSN:  (252852:174:1)

    There were no open transactions.  But does this indicate that the replication isn't completing correctly, with the oldest non-distributed LSN not being 0:0:0?  The subscription database is up to date, having the correct number of table records and all current data.

    Thanks for the help.


    use dbcc loginfo in the database to find the status of VLFs ..

    Oldest Distributed transaction is 0 .this means that either this is the first time the log reader agent has started or someone manually ran sp_repldone in the past ..anyway , you can see that there is a transaction showing up which is not replicated (252852:174:1) .In HEX this is  (0x003DBB4:000000AE:0001) . Now you can do sp_showreplcmds to see the command in publisher DB... if it has gone to Dist and you want to see if it exists on Distribution DB then you need to use sp_browserplcmds '<xcat_seqno from sp_showreplcmds>'

     

    regards

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Thursday, April 14, 2011 12:08 PM
  • Yes, it can - however from your posts in the disaster recovery forum we know that this database initially had its log file sized at 12 Gigs. There is no way you can shrink it in this case.

     

    From my response to you there:

     

    This does indicate that the log reader agent has transactions to pick up. However, what message is your log reader agent giving you? Is it saying idle or no replicated transactions available?

    If so, there is something lingering in your replication subsystem, that can only be solved by dropping your publications, and subscriptions and disabling CDS.

    HOWEVER - you told us earlier that your log file was initialy sized at 12 Gigs. If this is the case, there is no way you can shrink the size of the database below this limit.

     


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, April 14, 2011 12:13 PM
    Answerer
  • I don't have separate databases for publication and distribution.  This is a small system, so the publication and distribution are handled by the same server.  Should there be two distinct databases anyway for publication and distribution?

    Running dbcc loginfo shows the first 457 transactions with a status of 2, and the last 25 transactions with a status of 0.  The recovery model is currently set to 'Simple'.  Shouldn't the transaction be immediately integrated to the MDF?

    If I switch the recovery mode to 'Full' and then run a backup on the transaction log, should that clear out the transactions so that the status is 0 for all of them?  I've tried that already, yet the transaction log remains unshrinkable.

    Friday, April 15, 2011 1:41 AM
  • Actually what I was trying to say is that the "initial size" property is now at 12 GB.  The original "initial size" when the database was created was a small fraction of that.  Sorry for the confusion.

    Are you referring to the synchronization status?  It says "No replicated transactions are available".

    Please tell me what CDS is.  I'll remove and re-add the replication if necessary, but I need to know what to do differently so I don't just end up with the same problem again.

    Friday, April 15, 2011 1:52 AM
  • Switching to full recovery - will not change the log reuse wait, which is replication.  For some reason, replication is not clearing those transactions and the only way to clear those is to either get replication to mark them as reusable, or to completely remove replication.

    My guess is that you have a partial transactional replication set up.  You really need to remove replication completely - which may mean setting up a small publication for transactional replication and then removing it.  Once it has all been removed, execute sp_repl_done (lookup the parameters in books online).

    Once that is done, you should be able to run DBCC OPENTRAN and see no open transactions and DBCC LOGINFO and see that the VLF's have been marked as reusable.  Once that is done, run a DBCC SHRINKFILE(logical file, target_size) to shrink the log file.

    Once you have everything working again, then setup your snapshot replication again.


    Jeff Williams
    Friday, April 15, 2011 2:08 AM
  • I don't have separate databases for publication and distribution.  This is a small system, so the publication and distribution are handled by the same server.  Should there be two distinct databases anyway for publication and distribution?

    Running dbcc loginfo shows the first 457 transactions with a status of 2, and the last 25 transactions with a status of 0.  The recovery model is currently set to 'Simple'.  Shouldn't the transaction be immediately integrated to the MDF?

    If I switch the recovery mode to 'Full' and then run a backup on the transaction log, should that clear out the transactions so that the status is 0 for all of them?  I've tried that already, yet the transaction log remains unshrinkable.


    the DB is in simple recovery and you see a lot of 0s at the bottom . You also see undistributed replication transactions ...So the only thing left is the replicated transactions in the Publisher or Distributor whic hare left un replicated ...Publisher database and distributor database has to be different even if in the same instance of SQL Server ....

    I have few questions for you :

    1) What does the log reader agent and distributor show you ? Check the errors in the MSrepl_errors in the dist database and see if there are any latest errors ...

    2) Is the replication working fine even the space is growing ...

    3) what does sp_showreplcmds shows you .It shoud show you the command that is being fired and pending .

    So all you need is to find the pending repl transaction and see why its not replicated .it can have a lot of commands in it .if you get no clue then you can mark this transaction as replicated by running sp_repldone at the publisher and do the manual sync of PUB and SUB by using tablediff .Once the PUB and SUB are in sync restart your Log reader and dist agents ..


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Friday, April 15, 2011 4:00 AM
  • yes obviously.

    If log reader agent fail then transaction log gets full.

    Internally what happens log reader agent marks what are the transactions are not replicated from publisher to subscriber.

    To find what are the transactions not replicated it issues on stored procedure and it marks once that transactions replicated to subscriber again the log reader agent issues one more stored procedure (sp_repldone) telling that finished.

    before log reader agent issuing the sp_repldone if you try to shrink the log file it raises an error.

    Once the log reader agent issue sp_repldone then only you can shrink the log file.

    One more think manually you cant issue sp_repldone.

     

    In case log reader fails to mark the transactions then it will cause transaction log full.

    Friday, April 15, 2011 8:22 AM
  • Thanks to several people for providing helpful information.

    Abhay Chaudhary said "Publisher database and distributor database has to be different even if in the same instance of SQL Server".  That has to be the basic problem with my setup.  I didn't understand this from the documentation I read before setting this up.

    Without a distribution database, it makes sense that the publisher database wouldn't mark its transactions as having been replicated.  Probably a snapshot replication is the only type of replication that would work in this situation.  And it does work -- all the data is making its way to the subscription database.

    I'll try this on my test server first, and will let you know if this problem is resolved.

    Friday, April 15, 2011 8:04 PM
  • Thanks to several people for providing helpful information.

    Abhay Chaudhary said "Publisher database and distributor database has to be different even if in the same instance of SQL Server".  That has to be the basic problem with my setup.  I didn't understand this from the documentation I read before setting this up.

    Without a distribution database, it makes sense that the publisher database wouldn't mark its transactions as having been replicated.  Probably a snapshot replication is the only type of replication that would work in this situation.  And it does work -- all the data is making its way to the subscription database.

    I'll try this on my test server first, and will let you know if this problem is resolved.


    JM ,Irrespective of replication topology the publication db and dicetibution DB will never be same .If you run a query against sysdatabases and check the category value , you will get the intiger value . if its 16 its a distribution DB  if its 1 or 2 then its a subscriber or publication DB ...check it out in your enviornment ...and also check the distribution properties by right clicking replication and selecting the distributor properties ...it should show you distribution database name ...sp_helpdistributor in master ot publisher DB...
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Saturday, April 16, 2011 5:10 AM
  • I finally got back to this.  On my test servers I set up new replications, expecting to see a point at which I create a distribution database for each database being replicated.  But in the Replication Wizard when I specify that the publication server will also be the distribution server, the wizard said it'll automatically create the distribution database.  There were no options for what to name it or where to put the database files.

    But actually, I don't see any database that the wizard automatically created.  There's a "distrubution" system database, but it doesn't have any of the right tables, so I assume that database is just part of maintaining the replication.

    If the wizard did its part correctly, would there be a distribution database visible within SQL Management Studio?

    Thanks.

    Saturday, April 30, 2011 12:25 AM
  • On my test servers I set up new replications, expecting to see a point at which I create a distribution database for each database being replicated.  But in the Replication Wizard when I specify that the publication server will also be the distribution server, the wizard said it'll automatically create the distribution database.  There were no options for what to name it or where to put the database files.

    A single distribution db is usually sufficient.  If you require multiple distribution db's you can configure this in Distributor Properties (right-click Replication node -> Distributor Properties).  You can name your additional distribution db's as well as specify the folders for database and log files.

    Also, the Configure Distribution Wizard definitely has a page that lets us specify the name of the distribution db as well as the name and location of the distribution database and log files.  Are you sure there were no options?  I'm looking at them right now.

    But actually, I don't see any database that the wizard automatically created.  There's a "distrubution" system database, but it doesn't have any of the right tables, so I assume that database is just part of maintaining the replication.

    The distribution database is a system level db.  So the one that you saw.. that's it.

    If the wizard did its part correctly, would there be a distribution database visible within SQL Management Studio?

    It is visible, examine Databases -> System Databases.

    Hope this helps.


    sqlrepl.com
    • Proposed as answer by Alex Feng (SQL) Tuesday, May 3, 2011 7:48 AM
    • Marked as answer by JM6 Tuesday, May 10, 2011 8:44 PM
    Sunday, May 1, 2011 4:01 PM
  • Thanks, this information all helps.  It works correctly on my test system but not on my active database.  I'll probably remove the replication and then set it up again.
    Tuesday, May 10, 2011 8:47 PM