SQL Server auto Grow too large

First, background

The problem we are experiencing is as follows: The automatic growth of the unwarranted end is increased in proportion to this ratio;

650) this.width=650; "title=" image "src=" http://images.cnitblog.com/blog/48305/201308/ 07123224-0e576919994e4165a962a0c922ea8d86.png "alt=" image "Width=" 518 "height=" 173 "border=" 0 "style=" border:0px; background-image:none;padding-left:0px;padding-right:0px;padding-top:0px; "/>

(Figure1: The problem Lies)

When you try to modify the autogrow value using SSMS, the following error occurs:

650) this.width=650; "title=" image "src=" http://images.cnitblog.com/blog/48305/201308/ 07123225-0b321e041cae46b283cab29cb1730959.png "alt=" image "width=" 558 "height=" "border=" 0 "style=" border:0px; background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px; "/>

(Figure2: Error message)

To meet the above problem, we need to solve two problems:

1. Shrink the data file to a certain range of values, freeing up disk space;

2. Reset the auto-growth value, either by the percentage or using the specified space size, I personally prefer to use the value of n*1024m, for reference only;

3. If necessary, you can also make log file contraction;

Second, resolution Process

1. Use the following script to modify the autogrow value:

--1024alter DATABASE [databasename]modify FILE (NAME = N ' FileName ', filegrowth = 1024MB) GO

650) this.width=650; "title=" image "src=" http://images.cnitblog.com/blog/48305/201308/ 07123226-4c8d7cef4ccd4afbbe6e22a48102b0fa.png "alt=" image "Width=" 509 "height=" "border=" 0 "style=" border:0px; background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px; "/>

(Figure 3: effects)

Above is performed according to the effect of 1024MB space execution, if you want to use the percentage of the following SQL can be used:

--10%alter DATABASE [databasename]modify FILE (NAME = N ' FileName ', filegrowth = 10%) GO

2. Use the following script to shrink the data: (by default, MB is the unit)

--12000mbuse [databasename]godbcc shrinkfile (N ' FileName ', 12000) GO

650) this.width=650; "title=" image "src=" http://images.cnitblog.com/blog/48305/201308/ 07123226-3f3c41f6caa641df8190f0b6b01df184.png "alt=" image "Width=" 432 "height=" "border=" 0 "style=" border:0px; background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px; "/>

(Figure4: effect)

650) this.width=650; "title=" image "src=" http://images.cnitblog.com/blog/48305/201308/ 07123227-4a5890664ace46cdb5c10e83b07826eb.png "alt=" image "Width=" 510 "height=" "border=" 0 "style=" border:0px; background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px; "/>

(Figure5: effect)

3. If necessary, you can also make log file contraction: (need simple mode, if in full mode, you need to do is a backup of log files)

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none RGB ( 221,221,221); Background-color:rgb (255,255,255); "/>

--sql server 2005BACKUP LOG [DataBaseName] with NO_LOGDBCC shrinkdatabase ([databasename],truncateonly)--sql Server 2008ALTER DATABASE [DataBaseName] SET RECOVERY simplego--shrink DBCC SHRINKFILE (2,30)--30 unit m, log file size go-change mode to full, and then change back, If you are already simple, you do not have to alter the DATABASE [DataBaseName] SET RECOVERY Fullgo

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Border:none RGB ( 221,221,221); Background-color:rgb (255,255,255); "/>

SQL Server auto Grow too large