June 14, 2021 at 8:30 pm
I have a question for the seasoned DBA's on here. I have come across a sql server environment where the DBA over the environment has all the files set to none for autogrowth. I know that this cannot be a good thing from my years as a DBA and me adjusting autogrowth after installs to accommodate the user database but I wanted to get you all's thoughts about this and what the consequences in doing this are.
MCSE SQL Server 2012\2014\2016
June 14, 2021 at 9:00 pm
Maybe the DBA likes calls late at night asking why the application is hung.
June 14, 2021 at 9:15 pm
Size the database as best you can and keep an eye on it for adding more space when it gets below a certain threshold but would still have auto grow enabled as all the best efforts you will still have times where you will need it.
Adding space can be a costly operation deepening on the growth amount. So many be that’s why it’s disabled so they can plan expansions manually out of hours.
June 15, 2021 at 12:19 am
(Is it a good idea to record the size of the various tables you want to track by inserting the recordcounts for each into a static table?)
I've done this by querying the sys.partitions table and filtering for the tables I'm interested in, and then inserting the sum of the recordcounts or the table size into a table somewhere (Maybe in a reporting database).
Then you can query it for file size and map/calculate growth etc in Excel.
June 15, 2021 at 6:22 am
When I worked also as production DBA, I did set autogrowth for databases's files, but I also had a job which ran every night and checked all databases, and if database had less then a specific amount of free space, the DBA team got a message about it. The autogrowth was there only as a security net, but as far as I was concerned, if I did get into the situation that it was activated, it meant that we did something wrong.
Adi
June 15, 2021 at 11:34 am
I suppose if you have really good monitoring in place, you could use this approach everywhere. However, what I've found over the years is that some databases need a limit because they're likely to get spurious or weird growth. Lots of other databases grow very slowly over time, so taking away the ability for it to happen automatically is just putting work on you to no purpose. So, a mix is what I've done. Some fixed, with monitoring, and I manually intervene occasionally, the rest all automatic.
The only thing I will say is, get rid of that % growth ASAP. Much better to grow by fixed amounts (said amount determined through experimentation & monitoring) than a fixed percentage which gets scarier & scarier over time.
"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
June 15, 2021 at 3:19 pm
I stopped using % to grow files years ago. Microsoft even has an article saying what releases to use % and which to use actual amounts. I am actively looking for this article to post in this thread, I believe it was with SQL 2012 % stopped.
Edit:
Found the article, almost in the middle of the page under If Filegrowth is not specified, the default values are:
MCSE SQL Server 2012\2014\2016
June 15, 2021 at 7:48 pm
It is quite normal on other products to not allow auto growth to occur in the database files or tablespaces. Monitoring of those files and extending them when needed is the normal state. At least it was when I worked on those other systems.
This is considered part of the process for capacity planning - and making sure you have enough storage available for the next cycle. This was especially important when we had much smaller disks and arrays available - and when we had hard limits on the sizes of the files. For example - we use to have a hard limit of 16GB of database files and if that file started nearing that limit we needed to address the growth. At that point - we would either force an archive/purge process to recover enough space to continue operations, or would have to figure out how to split the data across files without impacting the applications abilities to access that data.
This was also important when adding storage to a Windows server required a downtime. Extending databases and volumes would need to be scheduled and that would normally be done after hours. In fact - it is coming back now because you cannot add space to servers in some of the cloud solutions without a downtime...
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
June 16, 2021 at 5:58 pm
I have always set autogrowth, by some amount, not %. I make sure each file in a db has the same settings.
However.
If autogrowth executes, I take this as a personal failure. I should be monitoring growth and catch anything getting close. I also set alerts in autogrowth events to let me know something is broken.
I also set placeholders, so that if autogrowth goes crazy, I have space to fix things: https://voiceofthedba.com/2014/12/01/creating-placeholder-files/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply