locked
How to Increase database / log size of mirrored database RRS feed

  • Question

  • Hi All,

    I have been asked to increase the default database and log size of two databases, both of which are mirrored.

    I have been trying to find out what else I need to do in this case other than the standard resizing.

    Are there extra steps I need to perform as these dbs are mirrored (SQL Mirroring)?

    Farren

    Wednesday, January 14, 2015 12:32 PM

Answers

  • Run it on the Principal

    ALTER DATABASE dbname
    MODIFY FILE 

          NAME =dbname_Log 
        , SIZE = 2000  ----(2gb)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 14, 2015 1:30 PM
  • Hi Farren,

    Based on my test, if you choose to expand the mirrored database by increasing the size of an existing data or log file, you can directly execute the ALTER DATABASE statement on the Principal server as Uri’s post, but before that please make sure the mirroring status is synchronized. You can check the mirroring configuration and partner status with the following query.

    SELECT DB_NAME(DATABASE_ID) 'DBNAME',mirroring_state,
    mirroring_state_desc,mirroring_role,mirroring_role_desc,
    mirroring_partner_name,mirroring_partner_instance
    FROM sys.database_mirroring
    WHERE mirroring_guid IS NOT NULL
     
    However, if you choose to expand the mirrored database by adding a new file to the database, you need to perform extra steps as follows.
       1. Check the mirroring configuration and partner status with above query.
       2. Run the below command on the principal server to break the mirror.  
    ALTER DATABASE DatabaseName SET PARTNER OFF
       3. Create your database file on the principal server.
       4. Run a log backup on the principal server.
       5. Restore this log backup on the mirrored server using the NORECOVERY and MOVE options.
       6. Re-establish mirroring between the servers for this database.


    Reference:
    Increase the Size of a Database
    How to add a database file to a mirrored SQL Server database

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Thursday, January 15, 2015 6:56 AM

All replies

  • find why the size is getting bigger

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

    Wednesday, January 14, 2015 1:21 PM
  • Hi,

    This is a new-ish install (still in QA), and I have been asked to set the database sizes to a specific level to decrease performance problems that may occur as the new data-set is added. So the growth itself is not a problem.

    I'm just not sure what else I need to do to make the change.

    Regards

    Farren

    Wednesday, January 14, 2015 1:27 PM
  • Run it on the Principal

    ALTER DATABASE dbname
    MODIFY FILE 

          NAME =dbname_Log 
        , SIZE = 2000  ----(2gb)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 14, 2015 1:30 PM
  • Hi Farren,

    Based on my test, if you choose to expand the mirrored database by increasing the size of an existing data or log file, you can directly execute the ALTER DATABASE statement on the Principal server as Uri’s post, but before that please make sure the mirroring status is synchronized. You can check the mirroring configuration and partner status with the following query.

    SELECT DB_NAME(DATABASE_ID) 'DBNAME',mirroring_state,
    mirroring_state_desc,mirroring_role,mirroring_role_desc,
    mirroring_partner_name,mirroring_partner_instance
    FROM sys.database_mirroring
    WHERE mirroring_guid IS NOT NULL
     
    However, if you choose to expand the mirrored database by adding a new file to the database, you need to perform extra steps as follows.
       1. Check the mirroring configuration and partner status with above query.
       2. Run the below command on the principal server to break the mirror.  
    ALTER DATABASE DatabaseName SET PARTNER OFF
       3. Create your database file on the principal server.
       4. Run a log backup on the principal server.
       5. Restore this log backup on the mirrored server using the NORECOVERY and MOVE options.
       6. Re-establish mirroring between the servers for this database.


    Reference:
    Increase the Size of a Database
    How to add a database file to a mirrored SQL Server database

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Thursday, January 15, 2015 6:56 AM
  • Thanks Uri
    Thursday, January 15, 2015 9:32 AM
  • Thanks Lydia,

    This is just what I needed to know.

    Regards

    Farren

    Thursday, January 15, 2015 9:33 AM