none
AlwaysOn - changing log file growth setting RRS feed

  • Question

  • Hi

    I was looking to change the file growth setting in our AlwaysOn environment databases.

    We have a single availability group, one primary and one secondary replica. I learned that when changing the file growth setting on the primary databases (data file), the change flows though to the database on the secondary replica. Very cool.

    However after doing the same with the log files, the file growth setting changed on the primary but the change did NOT propagate to the secondary.

    Please would someone kindly explain this behavior? Ideally if there is some documentation of this that would be great to read. I've tried to find in BOL without success. Not sure what to search for!

    Is the solution to apply the change directly to the secondary?

    thank you for your help in advance.

    kindest regards

    Paul

    p.s - here's the T-SQL code I used:

    ALTER DATABASE myDB
    MODIFY FILE ( NAME = N'myDB_log', FILEGROWTH = 512MB );
    GO

    SQL Server 2012 (11.0.5532)

    Wednesday, August 5, 2015 11:09 AM

Answers

  • Did you checked the log file of database after running alter statement , did it really changed , can you confirm this ?

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by SQL_Golfer Friday, August 7, 2015 2:09 PM
    Wednesday, August 5, 2015 1:26 PM
    Moderator
  • My guess is that the *real* values are replicated. By that I mean the databases sys.database_files. These are that is actually used when the database grows.

    What you are looking at is sys.master_files, which is just a copy of that information, stored in the master database. You can check sys.database_files, but that requires a readable replica or setting it online. Or do enough operation on the primary so a grow occurs and study the secondary. But, IMO, don't trust sys.master_files.


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, August 6, 2015 8:34 PM
    Moderator

All replies

  • Did you checked the log file of database after running alter statement , did it really changed , can you confirm this ?

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by SQL_Golfer Friday, August 7, 2015 2:09 PM
    Wednesday, August 5, 2015 1:26 PM
    Moderator
  • unble to repro on

    Microsoft SQL Server 2012 - 11.0.5058.0 (X64)

    May 14 2014 18:34:29

    Copyright (c) Microsoft Corporation

    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)



    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, August 5, 2015 3:18 PM
    Moderator
  • @Shanky_621 - yes I can confirm that the file growth option for the data files had actually changed on the secondary. I confirmed this with the code below. Using the same code I can see also that the file growth setting for the log file remains unchanged on the secondary.

    SELECT
    DB_NAME(mf.database_id) database_name
    , mf.name logical_name
    , CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB]
    ,	CASE mf.is_percent_growth
    		WHEN 1 THEN 'Yes'
    		ELSE 'No'
    	END AS [is_percent_growth]
    ,	CASE mf.is_percent_growth
    		WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
    		WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
    	END AS [growth_in_increment_of]
    ,	CASE mf.is_percent_growth
    		WHEN 1 THEN
    		CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
    		WHEN 0 THEN
    		CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))
    	END AS [next_auto_growth_size_MB]
    ,	CASE mf.max_size
    		WHEN 0 THEN 'No growth is allowed'
    		WHEN -1 THEN 'File will grow until the disk is full'
    		ELSE CONVERT(VARCHAR, mf.max_size)
    	END AS [max_size]
    , physical_name
    FROM sys.master_files mf;

    kind regards

    Paul

    Thursday, August 6, 2015 8:39 AM
  • My guess is that the *real* values are replicated. By that I mean the databases sys.database_files. These are that is actually used when the database grows.

    What you are looking at is sys.master_files, which is just a copy of that information, stored in the master database. You can check sys.database_files, but that requires a readable replica or setting it online. Or do enough operation on the primary so a grow occurs and study the secondary. But, IMO, don't trust sys.master_files.


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, August 6, 2015 8:34 PM
    Moderator
  • Unfortunately growth of the log and shrinking of the log do not replicate in Availability groups.

    You are expecting like your cluster environment. you need to remember participant nodes in AlwaysON are standard alone instances. 

    Always on synch the data i have come across this issue several time. Where my log files become too large and wanted to take truncate and shrink.

    First of all i can not perform such tasks with out breaking the Always on.

    When i have to do such maintenance i will remove the databses from AG and do maintenance and add back to the availability group.

    I hope this will give you an idea

    Thursday, August 6, 2015 11:08 PM
  • Unfortunately growth of the log and shrinking of the log do not replicate in Availability groups.

    I don't believe that above statement is correct. Did you find any documentation?

    You are expecting like your cluster environment. you need to remember participant nodes in AlwaysON are standard alone instances. 

    Always on synch the data i have come across this issue several time. Where my log files become too large and wanted to take truncate and shrink.

    First of all i can not perform such tasks with out breaking the Always on.

    What was the wait in log_reuse_wait_desc in sys.databases?

    When i have to do such maintenance i will remove the databses from AG and do maintenance and add back to the availability group.

    I hope this will give you an idea

    Please share the repro steps where it doesn't work.

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Friday, August 7, 2015 12:17 AM
    Moderator
  • @Shanky_621 - yes I can confirm that the file growth option for the data files had actually changed on the secondary. I confirmed this with the code below. Using the same code I can see also that the file growth setting for the log file remains unchanged on the secondary.

    Can you check if -

    1. Secondary Database is in Sync with Primary.
    2. You are verifying the size via database, right click, properties in SSMS.


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Friday, August 7, 2015 12:32 AM
    Moderator
  • Hello

    Thanks to all who have taken the time to help.

    Tibor solved the issue. The filegrowth values had indeed changed. The issue was with sys.master_files. Is was and still reports the wrong value. I will now also not trust this catalog view. Querying the sys.database_files catalog view returned the correct values. I also checked in the SSMS GUI and they also were correct. Percent log growth setting gone, even though sys.master_files reports it as still there!

    @Kumar - you have misunderstood the issue. I was not referring to actual growth events, merely the file growth setting itself.

    @Balmukund - The databases are in sync yes. The mode is synchronous (or safe mode).

    Thanks again everyone.

    kindest regards

    Paul

    Friday, August 7, 2015 2:09 PM
  • Hi All,

    Just letting you know that if you change the autogrowth settings in Primary it will be reflected in Secondary replica as well (Both synchronous and asynschronous mode).

    I have worked on a production server consisting SQL 2012 SP3 and it worked.

    Regards

    Shailesh


    Regards Shailesh

    Thursday, November 2, 2017 12:03 PM