Can not Truncate the database because “Secondary has no log was added”
-
I got an error when trying to truncate the database: "Database can not shrink until all secondaries have moved past the point where the log was added". When I check the status of the secondary on the primary machine (Always On High Availability Dashboard), it has heath: good and Synchronization state: Synchronizing. But the status of database on secondary is NULL not ONLINE. When I check on the Always On High Availability of the Secondary machine, it's state is No data available on secondary replicas. What should I do and what is going on with the database..?
(I want to truncate because the free space is no more enough.)
Thank for reading. Pls help.
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
So have you run out of space on the log drives? The first thing would be to add more storage to each log drive and let the databases continue sync'ing.
Otherwise, remove the DB's from the AOAG, set to simple, shrink the log, set to full, do a full and log backup, add the DB's back into the AOAG.
And then make sure you are doing regular log backups as well as full backups.
As for the dashboard that is correct, only certain information can be pulled from the primary replica, when viewing the dashboard on secondary replicas, certain pieces of information are not available.
-
Yes, I use E:\ for all databases file has used 421.5Gb and free 78.5Gb and the total is 500Gb. I have do some query like below:
database1 -
So your waiting for log backup which means you have probably never even done one in that database's life time given the size of the data and the size of the database.
So your best bet here, remove ApexCentral from the AOAG.
Set ApexCentral to simple recovery model.
Shrink the log to a reasonable size, maybe 1GB.
Set ApexCentral to the full recovery model
Perform a Full and Log backup of ApexCentral
Add ApexCentral back into the AOAG.
Setup a job which will backup your logs every 15minutes / 1 hour depending on what your RTO and RPO is for ApexOne and ApexCentral.
Oh and if you did add a 2nd log file to the DB remove it while the DB is in simple recovery, no need for the DB to have more than 1 log file.
-
I will try your solution.
I have a more thing, my SQL Server is in HA state, so I could not chnage to Simple mode for the database (in thoery).
Am I have to remove the database on the secondary machine when remove ApexCentral from the AOAG on the primary? (I am a newbie to SQL so It may have a little that is not so clear to understand..)
-
While the DB is part of the AOAG you cannot change to simple mode.
But as you will be removing the database from the AOAG, you can then set it to simple, fix the log, then set back to full.
On the secondary, no you do not need to remove the database.
As part of adding the DB back into the AOAG, you will need to restore the "fixed" copy onto the secondary so you will overwrite that copy of the database as part of adding back the DB into the HA state.
- Ant-Green wrote:
As part of adding the DB back into the AOAG, you will need to restore the "fixed" copy onto the secondary so you will overwrite that copy of the database as part of adding back the DB into the HA state.
Only if automatic seeding has not been enabled. If automatic seeding is enabled then you do have to remove the database from the secondary before adding the database to the AG.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
database2
additional information Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply