May 4, 2021 at 9:51 am
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?
May 4, 2021 at 12:11 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 4, 2021 at 3:55 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 4, 2021 at 4:56 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 6, 2021 at 10:15 am
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply