Sql-server – SQL Server 2008 R2 index rebuild fails with severity 17

You should be aiming to auto-grow as little as possible. Seven times a day is excruciating, even with instant file initialization.

Don't do a Shrink Database. Ever. Shrinkfile, maybe, but only after an extraordinary event. Shrinking it just to grow again is an exercise in futility and should actually be called auto-fragment.

If recovery model is simple, there is no way on earth you should need to grow your log file by 250 GB. The used space in the file will clean itself out automatically over time, unless you started a transaction a month ago and have no intentions of ever committing it or rolling it back.

So my advice would be:

Auto-grow the data file manually during a quiet period to a size that will accommodate several months of growth. What are you saving it for in the meantime?

Set the auto-growth increment for the data file to something relatively small (so that it doesn't interrupt users when it does happen), and alert on this event (you can catch it in the default trace, for example, or through extended events). This can tell you that you are hitting the high point you estimated and it is time to grow manually again. At this point you will want to keep this manual in case you want to add a new file / filegroup on a different drive to accommodate the space, since eventually you will fill the current drive.

Auto-grow the log file to, say, twice the largest it's ever been. It shouldn't auto-grow further unless there is some abnormal transaction holding things up. You should monitor for this event as well, so that you know about them.

Follow these steps:

1. Identify how much space you want to add to the database storage allocation:
   1. Open windows explorer
   2. Right click on the disk drive that your database files exist on
   3. Select properties
   4. Check how much disk space is available and decide how much of this you want to allocate for the database
      (Suggestion: Leave at least 20% disk space free if you house the database files on the same disk as your OS {Sub-Suggestion: Don't do this! Rebuild/migrate your data to it's own disk; you're screwing yourself on I/O.} and leave at least 8% for a pure data disk; these numbers are estimates of what I think the actual percentage suggestions are.)
2. Update the storage allocation for the database.
   1. Open SSMS
   2. Click the "View" tab
   3. Select "Object Explorer"
   4. Expand the "Databases" folder
   5. Right click the database your trying to bulk insert into
   6. Select "Properties"
   7. Click the "Files" list option from the "Select a page" area at the left of the properties window
   8. Find the "Database files" row with the "Filegroup" as "PRIMARY"
   9. Add whatever number of megabytes you want to add to the database allocation to the "Initial Size (MB)" number
   10. Hit "OK"
       (You might also want to consider your "Autogrowth" values while you're here.)

You want to give your database as much storage allocation as you can afford to give it. If it runs out of space you'll receive this error without auto-grow on and if auto-grow is on you'll take a performance hit each time it has to auto-grow. If you are simply out of disk space then that is your answer and you need a bigger disk.