SQL Server automatically grows too big a problem

I. BACKGROUND

The problems we encounter are shown in the following illustration: Automatic growth is growing in such proportions;

(Figure1: Where the problem lies)

If you try to modify the automatic growth value using SSMS, the following error occurs:

(Figure2: Error message)

Faced with the above problems, we need to solve two problems:

1. The data file is shrunk to a certain range of values, freeing up disk space;

2. Reset the automatic growth of the value, you can according to the percentage, can also use the specified space size, I personally prefer to use n*1024m such values, for reference only;

3. You can also do the contraction of the log file if necessary;

Ii. the process of settlement

1. Modify the automatically growing value using the following script:

--1024
ALTER DATABASE [DataBaseName]
MODIFY FILE (NAME = N ' FileName ', filegrowth = 1024MB)
go

(Figure 3: Effect)

The above is performed in accordance with 1024MB of space execution effect, if you want to use a percentage of the following SQL:

--10%
ALTER DATABASE [DataBaseName]
MODIFY FILE (NAME = N ' FileName ', filegrowth = 10%)
go

2. Use the following script to shrink the data: (the default is in MB)

--12000MB use
[DataBaseName]
go
DBCC shrinkfile (N ' FileName ', 12000)
go

(Figure4: effect)

(Figure5: effect)

3. If there is a need you can also be a log file shrinkage: (need simple mode, if in full mode, you need to do is log file backup)

--sql server DataBaseName
BACKUP LOG [] with no_log
DBCC shrinkdatabase ([databasename],truncateonly)
    
--sql Server 2008
ALTER DATABASE [DataBaseName] SET RECOVERY simple
go
--shrink
DBCC shrinkfile (2,30)  --30 unit m, log file size go 
--Change mode to full, then change back, if it is simple, then do not need
ALTER DATABASE [DataBaseName] SET RECOVERY Full Go

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/