Unexpected growth Transaction log file 100 GB which is part of Transactional Replication












0















I configured transactional replication on SQL Server 2014/Windows Server 2012. The database size is only 30 MB, but log file size is 100 GB. Every day, the log file size is growing 5-7 GB.



Database transaction log file size increased abnormally. Data file size is 30MB and the transaction log file grows to 95 GB.



While database under testing mode...



Subscriber of replication is also primary replica of Always On Availability Group.



How to reduce the size of transaction log file?



I had taken log backup and in full recovery model.



SELECT name, log_reuse_wait_desc FROM sys.databases;


...returns LOG_BACKUP.



Replication is running successfully. Subscriber is receiving changes from publisher.










share|improve this question
















bumped to the homepage by Community 4 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • Please can you post the output of sp_replcounters on the publisher, thanks.

    – T.H.
    Feb 15 '17 at 9:09
















0















I configured transactional replication on SQL Server 2014/Windows Server 2012. The database size is only 30 MB, but log file size is 100 GB. Every day, the log file size is growing 5-7 GB.



Database transaction log file size increased abnormally. Data file size is 30MB and the transaction log file grows to 95 GB.



While database under testing mode...



Subscriber of replication is also primary replica of Always On Availability Group.



How to reduce the size of transaction log file?



I had taken log backup and in full recovery model.



SELECT name, log_reuse_wait_desc FROM sys.databases;


...returns LOG_BACKUP.



Replication is running successfully. Subscriber is receiving changes from publisher.










share|improve this question
















bumped to the homepage by Community 4 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • Please can you post the output of sp_replcounters on the publisher, thanks.

    – T.H.
    Feb 15 '17 at 9:09














0












0








0


1






I configured transactional replication on SQL Server 2014/Windows Server 2012. The database size is only 30 MB, but log file size is 100 GB. Every day, the log file size is growing 5-7 GB.



Database transaction log file size increased abnormally. Data file size is 30MB and the transaction log file grows to 95 GB.



While database under testing mode...



Subscriber of replication is also primary replica of Always On Availability Group.



How to reduce the size of transaction log file?



I had taken log backup and in full recovery model.



SELECT name, log_reuse_wait_desc FROM sys.databases;


...returns LOG_BACKUP.



Replication is running successfully. Subscriber is receiving changes from publisher.










share|improve this question
















I configured transactional replication on SQL Server 2014/Windows Server 2012. The database size is only 30 MB, but log file size is 100 GB. Every day, the log file size is growing 5-7 GB.



Database transaction log file size increased abnormally. Data file size is 30MB and the transaction log file grows to 95 GB.



While database under testing mode...



Subscriber of replication is also primary replica of Always On Availability Group.



How to reduce the size of transaction log file?



I had taken log backup and in full recovery model.



SELECT name, log_reuse_wait_desc FROM sys.databases;


...returns LOG_BACKUP.



Replication is running successfully. Subscriber is receiving changes from publisher.







sql-server replication sql-server-2014 transaction-log transactional-replication






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jul 4 '17 at 13:04









Paul White

50.7k14277447




50.7k14277447










asked Feb 14 '17 at 7:07









Gulrez KhanGulrez Khan

116114




116114





bumped to the homepage by Community 4 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 4 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.















  • Please can you post the output of sp_replcounters on the publisher, thanks.

    – T.H.
    Feb 15 '17 at 9:09



















  • Please can you post the output of sp_replcounters on the publisher, thanks.

    – T.H.
    Feb 15 '17 at 9:09

















Please can you post the output of sp_replcounters on the publisher, thanks.

– T.H.
Feb 15 '17 at 9:09





Please can you post the output of sp_replcounters on the publisher, thanks.

– T.H.
Feb 15 '17 at 9:09










1 Answer
1






active

oldest

votes


















0














You may run below scripts to check the log reuse wait of the database(s):



SELECT DB_NAME(database_id) AS db_name, log_reuse_wait_desc FROM sys.databases;





share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f164243%2funexpected-growth-transaction-log-file-100-gb-which-is-part-of-transactional-rep%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    You may run below scripts to check the log reuse wait of the database(s):



    SELECT DB_NAME(database_id) AS db_name, log_reuse_wait_desc FROM sys.databases;





    share|improve this answer




























      0














      You may run below scripts to check the log reuse wait of the database(s):



      SELECT DB_NAME(database_id) AS db_name, log_reuse_wait_desc FROM sys.databases;





      share|improve this answer


























        0












        0








        0







        You may run below scripts to check the log reuse wait of the database(s):



        SELECT DB_NAME(database_id) AS db_name, log_reuse_wait_desc FROM sys.databases;





        share|improve this answer













        You may run below scripts to check the log reuse wait of the database(s):



        SELECT DB_NAME(database_id) AS db_name, log_reuse_wait_desc FROM sys.databases;






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Feb 14 '17 at 7:31









        Anson DaiAnson Dai

        112




        112






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f164243%2funexpected-growth-transaction-log-file-100-gb-which-is-part-of-transactional-rep%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh