Summary: Keep the autogrowth ON to avoid of failing the
programs/procedures calling SQL Server, but you should use autogrowth
at a minimum. Autoshrink is even more discouraged.
1. HOW DOES SQL SERVER WRITE DATA INTO THE FILE?
Suppose myDB has three data files and
two logs as below. Now we need to insert 40MB data and 20MB log. How does SQL
Server write them to these files?
|
FileName |
Current File Size in MB |
Free Space in MB |
|
MyDB_Primary |
2000 |
200 |
|
MyDB_Secondary1 |
2000 |
100 |
|
MyDB_ Secondary2 |
2000 |
100 |
|
MyDB_Log1 |
1000 |
500 |
|
MyDB_Log2 |
1000 |
1000 |
For the data file, SQL Server writes the data to the three data
files proportionally based on the free space available. Thus, 20MB to MyDB_Primary, 10 MB to MyDB_Secondary1, and 10 MB to MyDB_ Secondary2. If you put these files on different
disks, you will have the performance gain due to the parallel IO load balance.
However, if the data files are full, SQL Server will pick up one for autogrowth (oh, yeah, just one, not all of the three).
After this file reaches its maximum, SQL Server will autogrow the 2nd file…thus, parallel IO
gain does not occur in the case of autogrowth.
For the log data, SQL Server writes to one log file at a time
until it is full, no matter how many log files you have. After it is full, SQL
Server autogrows it until it reaches the maximum.
Then SQL Server uses the 2nd log file…
2. HOW TO CONFIGURE THE SETTINGS?
You can configure the autogrow and autoshrink settings by using one of the
following:
a.
An ALTER DATABASE statement
b.
SQL Server Management Studio
c.
The sp_dboption stored
procedure (deprecated in SQL Server 2005)
d.
You can also configure
the autogrow option when you create
a database.
To view the current settings, run
sp_helpdb [ [ @dbname= ] 'name' ]
Keep in mind that the autogrow settings
are per file. Therefore, you have to set them in at least two places for each
database (one for the primary data file and one for the primary log file). If
you have multiple data and/or log files, you must set the options on each file.
Depending on your environment, you may end with different settings for each
database file.
3. BEST
PRACTICES OF AUTOGROWTH AND AUTOSHRINK (SEE HTTP://SUPPORT.MICROSOFT.COM/KB/315512 FOR
MORE)
1) Autogrowth
· Plan ahead, do not rely on autogrowth
as a routine practice
·
If autogrowth
is needed, do not make the size of each growth too large (many data requests do
not have the patience to wait, the default threshold is 15s) or too small
·
Use size instead of %,
50-100MB for a small database, 100-200MB for a large database
· Regularly monitor the data files to ensure they have roughly
equal size of free spaces or whatever you designed.
SELECT name ,
log_reuse_wait_desc ,
recovery_model_desc
FROM sys.databases
WHERE name = 'YourDBName'
GO
SELECT name AS 'FileName' ,
physical_name AS 'PhysicalName' ,
size
/ 128 AS 'TotalSizeinMB' ,
size
/ 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) /
128.0 AS 'AvailableSpaceInMB' ,
CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) /
128.0 AS 'ActualSpaceUsedInMB' ,
( CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) /
128.0 ) / (
size
/ 128 ) *
100. AS '%SpaceUsed'
FROM sys.database_files;
· Set a maximum to avoid of filling the entire disk
· If autogrowth occurred, monitor the
file usages, take actions to avoid of writing data to one file only.
alter database <DBName> modify file(name='DATA1',
size=25GB) /*25GB
ist just an example*/
alter database <DBName> modify file(name='DATA2',
size=25GB)
alter database <DBName> modify file(name='DATA3',
size=25GB)
alter database <DBName> modify file(name='DATA4',
size=25GB)
2) Autoshrink
· If it’s ON, SQL Server checks the data file every 30 minutes, if
free space is over 25%, it will use DBCC ShrinkFile
to release the free space to the operating system. So it may only be a
reasonable option for a very intensive disk system, which is rare nowadays.
·
A performance killer.
·
It also creates fragments
·
You need to prevent the
crazy autogrowth, not to use autoshrink
· Bottom line: do not use it unless you have a strong reason.
