Sql-server – How should I best handle a rapidly growing database

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.

Well, since you have the CSV files, they can serve as a backup to the staging tables, so I see no benefit to keeping them in the primary database (or maintaining the staging tables after the import is done). If those staging tables can be discarded / truncated once the import is finished, then they really should be separate and have as minimal impact on the primary database as possible.

Also, I see no value in putting the staging tables in the primary database in any case, no matter how permanent the loaded data will be. And if you need the data to be permanent, you can always reference the data even if it is in a different database - in fact that is arguably better, because now:

• you can handle that data alone independently:

  • set the recovery model of that database to simple
  • mark it as read only
  • heck back it up and drop it, knowing that you can always recover it later
  • restrict access to it in simpler and higher level ways than you could tables in the primary database (to prevent the "until someone truncates it" fear)
• or just abandon it because, as I said above, your CSV files are already acting as a secondary backup.

In addition to the cons you've already identified, well, they're essentially just scratch tables. Even if you're only on a single spindle, why bother disrupting the primary database? There's no penalty in crossing database boundaries on the same instance, e.g. INSERT ... FROM db.dbo.table; compared to INSERT ... FROM dbo.table;.

Finally, when you say "autogrowth" I cringe. If you know your database is going to get bigger than it is now, why not manually expand it to the size you need it to be? Autogrow is a performance nightmare when it happens during peak activity - and you will certainly notice it happening even on a single machine and even as the only one performing any work - basically all of your data transfer will halt when either the data or log file needs to grow. This is particularly true if you've left the autogrow settings at the current, ridiculous, defaults, and/or if you don't have instant file initialization. Don't forget to set your log growth settings appropriately too, as log files are not able to take advantage of IFI.