We can use SSMS to Enable Autogrowth property of the Database.
Right-click on Database click on Properties and click on Files. There will be column of Autogrowth, click on small box with three (…) dots.
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
11 Comments. Leave new
That’s a usefull info, thanks Dave.
Hi Dave,
Good article. On one of my DB instances, SQL Server always reset this to “Restricted File Growth”. I tried few times to define Unrestricted File Growth, and server always reverts it bac to restricted. Any idea what could be wrong?
It might be the max limit allowed to the filegroup for the license of SQL Server that you possess.
It might be the max size allowed to filegroup for the SQL license that you possess!
Even though I change the autogrowth with method above, its still 1280% in sysfiles like when I do:
select * from sysfiles
I have a problem with the auto growth, for some reason sql 2005 changes the auto growth to grow by either 6400 or 1440 %. i cant disable it because there is more inserting then selecting.
if anyway could could give me a solution it will really be appreciated.
regarads gavin
please mail it to me.
hi dave
how can i set the growth file size ……..?
is it will create any problem during DB transaction??
I am trying to figure out code to run every time a database is created, so this would all be part of a trigger on create of any database. My idea is to change the autogrowth for that database if it meets certain requirements. Right now when i execute the code it executes fine, but will not actually change the autogrowth for the database :(.
DELCARE @dbname nvarchar(100), @File1Growth nvarchar(10), @File2Growth nvarchar(10)
SET @bdname = ‘TempDatabase’
SET @SQLSTRING =
(
‘ USE ‘ + @dbname +
‘ SET ‘ + @File1Growth + ‘ = ‘ +
‘ ( ‘ +
‘ SELECT sysfiles.growth ‘ +
‘ FROM sysfiles ‘ +
‘ WHERE sysfiles.FileId = 1 ‘ +
‘ ); ‘ +
‘ SET ‘ + @File2Growth + ‘ = ‘ +
‘ ( ‘ +
‘ SELECT sysfiles.growth ‘ +
‘ FROM sysfiles ‘ +
‘ WHERE sysfiles.FileId = 2 ‘ +
‘ ); ‘ +
‘ IF ‘ + @File1Growth + ‘ <= 1 ' +
' ALTER DATABASE ' + @dbname +
' MODIFY FILE (NAME = ' + @dbname + ',FILEGROWTH = 4MB);' +
' IF ' + @File2Growth + ' <= 10 ' +
' ALTER DATABASE ' + @dbname +
' MODIFY FILE (NAME = ' + @dbname + '_Log,FILEGROWTH = 4MB);'
);
EXEC SP_EXECUTESQL @SQLSTRING
I am wondering if anyone has any ideas that may work?
Hi Pinal ,
When i found this error wile running my SP I went to Enable Autogrowth but it was already set still i m facing this error.
and one more thing my sp is not giving same behavior for same input some times it gives me error after 40 iterations or some time 200 .It have total no of iteration is 331.
STRUCTURE OF SP IS Like:
While()
BEGIN
While()
BEGIN
While()
BEGIN
END
END
END
Means more complex…
Your Sql Server is 2005? if it is means You can’t have Storage OF more than 4 GB
Thanks so much