How to Change SQL Server Database Auto Growth Settings
Aug 02, 2012
In this article we will go through the steps to change SQL Server Database Auto Growth Settings. It is always a best practice to set an appropriate auto growth setting for all Production database to a handle unexpected database growth which can be due to unexpected data load or due to the disk space requirements to perform maintenance tasks. The steps mentioned in this article are same across SQL Server 2005 and higher versions.
Different ways to Change SQL Server Database Auto Growth Settings
- Change SQL Server Database Auto Growth Settings Using SQL Server Management Studio (SSMS)
- Change SQL Server Database Auto Growth Settings Using TSQL Script
Recommendation: It is a best practice to Configuring Database Instant File Initialization Feature on windows for SQL Server to reduce the time required to growth database file.
Let us go through each of the above mentioned options in detail.
How to Change SQL Server Database Auto Growth Settings Using SQL Server Management Studio (SSMS)
1. Connect to SQL Server Instance Using SQL Server Management Studio
2. Expand Databases; right click the database and select Properties from the drop down list to open up Database Properties to change the Autogrowth settings for a database as shown in the snippet below.
3. In Database Properties; Select Files Page on the left side panel as highlighted and then click on “…” button to open up Change Autogrowth for Database dialog box.
4. In Change Autogrowth for Database dialog box you will see that the default File Growth Autogrowth setting is 1 MB. You can change the Autogrowth settings by changing the value either in Mega Bytes or in Percentage. However, it is better to change the value in Megabytes are this will have better control on the database file growth. The Autogrowth value should be change for both Data and Log files. In this demo I have set the data file growth as 512 MB and Log File growth as 256 MB. Once you change Autogrowth setting click OK to save the changes and return to Database Properties window.
5. In the Database Properties Window you could see that new values for Data and Log file Autogrowth is reflected. Click OK to make the changes to the Autogrowth settings of the database.
How to Change SQL Server Database Auto Growth Settings Using TSQL Script
Using the below script you can change the database Autogrowth settings to grow data file at 512 MB and Log file at 256 MB.
USE [master] GO ALTER DATABASE [MyTechMantra] MODIFY FILE ( NAME = N'MyTechMantra', FILEGROWTH = 512MB ) GO ALTER DATABASE [MyTechMantra] MODIFY FILE (NAME = N'MyTechMantra_log', FILEGROWTH = 256MB ) GO
Continue Free Learning...
Geeks who read this article also read…
- Troubleshooting SYSPOLICY_PURGE_HISTORY Job Failure in Stand Alone Instance SQL Server 2008
- Troubleshooting OLE DB Provider Microsoft.ACE.OLEDB.12.0 is not registered Error
- Troubleshooting SQL Server blocked access to procedure sp_send_dbmail
- Performance Dashboard Reports in SQL Server 2012
- Tips to Avoid Account Lockout Issues
- Encrypt Database Backups in SQL Server Using MEDIAPASSWORD Option
- Using SP_SERVER_DIAGNOSTICS Stored Procedure Quickly Gather Diagnostic Data and Health Information in SQL Server 2012
- New Features in Microsoft SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Why an SQL Server Database from a higher version cannot be restored onto a lower version?
- How to identify if the database was upgraded from a previous version of SQL Server
- Installing SQL Server 2008 R2 on Windows Server 2008 R2
- Using Transparent Data Encryption Feature of SQL Server 2008
- New Date and Time Data Types in SQL Server 2008
- Date and Time Functions in SQL Server 2008
- Configuring Database Instant File Initialization Feature of SQL Server
- Changing the default location of SQL Server Data and Log files
- Changing Default SQL Server Backup Folder in SQL Server 2008
- How to repair a Suspect Database in SQL Server
- Steps to Rebuild System Databases in SQL Server
- How to Get Exclusive Access to SQL Server Database
- Read More SQL Server Articles…