I have set the autogrowth and maxsize properties to:
By 10 MB, Unlimited
When I look at the Properties -> General Tab by right-clicking the database in SSMS, I see the file size is 2249MB with 6.1 MB of space available. What does this mean?
I have 700 GB free space remaining on the drive where the log file and database file sits. The log file does have a limit of 2097152 MB. It looks like that space available means log file space?
In terms of database performance, how does the size of the database and log files affect reads and writes?
Best Answer
The size in and of itself is what you will need to appropriately determine when setting and growing. This is all part of properly sizing your database.
But you need to be careful with growing your files at such a small increment. If you are growing your files often, you are causing SQL Server to have to do this relatively expensive operation. Take, for instance, growing of the transaction log. When this happens all write activity will be impeded. Another thing about that is if you grow your transaction log at a small interval and it grows often, you will end up with a large amount of VLFs that are small in size.
Instant File Initialization is a great approach, as it bypasses the zero-initialization of the additional file (or file allocation). This shaves off a lot of the overhead and performance impact, but this is only possible with data files, not log files.
As you can see, there are a handful of things to consider depending on your environment and how much your database grows. Size your database appropriately and find the right growth increment.
Note, autogrowth should be reserved for emergency situations and it is recommended to manually grow your files if at all possible. This gives you full control over all of the points above, as well as allowing you to determine when this operation occurs.
As for your numbers, "space available" is just that: The free space that you have for additional data until growth will need to happen. One strategy is to get notified when you reach a certain threshold of used space so that you can schedule and kick off growth of the appropriate file(s).