Learn with me Microsoft SQL Server Internals (3.3)

Directory

• • Directory
  • Correctly scaling and shrinking SQL Server data database files
  • Resources

Correctly expanding and shrinking the SQL Server data (database) file

After creating the MS SQL Server database, we will adjust an important option, that is, how the data files and log files grow. While MS SQL Server can shrink the size of the data file and the log file if necessary, it is not recommended for individuals to do so.

The following shows how the database files are automatically grown through SSMs:

Individuals are advised to initially give enough data to log space and then set to automatically grow by a fixed MB size. If you grow as a percentage as shown in the figure, SQL Server consumes a large amount of time later because of the growing file size, so be sure to use a fixed megabyte.

Before using instant file initialization to improve SQL Server performance, it is recommended that you review the article "Database Instant file Initialization" and "improve SQL Server performance with instant file initialization", Correctly master the use of the data file instant initialization function.

To enable the Instant file initialization step:
1. In the Run Command Window input: secpol.msc, open "Local Security policy"
2. Under Local Policies, user rights Assignment, perform volume maintenance tasks, add SQL Server-related domain administrative users or local computer administrative users with SQL Server administrative rights

For manual shrinking of data files, you can use the following T-SQL statements:

DBCC SHRINKFILE

Note: If necessary, shrink the data file manually. Instead of turning on the auto-shrink feature.

Resources

1. Microsoft SQL Server Internals-Chapter 3

2.Database Instant File Initialization
Https://technet.microsoft.com/en-us/library/ms175935%28v=sql.110%29.aspx

3. Improve SQL Server performance with instant file initialization
Http://www.tuicool.com/articles/ba26jq

4.ALTER DATABASE (Transact-SQL)
Https://technet.microsoft.com/en-us/library/ms174269%28v=sql.110%29.aspx

Update by Hyperwang at 2016/05/26

Learn with me Microsoft SQL Server Internals (3.3)