5

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:

  1. Shrink the database with re-organize
  2. 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.

  • 1
    When you change the database to the simple recovery model you don't need to back it up to complete the process. That's only when you change it to full. – Rob Farley Apr 13 '16 at 21:08
4

As this database will only be used for reporting, we are thinking of shrinking the database.

A one time sensible shrink is OK. Disk space is cheap !

Increases the index fragmentation is the side effect of doing a shrink operation. Its advisable to shrink using DBCC SHRINKFILE during maintenance window or when there is minimal activity. Shrink database in chunks using the script.

What other options can we consider for recovering the space?

Another option would be to Script out the database and then bcp out and bcp in the data.

If you take backup / restore route, make sure you enable backup compression (source server) and Instant file initialization (source and destination server). This will help cut down the restore time.

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.

I am not sure, how this is related to shrinking the database. After doing a shrink and reorg, you can take a backup.

Since you are ONLY going to use the database for reporting, why not keep the database in read-only mode ?

4

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.

  • Thanks Rob, The archive database will be created on another server and I think it would not be good idea to import/export data over network. Also, compression would decrease the file size, however this will also affect the performance as SQL have to decompress the data back every time it is queried. – azim Apr 13 '16 at 13:27
  • I think you would find that the cost of compression is pretty negligible (although you have to test of course). It's primarily CPU and on most systems there is at least some CPU to spare. Again it depends on your workload. – Kenneth Fisher Apr 13 '16 at 15:07
  • 1
    @azim - So do your restore first as a different (maybe the original) name,and then copy the data into a new database. This method of getting a "shrunk" database without fragmentation is quite common and the best approach. And do try compressing it, as the CPU load of decompressing it is typically less than the load of getting it off the disk. And you've already hinted that disk space is a premium. But you can test it if you're worried. – Rob Farley Apr 13 '16 at 21:05
3

I think you will find that if you use an SSIS package to move your data between multiple servers @RobFarley's suggestion will work just fine. You are transmitting information across the network either way. In one case a backup file and in the other the data. However if you are absolutely against the idea you can use a combination of the two ideas.

  1. Restore your backup to the new location.
  2. Create a new database.
  3. Move your existing data into it.
    • Compression is a good idea. You will probably find that the CPU cost isn't going to be that high and you will see a performance increase as well as a space decrease.
    • You can also use poor mans compression and set your FILLFACTOR to 100%.
  4. Drop your restored database.

I wouldn't try using your initial method.

  1. Shrink the database with re-organize
  2. Re-organize indexes

By shrinking the database you shred the indexes (which is why you want to re-build them afterwards). Note I said rebuild not reorganize. That's because reorganizing only defragments the leaf level of the index. Unfortunately when you rebuild it requires extra space in the database. That means that your database is going to grow again (by at least the size of your largest table).

One last option.

  1. Restore your backup to the new location
  2. Create a new filegroup with enough space to hold all of your data (or possibly a bit less & let it grow a bit to be sure you get the minimum space).
  3. Re-create your indexes (including the clustered ones) onto the new filegroup using whatever form of compression you choose. This removes data from the PRIMARY filegroup and puts it into your new filegroup.
  4. Once all of your user data is in the new filegroup you can safely shrink the PRIMARY filegroup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.