locked
Shrink Database on a DB flagged for replication. Thoughts? RRS feed

  • Question

  • Fellow SQLers

    1- Database A is the primary databse, it is replicated to database B on another server.

    2 - We need to do a LOT of purging on the A db. About half the data. So the USED amount of space will drop from 780 GB to about 400 GB.

    3 - I WILL HAVE an outage window of over 24 hours.

    There are number of performance hits and observcations here. 

    1 - Left as is, the massive deletes will be replicated.

    2 - Blocking will not be an issue because I have an outage window where apps will not be hitting the db.

    3 - When done, we want to free up the unused space on A. Due to processing changes, it will be unlikely it will grow back that big again and we can use the space. So this shrink scenario makes sense.

    My question is the best way around this?

    To me, it appears that the easiest approach might be to cease all replication. Do the deletes on A. Do the shrink on A. Then reinitialize the snapshot to B.  thoughts?

    Thanks

    MG

    Monday, April 27, 2020 5:51 PM

Answers

  • I would just let it go.  There is no reason to do anything special.

    The deletes will be replicated to the other server, just like any other command, and run on the 2nd server, just like the first.  Then if you must, you can shrink the database on both servers.

    • Marked as answer by mg101 Monday, April 27, 2020 9:33 PM
    Monday, April 27, 2020 6:17 PM

All replies

  • I would just let it go.  There is no reason to do anything special.

    The deletes will be replicated to the other server, just like any other command, and run on the 2nd server, just like the first.  Then if you must, you can shrink the database on both servers.

    • Marked as answer by mg101 Monday, April 27, 2020 9:33 PM
    Monday, April 27, 2020 6:17 PM
  • HI tom,

    My concerns is that I was reading whereby the Shrink page movements would also get replicated.

    right/wrong?

    MG

    • Marked as answer by mg101 Monday, April 27, 2020 9:33 PM
    • Unmarked as answer by mg101 Monday, April 27, 2020 9:33 PM
    Monday, April 27, 2020 6:20 PM
  • No. Shrink commands are not replicated to other servers.


    Monday, April 27, 2020 6:45 PM
  • the least painful way of shrinking a database (files) is to add an additional filegroup,

    rebuild the large tables : 

    in this case, pick several of the large tables (excluding lob pages) 

    drop the nonclustered indexes

    rebuild the clustered index to the new filegroup.

    shrink the original files with sufficient room to move the large table back.

    rebuild the clustered indexes to move the tables back

    built the nonclustered indexes

    drop or keep the extra file group


    jchang

    Monday, April 27, 2020 8:49 PM
  • Thanks Jchang61. I understand all of that. My focus was on the replication transfer.

    Thanks,

    MG

    Monday, April 27, 2020 9:33 PM