SQL Server Database Autogrowth And Expertise Idea to Deal With It
Nowadays it is now easy for end users to create databases in the server with n number of tables in each. But, a problem in future occurs when users leave default settings of SQL Server Database Autogrowth as it is. Why? What to do then? Let’s read it!
Lots of maintenance jobs are present in Microsoft SQL server applications. If these jobs are ignored, they create problems in future. Regular maintenance of the server makes it easy to manage database system and keep its functioning constant. Microsoft renders several point and click actions for defining number of maintenance operations like database backup, regular integrity checks, shrinking databases, etc. Several operations are handled by the SQL server app on its own via configured settings.
These settings comprise of the predefined value, which might be inappropriate in all installations. Being the database administrator, it is your duty to override the predefined configuration settings. You should look at the server setting and make changes in it on basis of your suited condition. One such settings is ‘SQL Server Database Autogrowth’, which is created when first database in the server is created.
More About Autogrowth in SQL Server
The procedure through which the engine of MS SQL service explores the database file size when it executes out of space is known as autogrowth event. The value by which the growth of database file size, is dependent upon the predefined settings for file growth options. Each database comprises of an autogrowth settings in the server. There exists three different settings through which you can determine ‘how the server database files will grow’. They could grow on basis of their particular size, current size percentage, or not grow at all.
In addition to this, DBAs can define their files for unlimited growth that means they will continuously keep increasing. This kind of scenario is required when administrators demand for more space or are running out the monitor SQL Server disk space. Database administrators can also restrict the database file growth not to grow more than the defined size. Each one of the settings are already defined at the installation time and administrators can customize them as per the requirement.
Major use of the SQL server database file autogrowth setting is to automate the database file growth. This setting proves itself useful only when you are using it carefully with systematic planning. But, a problem might occur when DBAs do not put their attention towards SQL Server Database Autogrowth. Leaving the setting’s value same as they were at the time of installation will definitely prove themselves risky in future. If administrators try to create too many autogrow events in their database, this might leads to the server performance degradation. Well, next comes the section through which we are going to illustrate that how SQL DBAs can reduce the unfortunate events of autogrowth to enhance the server performance?
Ideas to Deal With SQL Server data file Autogrowth
1. Set Minimum Size of Database = Your database should be having enough size so that it never grows. Even if in case it grows, ensure that instant file initialization is enabled on that instance of the SQL server. Use the option of autogrowth in a way that it is being getting used to avoid the sudden scenarios associated with the storage space. You have to think of the database size in such a manner that it minimizes the use of autogrowth settings. This will help you in minimizing events associated with SQL Server Database Autogrowth, which are to be occurred in future.
2. Resize the Autogrowth Settings = Remember one thing that autogrowth events are quite expensive tasks, which degrade the database performance. It happens because whenever this sort of event occurs, the SQL server controls the database processing. This results in slowing down the response time for all those server commands, which are being in use against the databases that are growing. Another negative phase of autogrowth is the disk fragmentation. The more events of autogrowth you have, the more physical fragmentation you’ll be having. So, change the autogrowth size of each database file of the model database, which will enforce on all databases created on the server instance.
Method to Customize Autogrowth Settings
Administrators can change settings of database autogrowth in SQL server by running following command :
You can also prefer the use of GUI in SSMS to change the autogrowth settings in the SQL server. For this, you simply have to go through following steps :
1. Open the database property window on your PC
2. Click on the File option, located at the left hand side of the panel
3. In the field of Database Files, a list will appear. Click on the (…) icon of the database whose setting is to be change
4. This opens a Change Autogrowth setting window in which you have to set your desired changes. Once done, simply click on OK
5. Again click on OK and restart the server to finish up with the changes
Don’t be Dependent on Default Settings
At last, we only want to suggest DBAs that they should never be dependent on the default settings of SQL Server Database Autogrowth. Not only regarding autogrowth but, we would also like to suggest you the same for all properties settings of the server. It simply means that enterprises have to define the settings according to their premises need.
