Category: 2012

Today I like to write about a problem that excists in a lot of environments. The file sizes and file growth of the databases are default configured with 1MB growth and 10% growth. I see a lot of environments and most of them do not have adjusted values for these settings. The default setting for database files is:

  • Data: initial size 2MB; Autogrowth: by 1MB, unrestricted growth
  • Log: initial size 1MB; Autogrowth: by 10 percent, unrestricted growth

Good reading about the default values is this article [Choosing Default Sizes for Your Data and Log Files]. So after reading that excellent blog article by Paul S. Randal you should be convinced that the default settings are not the best for your environment. But what should they be?

Of course the well known answer is: “It depends…”

In an ideal situation you know what the expected growth in data for your database is and you pre-size your database files accordingly. However I rarely see an environment where the DBA and/or the Developers have any clue about the growth in data for the next few months, years of even a longer time frame.

After changing file sizes too long by hand I started thinking about creating a script for automating this based on the file sizes the databases have when you run the script.

I started with defining 4 possible sizes for database file growth settings:

  • Databases that grow per 100MB
  • Databases that grow per 250MB
  • Databases that grow per 1.000MB
  • Databases that grow per 2.500MB

The next thing is to define what file sizes are the limits for these file growth settings:

  • Databases from 1MB to 249MB
  • Databases from 250MB to 2.499MB
  • Databases from 2.500MB to 4.999MB
  • Databases from 5.000MB to ∞

Next I started writing a script to programmatically create the T-SQL statement to change the settings for the databases.

First I check what the result will be for each database with an overview for every new file size and file growth setting:

SELECT [SMF].[database_id] AS [Database ID]

, [SMF].[name] AS [File name]

, [SMF].[size] * 8 / 1024 AS [Current size]

, ROUND ((([SMF].[size]) * 8 / 1024 / 100) , 2 , 0) * 100 + 100 AS [New size in growth per 100]

, ROUND ((([SMF].[size]) * 8 / 1024 / 250) , 2 , 0) * 250 + 250 AS [New size in growth per 250]

, ROUND ((([SMF].[size]) * 8 / 1024 / 1000) , 2 , 0) * 1000 + 1000 AS [New size in growth per 1000]

, ROUND ((([SMF].[size]) * 8 / 1024 / 2500) , 2 , 0) * 2500 + 2500 AS [New size in growth per 2500]

FROM [master].[sys].[master_files] [SMF]

The result from the script above will be something like this (results will vary per environment):

 

 

 

 

 

Next I created the script that creates a result based on the current file sizes:

SELECT [SMF].[database_id] AS [Database ID]

, [SMF].[file_id] AS [File ID]

, [SMF].[name] AS [File name]

, [SMF].[size] * 8 / 1024 AS [Current size]

, [SMF].[growth] * 8 / 1024 AS [Current growth]

, [SMF].[is_percent_growth] AS [Is current percentage growth]

, CASE

WHEN [SMF].[size] * 8 / 1024 < 100 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 100) , 2 , 0) * 100 + 100)

WHEN [SMF].[size] * 8 / 1024 < 2500 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 250) , 2 , 0) * 250 + 250)

WHEN [SMF].[size] * 8 / 1024 < 5000 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 1000) , 2 , 0) * 1000 + 1000)

WHEN [SMF].[size] * 8 / 1024 >= 5000 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 2500) , 2 , 0) * 2500 + 2500)

END AS [New file size]

, CASE

WHEN [SMF].[size] * 8 / 1024 < 100 THEN '100'

WHEN [SMF].[size] * 8 / 1024 < 2499 THEN '250'

WHEN [SMF].[size] * 8 / 1024 < 4999 THEN '1000'

WHEN [SMF].[size] * 8 / 1024 >= 5000 THEN '2500'

ELSE '1024'

END AS [New file growth]

FROM [master].[sys].[master_files] [SMF]

In the results from that script you will see what the current sizes and growth settings are and the proposed new sizes and growth settings (results will vary per environment):


 

 

 

 

 

So with this logic in place I could create the final script that creates the T-SQL code for changing the database file sizes for each database in the SQL Server instance based on the current file sizes and defenitions made earlier. In scripts like these I never make them that the T-SQL code will automatically run. The scripts create T-SQL code that you need to copy paste into a new query window so that you can check if the script accomplishes what you want. Before running the script below make sure your results are displayed in Text (CTRL + T)

In this script I use the SQLCMD mode option :CONNECT + Servername to be sure the changes will be done on the correct SQL Server instance.

SELECT ':CONNECT ' + @@SERVERNAME + '

ALTER DATABASE [' + CONVERT(nvarchar(1000), DB_NAME(SMF.database_id)) + '] MODIFY FILE ( NAME = ''' + RTRIM(SMF.name) + ''', SIZE = ' +

CASE

WHEN [SMF].[size] * 8 / 1024 < 100 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 250) , 2 , 0) * 100 + 100)

WHEN [SMF].[size] * 8 / 1024 < 2500 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size])* 8 / 1024 / 250) , 2 , 0) * 250 + 250)

WHEN [SMF].[size] * 8 / 1024 < 5000 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 1000) , 2 , 0) * 1000 + 1000)

WHEN [SMF].[size] * 8 / 1024 >= 5000 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 /1024 / 2500) , 2 , 0) * 2500 + 2500)

END + ', FILEGROWTH = ' + (

CASE

WHEN [SMF].[size] * 8 / 1024 < 100 THEN '100'

WHEN [SMF].[size] * 8 / 1024 < 2499 THEN '250'

WHEN [SMF].[size] * 8 / 1024 < 4999 THEN '1000'

WHEN [SMF].[size] * 8 / 1024 >= 5000 THEN '2500'

ELSE '1024'

END + 'MB )' )

FROM [master].[sys].[master_files] [SMF]

The results from this script are the T-SQL commands for changing the file sizes and file growth settings (results will vary per environment):


 

 

 

 

 

 

 

 

 

When you used CTRL + T to get the results to text the result will look like above. Select only the commands and copy them into a new Query windows:


 

 

 

 

 

 

 

Default you will see the :CONNECT + Server lines not in grey, to get your script to run correctly you first need to enable SQLCMD mode via the menu option [Query] and [SQLCMD Mode]

Now you can run the query created by the script to change your database file sizes and file growth settings.

Remember that every time you run the query the sizes will change. This script is mainly intended for first time running after installing a new server with databases or when you need to change the default settings for the first time.

Of course you could run this script every now and then. My advise in this case is to change only databases that needs to change and leave the already changed databases as they are.

There is impact when running the results from the script because databases are grown to new sizes and that takes disk I/O. So in heavy used environments I advise to run the results from the script planned in a time windows where there is lower usage or better in a maintenance window.