patternsqlMinor
Shrinking database which is not having any insert/update
Viewed 0 times
insertupdateanyhavingdatabasewhichshrinkingnot
Problem
We have a process of archiving wherein we take the backup of current database and restore it as xxx archive database. Now this database contains previous data and will not have any insert/update/Delete.
As this database will only be used for reporting, we are thinking of shrinking the database. However, this increases the index fragmentation. We are thinking of following below steps:
Will this hamper the database performance? What other options can we consider for recovering the space?
Update: In the process, We change the recovery model to simple, backup the database to move the database out of Pseudo-Simple state, and then planning to shrink the database.
As this database will only be used for reporting, we are thinking of shrinking the database. However, this increases the index fragmentation. We are thinking of following below steps:
- Shrink the database with re-organize
- Re-organize indexes
Will this hamper the database performance? What other options can we consider for recovering the space?
Update: In the process, We change the recovery model to simple, backup the database to move the database out of Pseudo-Simple state, and then planning to shrink the database.
Solution
Instead of doing a backup/restore, consider moving data across into a new, presized database, and creating new indexes on this data. This should keep your file size small and your indexes unfragmented. Also make sure you have compression turned on.
Context
StackExchange Database Administrators Q#135150, answer score: 4
Revisions (0)
No revisions yet.