none
Unable to change autogrowth properties of SQL Server 2008 data and log files

    Question

  • I have used the GUI and the CLI to attempt to change SQL Server 2008 log file autogrowth property all to NO avail. In other words, there are NO error messages of any kind, indeed the CLI reports that the command completed successfully. But re-examining the database properties shows that NO change was effected.

    For the CLI command, I have used the following with dots filled in for my database and logfile:


    ALTER DATABASE ... MODIFY FILE ( NAME = ..., FILEGROWTH = 10%)

    Why doesn't this work? How to make it work?

    Thanks

    Monday, March 22, 2010 6:15 PM

Answers

  • Facing the same issue.  The changes done thru UI is not persisted.  It resets back to its old value 2,097,152

    - No policy exists

    - I have full admin rights to the DB & server


    Got the workaround?  It's long time.

    Are you guys setting the max size as unlimited for a logfile ? If thats the case , then this is by design. When you set the log file size to unlimited it gets reset to 2TB which is the max size supported for a logfile in SQL server currently. Both means the same and thats how MSFT has hard-coded this setting.
    Thanks, Leks
    Saturday, October 02, 2010 6:34 AM
    Answerer

All replies

  • This should work anyway ( thru GUI or TSQL) . Are you running the command with an account that has right access

    to do it ( sysadmin or db_owner for that database) ?


    Thanks, Leks
    Monday, March 22, 2010 8:21 PM
    Answerer
  • Yes the account used for login to Windows also is an account with sysadmin and db_owner privileges for the database and is mapped to dbo for the database.

    But no matter what I do, even creating a new additional log file with unrestricted growth, it gets automatically converted to the restricted growth same as the initial log file.

    Monday, March 22, 2010 9:15 PM
  • Check whether you have any triggers or SQL policies defined for this rule at the database scope.

     


    Thanks, Leks
    • Edited by LekssEditor Tuesday, October 12, 2010 7:31 PM
    Monday, March 22, 2010 9:25 PM
    Answerer
  • Not that I can find.

    Unless you advise checking something in particular.

    What about file permissions in the file system? What do those require? If they are not set right, why don't I get an error message of denied permissions for file access?

    Monday, March 22, 2010 9:50 PM
  • What is the current value for autogrowth?  And it stay the same after the ALTER? What if you try to set a fixed size (which is recommended anyhow, but if for nothing else then for troubleshooting measures)?=


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, March 23, 2010 4:23 PM
    Moderator
  • What is the current value for autogrowth?  And it stay the same after the ALTER? What if you try to set a fixed size (which is recommended anyhow, but if for nothing else then for troubleshooting measures)?


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, March 23, 2010 4:23 PM
    Moderator
  • Facing the same issue.  The changes done thru UI is not persisted.  It resets back to its old value 2,097,152

    - No policy exists

    - I have full admin rights to the DB & server

    Monday, July 05, 2010 8:01 AM
  • Facing the same issue.  The changes done thru UI is not persisted.  It resets back to its old value 2,097,152

    - No policy exists

    - I have full admin rights to the DB & server


    Got the workaround?  It's long time.

    Are you guys setting the max size as unlimited for a logfile ? If thats the case , then this is by design. When you set the log file size to unlimited it gets reset to 2TB which is the max size supported for a logfile in SQL server currently. Both means the same and thats how MSFT has hard-coded this setting.
    Thanks, Leks
    Saturday, October 02, 2010 6:34 AM
    Answerer
  • Agree with Leks.
    Unlimited Log file = 2 TB Limited.
    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Saturday, October 02, 2010 5:30 PM
    Moderator