none
SQLSERVER 2012 LOG BACKUP RRS feed

  • Question

  • Hi Friends,

    I want to inquire, as our sqlserver 2012 in full  recovery mode , when ever we are taking backup using netbackup , the logfile size did not shrink or reduce. My question is what is default behavior of sqlserver 2012 after taking logfile backup the logfile size should reduce or not. As the logfile size keep increase which creating problem.

    thank you.

    regards,

     


    asad

    Wednesday, March 4, 2015 6:02 AM

Answers

All replies

  • After tlog backup you have to shrink the log file to release space. Tlog backup by itself will not shrink ldf file.
    Wednesday, March 4, 2015 6:08 AM
  • Hello,

    As dave_gona already wrote, shrinking a database file is an expensive operation and SQL Server will never do it on it's own (beside the silly option "Autoshrink".

    If your log file keeps growing then please check why the VLF are not release with

    select name, log_reuse_wait_desc
    from sys.databases
    order by name


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, March 4, 2015 6:43 AM
    Moderator
  • You need to find the root cause of the log growth.

    Run the query as Olaf suggested,  once you get the output check the log_reuse_wait_desc.

    https://msdn.microsoft.com/en-gb/library/ms190925(v=sql.110).aspx#FactorsThatDelayTruncation This article describes all the different factors that causes log not to be reused and that is what is causing the increase.


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

    Wednesday, March 4, 2015 11:46 AM
  • thank you for reply, the above article having many factor, and its hard to check them all, but we are using replication , please provide me a query so I can find the cause of logfile growth.


    asad

    Wednesday, March 4, 2015 4:37 PM
  • Hi asad,

    Did you read and understood, what Olaf and I have typed above?

    Can't you see the query in Olaf's post?


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

    Wednesday, March 4, 2015 4:41 PM
  • oh yes, I executed query and its given me log_reuse_wait_desc="nothing" thank you.

    regards,

    asad


    asad

    Wednesday, March 4, 2015 4:46 PM
  • "NOTHING" means there is *nothing* that is causing the log file not to be reused. Which means SQL Server will use the log file which exists until it fills up and will only grow if more space is required.

    I think you dont have a problem now. Your log file could have grew earlier due to some reason which cant be found out now. The condition doesnt exist now and if you run the below query you will find that most of the log file is free.

    dbcc sqlperf(logspace)

    As others have already stated if you want to release the space back to operating system then you need to shrink the log file, which I wont recommend as it is going to grow back as soon as SQL Server finds that the log file space is not enough.

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

    Wednesday, March 4, 2015 4:50 PM
  • Hi, I rerun the query and got following result 

    model=LOG_BACKUP


    asad

    Wednesday, March 4, 2015 4:56 PM
  • Ok, Now again if you check the link I have posted, it says what is the issue with each description.

    LOG_BACKUP means it is waiting for a transactional log backup to clear the VLF's and only then it can be reused.

    Do you have regular transaction log backups configured? If so try changing the frequency. The current frequency might not be enough to maintain the size of log file that you have configured.


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

    Thursday, March 5, 2015 9:37 AM
  • Hi, thank you for reply, find below the growth rate, I schedule to execute per hour, I think still growing more

    dbname logSizeMB logSpaceUsedPct LogSpaceUnusedMB datetime
    PROD 70007.99219 0.153397664 69900.60156 4:00:00 PM
    PROD 70007.99219 0.403793186 69725.30469 1:00:01 PM
    PROD 70007.99219 0.611816287 69579.67189 2:00:01 PM
    PROD 70007.99219 0.662547112 69544.15626 3:00:00 PM
    PROD 70007.99219 0.916770339 69366.17968 5:00:00 PM
    PROD 70007.99219 1.060091019 69265.84375 11:00:01 AM
    PROD 70007.99219 1.965590358 68631.92184 10:04:22 AM
    PROD 70007.99219 2.066393614 68561.35151 12:00:00 PM


    asad

    Thursday, March 5, 2015 2:23 PM
  • I dont see any growth or issue here.

    The size is stagnant at 70007.99219

    You dont have any issue here and your log file is not even utilized . The maximum utilization is 2.06 %


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

    Thursday, March 5, 2015 2:28 PM
  • please provide me a query so I can find the cause of logfile growth


    Not the cause, but the rate can be queried from Performance Counter: Log Growth Rate

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, March 11, 2015 12:04 PM
    Moderator
  • Please find below we experience suddenly extensive growth while rebuilding indexes and manually we shrink the log file.

    PROD

    102775.9922 98.71157074 1324.195956 3/9/15 1:00 PM
    PROD 248184 99.48280334 1283.599347 3/9/15 2:00 PM
    PROD 248184 99.4907074 1263.982753 3/9/15 3:00 PM
    PROD 104823.9922 99.01555634 1031.93315 3/9/15 1:00 PM
    PROD 248184 99.62797546 923.3053748 3/9/15 4:00 PM
    PROD 749.8046875 4.465681076 716.3208015 3/8/15 11:00 AM
    PROD 749.8046875 5.280020714 710.2148447 3/10/15 8:00 AM
    PROD 749.8046875 6.103217125 704.0424794 3/11/15 10:00 AM
    PROD 749.8046875 7.214378834 695.7109368 3/9/15 6:00 PM
    PROD 749.8046875 8.520057678 685.9208957 3/8/15 12:00 PM
    PROD 749.8046875 9.84631443 675.9765604 3/8/15 1:00 PM
    PROD 749.8046875 9.94217205 675.2578154 3/8/15 2:00 PM
    PROD 749.8046875 10.02917385 674.6054718 3/10/15 6:00 PM
    PROD 749.8046875 10.04324055 674.4999991 3/8/15 3:00 PM
    PROD 749.8046875 10.60015583 670.3242222 3/8/15 4:00 PM
    PROD 749.8046875 13.66189098 647.3671885 3/8/15 5:00 PM
    PROD 749.8046875 13.92289639 645.4101578 3/10/15 11:00 PM
    PROD 749.8046875 14.33654594 642.308594 3/11/15 3:00 PM
    PROD 749.8046875 14.72414684 639.4023443 3/10/15 9:00 AM
    PROD 749.8046875 17.50924683 618.5195341 3/10/15 2:00 PM
    PROD 749.8046875 18.69497299 609.6289037 3/8/15 6:00 PM
    PROD 749.8046875 18.86376572 608.3632879 3/9/15 7:00 PM
    PROD 749.8046875 19.96249008 600.1250011 3/9/15 2:00 PM
    PROD 749.8046875 21.41690636 589.2197197 3/8/15 7:00 PM
    PROD 749.8046875 23.57749367 573.0195348 3/11/15 11:00 AM
    PROD 749.8046875 24.80978203 563.7797789 3/8/15 8:00 PM
    PROD 749.8046875 24.98254776 562.4843733 3/10/15 7:00 PM
    PROD 749.8046875 26.69236755 549.6640644 3/8/15 9:00 PM
    PROD 749.8046875 26.81687927 548.7304697 3/8/15 10:00 PM
    PROD 749.8046875 26.84032249 548.5546913 3/8/15 11:00 PM
    PROD 749.8046875 26.89606667 548.1367189 3/9/15 7:00 AM
    PROD 749.8046875 27.09090996 546.6757747 3/9/15 8:00 AM
    PROD 749.8046875 27.33628464 544.8359439 3/11/15 7:00 AM
    PROD 749.8046875 27.69386482 542.1547909 3/5/15 10:04 AM
    PROD 749.8046875 28.27455139 537.8007758 3/11/15 4:00 PM
    PROD 749.8046875 28.71086311 534.5292901 3/9/15 9:00 AM
    PROD 749.8046875 28.7757225 534.0429713 3/9/15 8:00 PM
    PROD 749.8046875 29.53060722 528.3828103 3/10/15 10:00 AM
    PROD 749.8046875 31.06902885 516.8476528 3/10/15 3:00 PM
    PROD 749.8046875 31.37275314 514.5703138 3/9/15 10:00 AM

    asad

    Wednesday, March 11, 2015 1:33 PM