Linko recommends the following settings for both the SQL
Express and the Full SQL Versions.
These settings are defaulted to the values listed so there’s no need to change any of them unless they’re different.
Database
Properties – Options page
Right-click on CTS_Data and then click Properties
Automatic
Auto-close False
Auto-create
statistics True
Auto-shrink False
Auto-update statistics True
Statistics
Query statistics are managed automatically by SQL Server when auto-create statistics and auto-update statistics are set to Yes. This is recommended.
Recovery
Page
Verify Torn page
detection
File Size/Growth:
· CTS_Data_Data:
o Initial Size: 50 MB
o Automatically grow file
o By 10%
o 1 gig max file growth
· CTS_Data_Log: (if Full recovery model implemented)
o Initial Size: 10 MB
o Automatically grow file
o By 20%
o 2 gig max file growth
Recovery Model:
Simple (recommended, with nightly
scheduled backups)
Full (Optional)
Optional – Full Recovery Model
If you choose to implement the Full Recovery model, Linko recommends…
- Incremental transaction log backups should be performed at least twice daily (11:00am and 2:00pm)
- After performing your nightly Full database backup, perform a Full transaction log backup. (SQL will automatically truncate the transaction log after the log is backed up. It will not reduce the size of the physical log)
- After a complete database backup is performed, the transaction log backup series can be restarted. (It is not necessary to keep the transaction log backups created prior to the last full database backup.)
- Never manually truncate a log file until the log file has been backed up.
- To reduce the physical size of the CTS_Data_log file, use DBCC SHRINKFILE
- DBCC SHRINKFILE (cts_data_log)
- GO
Server
Properties – Security page
Right-click on the top level of the Hierarchy which is the
Server Name\SQL Instance, and then click Properties.
SQL Server and Windows Authentication mode should be checked.
