How to Shring Data and Log file of Mirrored SQL Database?

  • Hi Guys

    Looking for Steps to perfrom for shrinking Data and log files for Mirrored Database. Can any one give insturction step by step . would be great if have script too

    Thanks

    --------------------------------------------------------------------------------

  • Same as a non-mirrored database. What you do to the primary will be sent to the secondary. The big concern is load while this occurs.

    Shrinking the log makes sense, but the size of your log needs to handle the peak size between log backups with a pad. Make sure you know what this is.

    For data files, be careful. You need free space in these files, and the amount of space depends on data growth as well as the maintenance you need. Calculate the space you need for at least the next 3 months and size your data file there. Then if you need to shrink, after you're done, you will need to rebuild some indexes because you will have fragmented them. I'd look at the index maintenance script from http://www.sqlfool.com.

    Other thoughts: http://wp.me/p14wgJ-di

  • logicinside22 (10/17/2013)


    Hi Guys

    Looking for Steps to perfrom for shrinking Data and log files for Mirrored Database. Can any one give insturction step by step . would be great if have script too

    Thanks

    --------------------------------------------------------------------------------

    Is your mirror session synchronised, the transaction log will continue to grow if the mirror session is disconnected for example.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • If you must shrink the data files, do that first.

    The index rebuilds and the concomitant latency will require a lot of log file space.

    Make sure you communicate to the system owners that the system may have latency during and after the index rebuilds.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply