Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Dec 4 '12 at 4:35

This question came from our site for professional and enthusiast programmers.

1  
this q would be better on the DBA site. The size of the file affects if you have a small pause when the file needs to grow. When the db runs out of space in a file, it needs to allocate more space on the drive and that initialization takes time. On the otherhand, if you run out of drive space, the server can go down (depending on which drive(s) filled up.) Optimally, a db is sized initially to the expected size. –  MatthewMartin Dec 4 '12 at 2:52
    
Additionally the "Space available" indicates how much space is left before the file needs to grow again. –  FettMo Dec 4 '12 at 2:55

1 Answer 1

up vote 4 down vote accepted

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.