CP SQL Database Maintenance
Part of properly maintaining a MS SQL database system requires paying attention to at least these following items:
-
Shrinking the database.
This process recovers both space in the database instance and physical disk space on your drive. Data deleted or purged from you database id removed. A potential drawback is that the physical file fragmentation may increase. The I/O load on your server may increase, throttling system performance while this process is running. Most systems are set to auto-grow the database size. Over time, there is the potential for more disk fragmentation if the database is shrunk too often. Set a schedule that is adequate but not too aggressive.
-
Truncating the Transaction Log.
By default, MS SQL Server does extensive transaction and task logging. This process creates some really large transaction logs. MS SQL sees both the physical database and the transaction log sizes combined as the total size of the database. Consequently, part of your maintenance tasks should include this transaction log truncation step.
- Rebuilding the database.
Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages, and the index rows are reordered in contiguous pages. This normally improves disk performance by reducing the number of page reads required to obtain any requested data.
If you would like to know more about these procedures, or would like to have technician review your existing system set-up, please contact the CCS Retail Systems Support Department.