Last updated on January 10th, 2023 at 09:54 am
In a couple of recent blog posts I talked about two ways to prevent the SQL Server database from running out of disk space because of transaction logs, and what I did when I discovered that my SQL Server Reporting Services (SSRS) database was using a ridiculous amount of disk space. Along the same lines, this blog post will show you how to define the size of a SQL Server database in either a new or existing database.
Size of a SQL Server Database
First, remember that a database is comprised of two main items, the database itself (#1) and the transaction log (#2).
When you create a database, the default size is 8MB. The autogrowth setting is 64MB at a time with unlimited growth (SQL Server 2016). By the way, the initial size and autogrowth settings are not the same between SQL Server versions. You can see this in the SQL Server 2016 and SQL Server 2008 R2 screenshots below.
SQL Server 2016
SQL Server 2008 R2
A quick internet search will reveal that it is generally not recommended to have your database set to autogrowth. Why is it a bad idea? First, you can run out of disk space (this is what happened with my lab), and second, it can effect disk performance, specifically, file fragmentation.
If it’s generally considered a bad idea to allow autogrowth of both database and log files then:
Why is autogrowth the default setting, and how can I cap the size of both my database and log files?
I’m not sure why autogrowth is the default setting, but check out this article, Considerations for the “autogrow” and “autoshrink” settings in SQL Server, if you’d like to learn more about it.
As for the second part of my question, ideally the size of database files should be defined when a new database is created. For example, if you expect the database to reach 50MB in size over three years then you would limit the database file’s size to 50MB and the log file’s size to 15MB (approximately 25% of the database file’s size).
Below I will show you how to define the size of SQL Server database files in both a new database and an existing one.
New Database
First, create the database as you normally would.
Within the New Database window, on the General node, click within the Initial Size (MB) column and set the database to 50MB. Repeat the same for the log file size, but use 15MB.
Next, move to the Autogrowth / Maxsize column and click on the ellipsis button (…) next to the database file.
Deselect the Enable Autogrowth check box. Repeat this step for the log file.
When you are finished, your window will look similar to the one below.
Finally, click on the OK button in order to create the database.
Existing Database
What do you do if you already have any existing database? The steps are fairly similar to that of a new database. First, start by reviewing the properties of the database.
Select the Files node.
Click within the Initial Size (MB) column and set the database to 50MB. Repeat the same for the log file size, but use 15MB.
Next, move to the Autogrowth / Maxsize column and click on the ellipsis button (…) next to the database file.
Deselect the Enable Autogrowth check box. Repeat this step for the log file.
When you are finished, your window will look similar to the one below.
When you click on the OK button, your database files will be expanded to the specified size.
If you have any questions about how to define the size of a SQL Server database, please feel free to contact me @GarthMJ.