on 09-12-2012 5:40 AM
Hi,
in the screenshot I can see that all three data files have a red rating .... as you've expanded the details for the third one I can see that the file doesn't provide any free space and was flagged red for this reason.
Can you show us what's the reason for the red rating of the other two files?
Please note:
As long as ANY datafile still provides free space, SQL Server won't autogrow, e.g.:
File 1 - autogrow set to 100 MB - still provides 1 MB of free space
File 2 - autogrow set to 100 MB - is 100% full, no free space at all
File 3 - autogrow set to 100 MB - is 100% full, no free space at all
Even in such a situation SQL Server will not grow any files. Autogrow works differently:
SQL Server waits until ALL files are 100% full and provide no free space at all. Then, it will check if there is any file which is allowed to autogrow and if yes, it will pick one of the files where this applies and will grow this single file.
It's a common misconception that if you set autogrow for a file it will be immediately autogrown as soon as this very file is completely full. SQL Server will never autogrow any file as long as at least one of the datafiles still provides free space. This is why you should always grow your files manually before they are full to avoid uneven distribution of data.
Please also see SAP KBA 1660220, point 9 which explains in more detail how autogrow works and how it doesn't work. If you don't have access to SAP Notes you can find the same information here:
Microsoft SQL Server Common Misconceptions
Long story short:
- check if ANY of your data files still provides free space (doesn't matter how few it is)
- if this is the case, SQL Server doesn't grow the files which are currently full because it's designed to work this way
best regards,
beate
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
check the treshold and adjust autogrowth accordingly,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Philip,
Auto growth have been enables in percentage (or) in MegaBytes. Did you check the space in file system.
Regards
Pavan.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
What if you restart SQL server service?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.