Sql-server – Expanding Data File with Log Shipping and Mirroring: SQL Server 2008 R2

log-shippingsql serversql-server-2008

I am in a situation where I need to expand a data file by 750 Gb in order to get to 20% free space in the data files. This is a requirement by our third party application developer in order to upgrade to the latest version of their software.

Our environment

Our production environment is called SPOT. It is being replicated via log shipping to an offsite location called SQL_NGOFFSITE. SPOT is also being replicated to a reporting server called SQL_NGREPT01 using Standby/read-only log shipping. SPOT is mirroring to another server called SPOTMIRROR.

If I expand the file NextGen_Core_Data_1 (detailed below), will the file on the replicated servers be automatically expanded or do I need to expand them manually? If I need to expand them manually, do I need to expand the secondaries first or the primary first? Will these shenanigans break mirroring and/or log shipping? Will it adversely affect the Standby/read only log shipping?

Specific Environment Info

name                    type_desc       is_default
PRIMARY                 ROWS_FILEGROUP  0
NEXTGEN_CORE            ROWS_FILEGROUP  1
NEXTGEN_INDEX_1         ROWS_FILEGROUP  0
NextGen_PhysDisk_Data   ROWS_FILEGROUP  0

file_name               physical_name                           filegroup_name
NextGen_System_Data     e:\MSSQL\Data\NGProd_Data.mdf           PRIMARY
NextGen_Index_1         e:\MSSQL\Data\NGProd_Index_1.ndf        NEXTGEN_INDEX_1
NextGen_Core_Data_1     e:\MSSQL\Data\NGProd_Core_Data_1.ndf    NEXTGEN_CORE
NextGen_PhysDisk_Data1  E:\MSSQL\DATA\NGProd_1.ndf         NextGen_PhysDisk_Data
NextGen_PhysDisk_Data2  E:\MSSQL\DATA\NGProd_2.ndf         NextGen_PhysDisk_Data
NextGen_PhysDisk_Data3  E:\MSSQL\DATA\NGProd_3.ndf         NextGen_PhysDisk_Data
NextGen_PhysDisk_Data4  E:\MSSQL\DATA\NGProd_4.ndf         NextGen_PhysDisk_Data
NextGen_PhysDisk_Data5  E:\MSSQL\DATA\NGProd_5.ndf         NextGen_PhysDisk_Data
NextGen_PhysDisk_Data6  E:\MSSQL\DATA\NGProd_6.ndf         NextGen_PhysDisk_Data
NextGen_PhysDisk_Data7  E:\MSSQL\DATA\NGProd_7.ndf         NextGen_PhysDisk_Data
NextGen_PhysDisk_Data8  E:\MSSQL\DATA\NGProd_8.ndf         NextGen_PhysDisk_Data

data_space_name         total_pages total_pages_mb
NEXTGEN_INDEX_1         220623076   1723617.78
NEXTGEN_CORE            468976068   3663875.53
NextGen_PhysDisk_Data   58059935    453593.24
PRIMARY                 195263      1525.49

TYPE    FILE_Name               FILEGROUP_NAME          File_Location                           FILESIZE_MB USEDSPACE_MB    FREESPACE_MB    FREESPACE_% AutoGrow
LOG     NextGen_Log             NULL                    F:\MSSQL\Data\NGProd_Log.ldf            104029.00   3708.02         100320.98       96.44       By 1000 MB - Unrestricted
ROWS    NextGen_Core_Data_1     NEXTGEN_CORE            e:\MSSQL\Data\NGProd_Core_Data_1.ndf    3760000.00  3560139.81      199860.06       5.32        By 10000 MB - Unrestricted
ROWS    NextGen_Index_1         NEXTGEN_INDEX_1         e:\MSSQL\Data\NGProd_Index_1.ndf        1750000.00  1722812.56      27187.44        1.55        By 10000 MB - Unrestricted
ROWS    NextGen_PhysDisk_Data1  NextGen_PhysDisk_Data   E:\MSSQL\DATA\NGProd_1.ndf              70000.00    56703.31        13296.69        19.00       By 1000 MB - Unrestricted
ROWS    NextGen_PhysDisk_Data2  NextGen_PhysDisk_Data   E:\MSSQL\DATA\NGProd_2.ndf              70000.00    56690.69        13309.31        19.01       By 1000 MB - Unrestricted
ROWS    NextGen_PhysDisk_Data3  NextGen_PhysDisk_Data   E:\MSSQL\DATA\NGProd_3.ndf              70000.00    56695.88        13304.13        19.01       By 1000 MB - Unrestricted
ROWS    NextGen_PhysDisk_Data4  NextGen_PhysDisk_Data   E:\MSSQL\DATA\NGProd_4.ndf              70000.00    56687.56        13312.44        19.02       By 1000 MB - Unrestricted
ROWS    NextGen_PhysDisk_Data5  NextGen_PhysDisk_Data   E:\MSSQL\DATA\NGProd_5.ndf              70000.00    56700.38        13299.63        19.00       By 1000 MB - Unrestricted
ROWS    NextGen_PhysDisk_Data6  NextGen_PhysDisk_Data   E:\MSSQL\DATA\NGProd_6.ndf              70000.00    56709.69        13290.31        18.99       By 1000 MB - Unrestricted
ROWS    NextGen_PhysDisk_Data7  NextGen_PhysDisk_Data   E:\MSSQL\DATA\NGProd_7.ndf              70000.00    56698.44        13301.56        19.00       By 1000 MB - Unrestricted
ROWS    NextGen_PhysDisk_Data8  NextGen_PhysDisk_Data   E:\MSSQL\DATA\NGProd_8.ndf              70000.00    56689.94        13310.06        19.01       By 1000 MB - Unrestricted
ROWS    NextGen_System_Data     PRIMARY                 e:\MSSQL\Data\NGProd_Data.mdf           2000.00     1525.50         474.50          23.73       By 100 MB - Unrestricted

Best Answer

Short, simple answer:

Yes, they will automatically expand on the secondary if you are using log shipping, mirroring, or AlwaysOn Availability Groups. Make sure the secondary server has enough disk space to accommodate the growth.

(They would not expand automatically if you were using some form of replication: snapshot, transactional, or merge.)

Related Question