Data files grow as data is added. Learn how to plan for and configure your file growth aspects to allow room for data with performance in mind.
- [Instructor] Considerations for how large your data files are, and how large they can grow, are things you need to understand, not only when installing SQL but more importantly, when you are creating databases on the installed instance. SQL Server allows you to set the initial size during the creation of the database, or you can accept the defaults, in this case which is eight megabytes. This size determines how much data you can actually store in the database. We can also expand the size of the data files to increase the storage space, or shrink them later to decrease the storage space.
But proper planning ahead of time really is crucial to supporting your database performance. Keep in mind that by default, data files will grow as required until the disc is full. Note that the max size in this particular instance is set to unlimited. You can choose different database growth options, such as manually expanding the database with a transact-SQL statement later, or setting the auto-growth options for the database. And in this case, we can enable auto-growth, and choose to allow the database file size to increase by a certain percentage or by a certain amount of megabytes.
And again, here's where we can limit the maximum file size or leave it set to unlimited. When we consider changing the size of the database in terms of auto-growth or even manually growing the database, we have to consider the impact on performance while the database is being expanded. A database is considered offline during the expand operation, which means no new connections can take place, and anybody that might have currently had a connection to the database will experience an interruption in their connection.
You can also consider adding a new file to increase the space rather than increasing the size of an existing file. In terms of your transaction log file sizing, you should allow that to expand automatically to avoid running out of log file space. You can also truncate that log file periodically if you find it's consuming too much disc space. Truncating will remove any committed entries and free space in the file. When shrinking your database, note that it recovers space by moving pages of data from the end of the file to unoccupied space closer to the front.
Then the empty space at the end can be removed, which then frees up space on the disc for other data. You cannot shrink a file smaller than the initial size in which it was created, however. So again, important planning at the outset of how large your data files should be and how much they should grow will help to improve the performance of your SQL Server installation, but also help you to optimize disc space.
This course is also ideal for anybody preparing for the Administering a SQL Database Infrastructure (70-764) exam, one of two exams necessary to earn an MCSA: SQL 2016 Database Administration certification.
- Installation and configuration considerations
- Data storage strategies
- Configuring database instances
- Performance considerations
- Security considerations
- Managing roles and users
- Managing data
- High-availability options