Tip: Configure SQL Server 2008 to Automatically Manage File Size

With SQL Server 2008, you can manage database and log size either automatically or manually. You can use SQL Server Management Studio or Transact-SQL to configure database or log size.

Using SQL Server Management Studio, you can configure automatic management of database and log size by performing the following steps:

1. Start SQL Server Management Studio. In the Object Explorer view, connect to the appropriate server, and then work your way down to the Databases folder.

2. Right-click the database you want to configure, and then select Properties from the shortcut menu.

3. Select Files from the Select A Page list in the Database Properties dialog box. Each data and log file associated with the database is listed under Database Files. For each data and log file, do the following:

• Click the button to the right of the file’s Autogrowth box to adjust the related settings. This will display the Change Autogrowth For dialog box.
• Set the file to grow using a percentage or an amount in megabytes, and then either restrict the maximum file growth to a specific size or allow unrestricted file growth.
• Click OK.

4. Optionally, access the Options page and set Auto Shrink to True. Auto Shrink compacts and shrinks the database periodically.

5. Click OK when you have finished. Your changes take effect immediately without restarting the server.

From the Microsoft Press book Microsoft SQL Server 2008 Administrator's Pocket Consultant.

Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.

For more Tips on other products, visit the TechNet Magazine Tips index.