0

We had a crash on our SQL AAG where our secondary database ended up being corrupt. The Primary database is all goood, and still working, and was not affected in any way.

So now I try to get the primary database back into the AAG Availability Database group, following the normal steps; CLean up on secondary SQL instance, by removing database, removing files, doing a test on the WFCM, doing the SQL tests for AAG listener and Replicas. All in the green, and good to go. Before the Add database wizard will accept the database, a full backup is required, so I already did this.

Running the wizard, Status is "Meets requirements", and connecting to secondary SQL instance works fine, and whether choosing Automatic Seeding (preferred) or Full database and log backup, it gives same warning:

Insufficient free space for data and log files on the server instance that hosts secondary replica <replica/Instance>

I can of course click Next, but this does not make the database compliant/healthy on secondary replica, so no gain by doing that.

I can add more disk space on the replica for the relevant drives, but how much space is needed?

The database file size is 2.2 TB The logfile size is 70 GB Running the Add database wizard say, the size is 2.4 TB

On the secondary server, I have expanded the data disk to 5 TB and the log disk to 1 TB, but I still see the warning

Anyone got any ideas for how large these disks needs to be??

In the past, I always thought that it required a "double-up" when adding/re-adding the database to the replica/secondary server, but this seems no longer to be the case, or the SQL Server reads other disk sizes, than file explorer

Details: Both servers running Windows Server 2019 with latest updates, SQL Server 2019, CU 18 (due to errors in later builds, we cannot use these yet)

Hi @Sean As I've done this operation a couple of times on this setup, I indeed did ensure locations are the same, but all good points, that I should have put in here. No mount points used at all. Primary server disk free space - Secondary server - expanded to try to get the AAG wizard to run.

D   95329                           D   83481
F   261001 - LDF                    F   1651181 - LDF                   
G   196873                          G   204041
U   1054054 - MDF                   U   4619104 - MDF

Here are file locations on primary

DBName  FileType    Location
master  ROWS    D:\Data\MSSQL15.instance_name\MSSQL\DATA\master.mdf
master  LOG     D:\Data\MSSQL15.instance_name\MSSQL\DATA\mastlog.ldf
tempdb  ROWS    G:\TempDB\instance_name\tempdb.mdf
tempdb  LOG     G:\TempDB\instance_name\templog.ldf
tempdb  ROWS    G:\TempDB\instance_name\tempdb_mssql_2.ndf
tempdb  ROWS    G:\TempDB\instance_name\tempdb_mssql_3.ndf
tempdb  ROWS    G:\TempDB\instance_name\tempdb_mssql_4.ndf
model   ROWS    D:\Data\MSSQL15.instance_name\MSSQL\DATA\model.mdf
model   LOG     D:\Data\MSSQL15.instance_name\MSSQL\DATA\modellog.ldf
msdb    ROWS    D:\Data\MSSQL15.instance_name\MSSQL\DATA\MSDBData.mdf
msdb    LOG     D:\Data\MSSQL15.instance_name\MSSQL\DATA\MSDBLog.ldf
UserDB  ROWS    U:\Unik-DB\Userdb.mdf
UserDB  LOG     F:\Logs\Userdb_log.ldf
UserDB  ROWS    U:\Unik-DB\user_ESDH.ndf

And secondary

master  ROWS    D:\Data\MSSQL15.instance_name\MSSQL\DATA\master.mdf
master  LOG     D:\Data\MSSQL15.instance_name\MSSQL\DATA\mastlog.ldf
tempdb  ROWS    G:\TempDB\instance_name\tempdb.mdf
tempdb  LOG     G:\TempDB\instance_name\templog.ldf
tempdb  ROWS    G:\TempDB\instance_name\tempdb_mssql_2.ndf
tempdb  ROWS    G:\TempDB\instance_name\tempdb_mssql_3.ndf
tempdb  ROWS    G:\TempDB\instance_name\tempdb_mssql_4.ndf
model   ROWS    D:\Data\MSSQL15.instance_name\MSSQL\DATA\model.mdf
model   LOG     D:\Data\MSSQL15.instance_name\MSSQL\DATA\modellog.ldf
msdb    ROWS    D:\Data\MSSQL15.instance_name\MSSQL\DATA\MSDBData.mdf
msdb    LOG     D:\Data\MSSQL15.instance_name\MSSQL\DATA\MSDBLog.ldf

Here the UserDB is missing, as I removed/deleted it - but both U and F drive exist with additional amount of space as the wizard has kept complaining of missing space.

I have now started a restore to secondary replica, and will try a Join Only in the wizard.

6
  • 1
    The user databases are only on U and F, what are the on-disk sizes (not used space) of each database? Oct 10, 2023 at 13:53
  • Only 1 userdatabase. Size: 3290087,38 MB with Space available: 279966,18 MB
    – Tim
    Oct 11, 2023 at 6:54
  • Can you put the output of select name, type_desc, size from sys.database_files for the user database? Oct 12, 2023 at 10:49
  • name type_desc size UserdbData ROWS 320704552 UserdbLog LOG 99743992 ftrow_ESDH ROWS 682640
    – Tim
    Oct 13, 2023 at 11:05
  • 1
    According to that data and the xp_fixeddrive, assuming the no mount points and everything, you should have space. Oct 13, 2023 at 11:14

2 Answers 2

0

Insufficient free space for data and log files on the server instance that hosts secondary replica <replica/Instance>

You'll need to look at the location of all of the files in your database, account for mount points, if any, and check the secondary replica to make sure that the drive locations, free space, and mount points (including space) match and are accurate. Clearly something is not.

Please put the output of your database file locations on the primary replica and xp_fixeddrives on the secondary replica if you'd like more help.

In the past, I always thought that it required a "double-up" when adding/re-adding the database to the replica/secondary server, but this seems no longer to be the case, or the SQL Server reads other disk sizes, than file explorer

This was never the case, the restore (whether manually or via automatic seeding) needs the size of the database.

I can of course click Next, but this does not make the database compliant/healthy on secondary replica, so no gain by doing that.

If you know better than the wizard, there is nothing stopping you from not using the wizard and doing this manually (adding the replica and using whatever synchronization setup mode you like). That's always an option... again, assuming you're correct and the tooling is not (which we don't know given the data available).

1
  • Hi Sean, I've added additional info in my question above., to answer your Q's
    – Tim
    Oct 10, 2023 at 8:00
0

Thank you @Sean, for your hints and pointers, and also confirming my numbers.

I ended up in the Join Only solution.

So if others get same issue, and cannot resolve within the AAG Autoseeding, this is the only option I found working:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a-secondary-database-for-an-availability-group-sql-server?view=sql-server-ver16

What would have been nice, was if the error telling me 'not enough space on secondary replica' could have explained why it gives the error, as obviously I got plenty of space all over.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.