Automatic growth and contraction of SQL files SQL Server

1.4 Automatic File growth and automatic contraction

SQL Server allows you to set the initial value and maximum value of a database, which can automatically increase or contract. These settings can prevent application modification failures caused by database space problems or SQL Server from exhausting hard disk space. Generally, if the database is not very busy, the default settings (Enable Automatic growth) can meet most of the requirements. However, the growth of data files and log files is a task that consumes system resources and affects performance. Therefore, if you rely entirely on SQL Server for Automatic completion, the system performance may be unstable. A well-managed system should take into account the potential space usage requirements in advance and plan and guide the data flow. Try to avoid the use of space to make SQL
Server has to grow automatically. At the same time, it is necessary to ensure that every automatic growth can be completed within an acceptable period of time to meet the needs of client applications in a timely manner.

So how can we achieve this purpose? Before talking about the best configuration, we should first discuss some features of SQL Server data files and log file space applications. Take the following database (1-31) as an example. It has three data files (assuming they belong to the same file group) and two log files (see Table 1-7 ):

Table 1-7 file size before data insertion

Assume that a client needs to insert 40 MB of data and 20 mb of log records. How does SQL server write data to these files? SQL Server has different processing methods for data and logs.

Data Files

SQL Server distributes new data to all spatial data files according to the size of the existing free space of all files in the same file group. If a file is full, SQL Server no longer writes it to this file, but writes it to other files with space.

In the above example, because the free space of the three files is, 40 MB of data is written to the three files according to the ratio of 20 mb: 10 MB: 10 MB.

Figure 1-31 data files and log files of the Sample Database

 

Log Files

SQL Server writes logs in strict order. So although there are two log files, SQL Server writes only one of them at a time point. Only when this file is full can SQL server write it to another file.

In our sample database, 20 mb of log records are written into mydb_log1.

Sometimes we add multiple database files and place them on different hard disks to distribute I/O loads. From the preceding processing method, we can see that if you want to achieve this goal, you must ensure that all data files in the same file group have the same size of free space. (Not as big as these files .) If the data file on a hard disk is full, SQL Server will no longer write data on the hard disk. If the free space is relatively small, the number of SQL Server writes is also relatively reduced.

For log files, since SQL Server only writes one file at a time, adding multiple log files will not be helpful in performance.

If all the files are full, what will SQL Server do? Data Files and log files are slightly different here. For data files, SQL Server selects one of the files (which may be any one) for automatic growth, rather than making every data file grow automatically. All the subsequent data is written into this automatically increasing file until the file is fully written again and SQL server needs to perform the next automatic growth. In other words, relying on automatic growth, we can only see the growth of one file, and it is difficult to enjoy the I/O load balancing effect.

For log files, SQL Server automatically increases the current log files to ensure the continuity of log records.

When an operation triggers Automatic File growth, SQL Server waits for that operation. The previous operation will not continue until the automatic file growth ends. If the auto-increment takes a long time, the original operation will be canceled after the timeout (generally, the default threshold is 15 seconds). This operation will not only roll back, automatic File growth will also be canceled. That is to say, this time the file is not increased. The worst case is that there are a lot of operations that need to apply for new space at a time point, but no one can wait until the file grows automatically and times out. It is reflected in the sense of the end user that no modification operation can be submitted and all modification operations have timed out. Until there is a connection that can wait long enough for SQL Server to finish the automatic growth. After that, other operations that have timed out suddenly become normal.

Why does automatic growth take a long time? This is basically due to the huge increase of space required each time. Data files are stored in units of 8 KB. Therefore, when data files are growing, SQL Server also needs to format these newly added parts. If you want to increase a lot of space at a time, such as uploading GB or dozens of GB, the formatting process will be very time-consuming. SQL Server 2005 and later versions adopt the delayed write technology. As long as the new space for growth has been allocated, this automatic growth will be a success. SQL Server uses a background thread to complete the remaining formatting. This greatly shortens the time for self-growth. The frontend is no longer prone to timeout failures.

Another extreme is that the value of automatic growth is too small. SQL Server needs to grow itself several times to meet operation requirements. In the same size, the time spent in one step is much less than several times. Therefore, the automatic growth value cannot be too small.

In short, pay attention to the following points when setting up auto-Growth of databases.

(1) set to increase by fixed size, rather than by proportion. This avoids unnecessary troubles caused by too much or too little growth at a time. We recommend that you set an increase of 50 MB to 100 MB for a small database. For large databases, the setting increases by 100 MB to 200 MB at a time.

(2) It is necessary to regularly monitor the usage of each data file and try to ensure that the remaining space of each file is as large as possible or the expected proportion.

(3) set the maximum file size to prevent the SQL Server File from consuming disk space and affecting the operating system.

(4) Check the distribution of new data files in a timely manner after the automatic growth. Avoid SQL Server Always writing data to individual files.

In addition to automatic growth, the database also has an automatic contraction function. If this function is set, SQL Server checks the File Usage every 30 minutes. If the free space is greater than 25%, SQL Server automatically runs the DBCC shrinkfile action. Therefore, this function prevents the database from applying for too much space. This setting is undoubtedly helpful for a system with tight hard disk space. However, considering the health and performance of the database, this setting is not recommended for many purposes. This is because:

(1) SQL server automatically increases only when space is exhausted. If you fail to find out the cause of auto-growth, you can avoid the exhaustion of space. Although you can temporarily use the DBCC shrinkfile function to shrink the file size, the database may grow up next time. Database shrinking is only a temporary solution.

(2) shrinking data files will cause more fragments to the files.

(3) Whether database contraction or growth, it is a waste of resources for SQL Server. In systems with heavy loads, performance is particularly affected. They should be avoided as much as possible rather than encouraged.

Therefore, for a busy database, we recommend that you enable the automatic database growth option to avoid application failure due to exhausted database space, but avoid automatic growth. At the same time, try not to use the auto-shrinking function.

 

 

Use master;
Go
Create
Database sales
On
(Name = sales_dat,
Filename =
'C:/program files/Microsoft SQL Server/mssql10_50.mssqlserver/MSSQL/data/saledat. MDF ',
Size =
10,
Maxsize =
50,
Filegrowth =
5)
Log
On
(Name = sales_log,
Filename =
'C:/program files/Microsoft SQL Server/mssql10_50.mssqlserver/MSSQL/data/salelog. ldf ',
Size = 5 MB,
Maxsize = 25 MB,
Filegrowth = 5 MB );
Go

Size is the initial size, maxsize is the maximum size, and filegrouth is the increment (may be the size, may be the percentage)
When the file size increases to a certain value, it will increase in increments.