Changing log to unlimited growth - reverts back to limited

  • Never seen this behaviour before (maybe it has happened and I haven't been bitten yet!)

    I have some new DBs (SQL 2012) and these have been created by creating new content DBs from within SharePoint.

    When I open SSMS and go to properties>files I notice that the log growth is limited to X.

    So I change this to unlimited and click ok twice. When I go and check again it is again sitting at limited to X. No tsure if SharePoint is preventing this change or if a background process is ignoring my change as it is in the middle of something.

    Any ideas?

  • Hmmm. Weird.

    I'd suggest putting Extended Events to work. You can look for Object: Altered events. I'm pretty sure that's where it would come up. Just filter by the database(s) in question. I have a blog post on database auditing in general using Extended Events.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I have not run into this, but I have a few thoughts.  First, are you using SSMS 2012 or a newer version?  I know with some things (SSIS comes to mind), if the SSMS version doesn't match the SQL version, you can have headaches with doing certain tasks.

    Alternately, you say this is a SharePoint database. Any chance the SharePoint service has permissions that would allow it to change database settings like that?  I could see a 3rd party tool having its own "best practice" setup and forcing certain database settings such as the log growth.  OR it may be that you need to configure the database settings inside the tool (SharePoint).  I know my company has a 3rd party tool that uses SQL Server as a back-end and it needs create database permissions so it can make the database with the settings they see as a best fit for the tool.  Once the database is created, I can remove those permissions, but it creates it with permissions that I don't fully agree with, but changing some of them makes the tool give alerts to all users (and gives admins a "click here to fix this setting" button).  I would not be too surprised if SharePoint is making those changes for you OR if you can (and need) to configure it from within SharePoint.

    Just my 2 cents.  I could be completely out to lunch here.  Do you have any other DBA's that sit near you who may see you are changing it and they change it back just to mess with you?

    Grant's suggestion of XE I think is going to be your best bet to track down what is causing it to change back.  Alternately, may not hurt to check the logs.  Could be there is some error with changing it but for whatever reason SSMS is not presenting it to you and it MAY be in a log.

  • This is a bug in SSMS - and has been around forever.  The value that you see is 2097152 MB - which is 2048 GB - which is 2 TB...and that is the maximum file size for a transaction log in SQL Server (https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15).

    At some point - someone set a max size for the transaction log and now the file will always be limited to the maximum allowable size of 2TB.  This has no effect on the file being able to grow as needed.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jay@Work wrote:

    Never seen this behaviour before (maybe it has happened and I haven't been bitten yet!) I have some new DBs (SQL 2012) and these have been created by creating new content DBs from within SharePoint. When I open SSMS and go to properties>files I notice that the log growth is limited to X. So I change this to unlimited and click ok twice. When I go and check again it is again sitting at limited to X. No tsure if SharePoint is preventing this change or if a background process is ignoring my change as it is in the middle of something. Any ideas?

    What value exactly are you seeing?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply