-9

can we make our database ( what ever its size) to not auto grow at all ( data and log file ) ?

if we proceed with this choice maybe we will face problems when the database is full during the on hours

Kermit
  • 33,827
  • 13
  • 85
  • 121
user3240791
  • 1
  • 3
    Welcome to Stack Overflow! Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it. – Taryn Jan 27 '14 at 14:31
  • 1
    You want to grow your database by restricting its size? Wut? – Martin Bean Jan 27 '14 at 14:31
  • Do you mean block all incoming transactions? – Randy Jan 27 '14 at 14:34
  • 1
    Just make it read only. Chance of growth: 0%. – Aaron Bertrand Jan 27 '14 at 14:58
  • Are you perhaps asking about how to set the maximum size of a database or log file? – Andriy M Jan 27 '14 at 14:59

1 Answers1

3

Typically the way you prevent growth events from occurring during business hours is by pre-allocating the data and log files to a large enough size to minimize or completely eliminate auto-growth events in the first place. This may mean making the files larger than they need to be right now, but large enough to handle all of the data and/or your largest transactions across some time period x.

Other things you can do to minimize the impact of growth events:

  • balance the growth size so that growth events are rare, but still don't take a lot of time individually. You don't want the default of 10% and 1MB that come from the model database; but there is no one-size-fits-all answer for what your settings should be.

  • ensure you are in the right recovery model. If you don't need point-in-time recovery, put your database in SIMPLE. If you do, put it in FULL, but make sure you are taking frequent log backups.

  • ensure you have instant file initialization enabled. This won't help with log files, but when your data file grows, it should be near instantaneous, up to a certain size (again, no one-size-fits-all here).

  • get off of slow storage.

Much more info here:

How do you clear the SQL Server transaction log?

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490