SQL Query: Set All Dbs AutoGrowth
Recommendations
The following are recommendations to proactively manage the growth of data and log files:
When possible, increase all data files and log files to their expected final size, or periodically increase these at set periods, for example, every month or every six months, or before rollout of a new storage-intensive site such as during file migrations.
Enable database autogrowth as a protective measure to make sure that you do not run out of space in data and log files. Consider the following:
Important:
You must factor in the performance and operations issues associated with using autogrowth. For more information, see Considerations for the “autogrow” and “autoshrink” settings in SQL Server.
Default Settings
The default settings for a new database are to grow by 1 MB increments. Because this default setting for autogrowth results in an increases in the size of the database, do not rely on the default setting. Instead, use the guidance provided in Set SQL Server options.
Set autogrowth values to a fixed number of megabytes instead of to a percentage. The bigger the database, the bigger the growth increment should be.
Note: Use care when you set the autogrowth feature for SharePoint databases. If you set a database to autogrowth as a percentage, for example at a 10-percent (%) growth rate, a database that is 5 GB grows by 500MB every time that a data file has to be expanded. In this scenario, you could run out of disk space. or the db could be 100gb and growth would be 10gb every time the file needed space
Consider for example, a scenario where content is gradually increased, say at 100MB increments, and autogrowth is set at 10MB. Then suddenly a new document management site requires a very large amount of data storage, perhaps with initial size of 50 GB. For this large addition, growth at 500 MB increments is more appropriate than 10MB increments.
For a managed production system, consider autogrowth to be merely a contingency for unexpected growth. Do not use the autogrow option to manage your data and log growth on a day-to-day basis. Instead, set the autogrowth to allow for an approximate size in one year and then add a 20 percent margin for error. Also set an alert to notify you when the database runs low on space or approaches a maximum size.
Maintain a level of at least 25 percent available space across drives to accommodate growth and peak usage patterns. If you add drives to a RAID array or allocate more storage to manage, monitor capacity closely to avoid running out of space. Setting autogrowth to use MB verse % along with changing the autogrowth MB size will create less fragmentation than using the defaults, this SQL Query makes it easier to modify a large number of Dbs.
-- Query to Set File AutoGrowth SELECT DB_NAME(mf.database_id) database_name, mf.name logical_name, CONVERT(DECIMAL(20, 2), (CONVERT(DECIMAL, size) / 128)) [file_size_MB], CASE mf.is_percent_growth WHEN 1 THEN 'Yes' ELSE 'No' END AS [is_percent_growth], CASE mf.is_percent_growth WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%' WHEN 0 THEN CONVERT(VARCHAR, mf.growth / 128) + ' MB' END AS [growth_in_increment_of], CASE mf.is_percent_growth WHEN 1 THEN CONVERT(DECIMAL(20, 2), (((CONVERT(DECIMAL, size) * growth) / 100) * 8) / 1024) WHEN 0 THEN CONVERT(DECIMAL(20, 2), (CONVERT(DECIMAL, growth) / 128)) END AS [next_auto_growth_size_MB], CASE mf.max_size WHEN 0 THEN 'No growth is allowed' WHEN -1 THEN 'File will grow until the disk is full' ELSE CONVERT(VARCHAR, mf.max_size) END AS [max_size], physical_name FROM sys.master_files mf ORDER BY database_name
Download: Set_AutoGrowth4AllDbs.sql
Ivan
![clip_image001[4]](http://blogs.msmvps.com/ivansanders/files/2015/02/clip_image0014.png "clip_image001[4]"){kind=small}
![clip_image002[4]](http://blogs.msmvps.com/ivansanders/files/2015/02/clip_image0024.png "clip_image002[4]"){kind=small}
![clip_image003[4]](http://blogs.msmvps.com/ivansanders/files/2015/02/clip_image0034.png "clip_image003[4]"){kind=small}
![clip_image004[4]](http://blogs.msmvps.com/ivansanders/files/2015/02/clip_image0044.jpg "clip_image004[4]"){kind=small}
![clip_image005[4]](http://blogs.msmvps.com/ivansanders/files/2015/02/clip_image0054.png "clip_image005[4]"){kind=small}
you ready for it? A successful social solution must give end users the foundational tools needed to effectively collaborate with coworkers, clients and partners while maintaining control of access, security and content. Each business has a different sweet spot when it comes to creating an engaging social environment that drives collaboration and meaningful dialogue. An enterprise social network’s success is dependent on unearthing your employees’ key motivational drivers, aligning the solution to your company’s culture, your vision for leveraging cloud-based solutions, and tailoring a strategy to fit. 
