First all our autogrow settings are either turned off or the file size is set to the max autogrow size. I guess same difference as I came in after the setup.

Although I've never had this problem, I've been looking for an answer to a possible condition: I need my file to grow for example a tiny amount, say 100 MB for a insert to happen. However my autogrow is set to say 500MB. The disk has 450 MB available. What would happen in that scenario. And just say this insert happens once in a while at night, so I had no idea that it was going to happen, so I couldn't or didn't pre grow the file.

I'm really just curious about how sql server would handle this.

  • 1
    It will fail, as there's not enough space to grow. SQL server datafile won't grow as "required" (the 100 MB) It will grow what is configured to grow (500MB) but since it doesn't have enough free space, the auto growth will fail, it will be reported in the SQL Server Error log. – dbamex Dec 2 '19 at 18:15
  • @dbamex you should make that an answer – alroc Dec 2 '19 at 20:07

SQL server would handle this aborting your transaction and throwing the following error:

Error message

I got this message from a test where I designed a scenario close enough to what you described:

Database definition

I placed the database MyDB in a small disk with 100MB in size. Inserts in the table dbo.TextTable worked fine until the file needed to grow (500MB) in order to acomodate more data, but failed due to the lack of space in the disk.

Microsoft says that

The autogrow setting cannot grow the database size beyond the limits of the available disk space on the drives for which files are defined. Therefore, if you rely on the autogrow functionality to size your databases, you must still independently check your available hard disk space. The autogrow setting is also limited by the MAXSIZE parameter you select for each file. To reduce the possibility of running out of space, you can monitor the Performance Monitor counter SQL Server: Databases Object :Data File(s) Size (KB) and set up an alert for when the database reaches a certain size.

| improve this answer | |
  • thanks for testing . this is what i thought. – user18843 Dec 3 '19 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy