April 1, 2015 at 4:29 am
hi,
my database transactionlog is full I am getting below error while shrinking and also my secondary database went to not synchronising mode/suspect mode .
Msg 9002, Level 17, State 9, Line 1
The transaction log for database 'New' is full due to 'AVAILABILITY_REPLICA'.
can any one suggest how to resolve this issue
April 1, 2015 at 4:32 am
Either fix the replica so that it is syncing or remove the availability groups, fix the root problems and reconfigure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2015 at 4:56 am
Hi,
I have tried to shirnk the database also below error I am getting
The transaction log for database 'New' is full due to 'AVAILABILITY_REPLICA'. (Microsoft SQL Server, Error: 9002)
April 1, 2015 at 5:22 am
No, you won't be able to shrink the database because the log is full.
GilaMonster (4/1/2015)
Either fix the replica so that it is syncing or remove the availability groups, fix the root problems and reconfigure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2015 at 5:46 am
Arjun SreeVastsva (4/1/2015)
hi,my database transactionlog is full I am getting below error while shrinking and also my secondary database went to not synchronising mode/suspect mode .
Msg 9002, Level 17, State 9, Line 1
The transaction log for database 'New' is full due to 'AVAILABILITY_REPLICA'.
can any one suggest how to resolve this issue
What's the status of any secondaries (synch or asynch session, redo queue length, etc)?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 1, 2015 at 5:50 am
Arjun SreeVastsva (4/1/2015)
Hi,I have tried to shirnk the database also below error I am getting
The transaction log for database 'New' is full due to 'AVAILABILITY_REPLICA'. (Microsoft SQL Server, Error: 9002)
And because the transaction log is what is used to ensure synchronizing with the secondary. You need to get that back online, or removed, before you're going to be able to do anything else.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2015 at 6:29 am
Remove the primary database from Availability Group. Shrink the DB and then add it back.
April 1, 2015 at 8:17 am
Ratheesh.K.Nair (4/1/2015)
Remove the primary database from Availability Group. Shrink the DB and then add it back.
This is the last thing you should do and you'd be well advised to avoid this
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 1, 2015 at 9:14 am
Perry Whittle (4/1/2015)
Ratheesh.K.Nair (4/1/2015)
Remove the primary database from Availability Group. Shrink the DB and then add it back.This is the last thing you should do and you'd be well advised to avoid this
Not unless you've fixed the root cause, that's for certain.
Please, listen to Perry on this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2015 at 11:37 am
April 2, 2015 at 2:47 am
As experts suggested, yes please listen to Perry.
I was just giving you the way how you can do that.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply