devxlogo

Specify a Reasonable Maximum Size When Creating a Database

Specify a Reasonable Maximum Size When Creating a Database

In an effort to simplify life for administrators, SQL Server 7 allows database files to grow automatically. That’s good news for you, since you will no longer be woken up in the middle of the night because the database ran out of space. Therefore, it is very tempting, when creating a database file, to omit a maximum growth size. However, as with extra dessert, too much of a good thing in SQL Server can also be dangerous. Here are two scenarios to illustrate the problem with uncontrolled growth. In the first scenario, a user kicks off a badly written query that uses a tremendous amount of space in tempdb. The tempdb database grows to fill all the available space. There is no room left for valid growth on the application databases. In another scenario, a salesperson has an application using SQL Server loaded onto a laptop. After long use, the database grows to fill all available space, until no room is left for the temporary files Windows uses, or for the paging file to expand. The machine grinds to a halt. The moral of the story is: Specify a reasonable maximum size when creating a database.

devx-admin

Share the Post: