Sunday, October 30, 2011

Tips: How to configure SQL Server 2008 to automatically manage the file size

Manually or automatically managing the database and the log size with SQL Server 2008 is a simple process. For this, you can use the SQL Server Management Studio or Transact-SQL. Then, you can configure the log size or the database.

Now, let’s check out the way we configure the automatic management of database and log size using the SQL Server Management Studio.

•    First, it is required to start the Start SQL Server Management Studio.
•    Now, in the Object Explorer view, it is required to connect to the appropriate server and then work on your way down to the Database folder.
•    After that, it is required to right click the database we need to configure and then select Properties from the shortcut menu.
•    Then, it is required to select the files from the Select A Page list in the Database Properties dialog box.
•    Now, you can check each data and the log file are associated with the database under the Database files.
•    Then, it is required to perform the following steps for each data and log file.

1.    After performing the above steps, click the button to the right of the file’s Autogrowth box to adjust the related settings to display the Change Autogrowth For dialog box.
2.    Then, set the file to grow using a percentage or an amount in megabytes and then restrict the maximum file growth to to a specific size or allow unrestricted file growth.
3.    Now, click OK.
•    Then, you can access the options page for setting Auto Shrink to True.
•    This Auto Shrink compacts and shrinks the database periodically.
•    Then, click OK if everything is finished.
•    Now, restart the server for the settings to take the effect immediately.
B  y ,