How to configure the database
Configure the database using best-practices and guidelines.
Follow the tasks below to configure the database engine.
|
Task |
Frequency |
Comments |
How to? |
|---|---|---|---|
|
Set the default location of the data and log files |
Once, when installing SQL Server |
Choose separated volumes for data and log files. |
|
|
Avoid excessive data and transaction log file fragmentation |
Once, when creating the database. |
Define an initial size large enough to avoid fragmentation in future operation. |
How to configure the initial size and growth of the database in SQL Server |
|
Set auto-growth correctly |
Once |
Set % for DB and fixed for Log and with an appropriately growth (in GB). |
|
|
Turn off any scheduled shrink operations |
Once |
Shrinking the database can cause later performance issues. |
|
|
Enable instant file initialization |
Once |
Instant file initialization allows for faster data file allocations. |
|
|
Enable AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS |
Once |
Enabling statistics optimizes the SQL Server Query Processor. |
|
|
Enable page checksums |
Once |
Page checksums increase the integrity of the data written to the database. |
Execute the command ALTER DATABASE <db> SET PAGE_VERIFY CHECKSUM; |
|
Configure alerts for page checksums and torn pages |
Once |
Configure alerts for Severity 24 errors. |
How to configure alerts for page checksums and torn pages in SQL Server |
|
Transaction Log backups |
Daily. Retention: 2 days |
With checksum and file verification. |
|
|
Differential database backups |
Daily. From Monday to Saturday. 01h00 Retention: 15 days |
With checksum and file verification. |
|
|
Full database backups |
Weekly. Sunday 02h00 Retention: 4 weeks |
Including Master and MSDB With checksum and file verification. |
|
|
Verify the database integrity |
Weekly. Sunday. 00h00 (before backup) |
Ensures the database is in a consistent state. |
Execute the command DBCC CHECKDB ('<db>') WITH NO_INFOMSGS, ALL_ERRORMSGS; |
|
Purge data to reduce size |
Yearly |
Delete unnecessary data. |