Thực hành tốt nhất DBAs ghét bất kỳ các thiết lập mặc định vì cơ sở dữ liệu autogrowth bị khóa trong một autogrowth. Tăng 1MB mức tăng trưởng có thể là quá nhỏ và sẽ gây gián đoạn thường xuyên. Một lỗi khác là để thiết lập autogrowth với tỷ lệ phần trăm. Là cơ sở dữ liệu phát triển, như vậy sẽ là thước tăng trưởng. | Part IV Developing with SQL Server The file sizes and growth options can be adjusted in code with the ALTER DATABASE DDL command and the MODIFY FILE option. The following code sets NewDB s data file to manual growth and sets the size to 25MB ALTER DATABASE NewDB MODIFY FILE Name NewDB SIZE 25MB MAXSIZE 2Gb FILEGROWTH 0 J J To list the databases using code query the catalog view. Best Practice Many DBAs detest the default autogrowth settings because the database is locked during an autogrowth. Growing by 1MB per growth is probably far too small and will cause frequent interruptions. Another common error is to set the autogrowth to a percentage. As the database grows so will the growth size. For serious production databases the best solution is to monitor the data size and manually grow the database but leave autogrowth on as a safety factor. Using multiple files Both the data file and the transaction log can be stored on multiple files for improved performance and to allow for growth. Any additional or secondary data files have an .ndf file extension by default. If the database uses multiple data files then the first or primary file will contain the system tables. While it does not enable control over the location of tables or indexes this technique does reduce the I O load on each disk subsystem. SQL Server attempts to balance the I O load by splitting the inserts among the multiple files according to the free space available in each file. As SQL Server balances the load rows for a single table may be split among multiple locations. If the database is configured for automatic growth all of the files will fill up before SQL Server increases the size of the files. Creating a database with multiple files To create a database with multiple files using Management Studio add the filename to the file grid in the Files page of the Database Properties dialog see Figure 20-3 . To create a database with multiple data files from code add the file locations to the
