none
SQL AlwaysOn backups Shrink Database

    Question

  • Hi,

    I am configuring SQL AlwaysOn backups in our environment. The first step will backup the databases, that works fine, the last step is to remove files older than 3 days, that works fine ...The problem is the Shrink Database command that runs second wont work ..it keeps failing with the error:

    "Property Size is not available for Database 'Database_Name'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. "

    I've googled but cant find anything related to this error. I can run sp_helpdb and get all of the information i need (when connecting in with the ApplicationIntent=ReadOnly switch)

    I am running the backups from the secondary server. Is it not possible to Shrink the DB from the secondary server as this runs fine from the primary?

    cheers,

    Jon

    Wednesday, July 25, 2012 2:17 PM

All replies

  • Is there any reason why you would like to shrink the database? It is not a recommended practice to regularly shrink SQL Server databases

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    Wednesday, July 25, 2012 5:42 PM
  • Tengo el mismo problema, al parecer, al estar en un proceso continuo de Sincronización, no puede reducir el log de transacciones. Creo que hay un Bug ahí. Lo que estoy tratando de hacer ahora es:

    1. Bajar la instancia Secundaria
    2. Generar Shink sobre la Instancia Principal, de esta manera no va a sincronizar con la segunda instancia
    3. Levantar la segunda Instancia
    4. Ver que pasa....

    Si me resulta te informo.

    Saludos, 

    Claudio Arancibia O.

    Friday, July 26, 2013 5:11 PM
  • as per -

    http://msdn.microsoft.com/en-us/library/ff877884.aspx

    Optionally, you can configure one or more secondary replicas to support read-only access to secondary databases, and you can configure any secondary replica to permit backups on secondary databases.

    Also see-
    Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups)
    http://msdn.microsoft.com/en-us/library/78f3f81a-066a-4fff-b023-7725ff874fdf


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Friday, July 26, 2013 5:49 PM
  • Hi Claudio,

    First off, it is not a recommended practice to shrink databases. You are introducing both external fragmentation as well as internal fragmentation on your databases. Check out this blog post on the effects of shrinking on your databases. I'm strongly against shrinking databases because of the effects of doing so. Second of all, you will not be able to perform any task on the database that causes any change - record change, metadata change, database property change, etc. - in the secondary replica because they are  in constant recovery mode. The changes will have to come from the primary replica in order for the changes to be propagated to all the other replicas. This concept is similar to how database mirroring works. By shutting down the second instance, you are introducing an unnecessary risk on your primary instance since the transactions will still be kept in the log (for synchronous Availability Groups) and you will not be able to truncate nor shrink the log any further


    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn


    Friday, July 26, 2013 5:59 PM