October 9, 2019 at 6:23 am
Hello,
I am trying to shrink my production log file as its occupying lot of space in L drive. Its in Alwayson setup. two days back i already removed the db from alwayon and made it simple recovery mode and shrinked the log file.After this i added back to AG.
Now again the logs were growing. I checked any open transactions. No open transactions. I checked the log_reuse_wait_desc. Its showing AVAILABILITY_REPLICA.
i tried below commands also.
USE[abc]
GO
CHECKPOINT
GO
CHECKPOINT
DBCC SHRINKFILE('abc_log', 1024, TRUNCATEONLY)
go
Took t-log backups also.tried shrinking log files. Nothing helping me to reduce the size. Infact after running the above commands the log space increased surprisingly. .
I can not remove this db from alwayson and make simple ,shrink. Please help me to reduce the log space..:(
Thanks,
October 9, 2019 at 7:23 am
On the top you have mentioned that you have removed the AlwaysOn but in the bottom you mentioned you cannot remove this DB from AlwaysOn. Would you please clarify whether AlwaysOn is enabled or not?
Alternatively, try out below URL's and see if it helps.
October 9, 2019 at 7:25 am
Run this:
select name, log_reuse_wait_desc from sys.databases, and tell us what the output is for that database.
October 9, 2019 at 7:47 am
Sorry. The db is in AG setup.
October 9, 2019 at 7:49 am
log_reuse_wait_desc= Availability_replica
October 9, 2019 at 8:38 am
Sorry, I was tired when I posted earlier; I see you've already said that in your first post. Is your AG in synchronous or asynchronous mode? When you check the dashboard does everything look healthy?
What about the error log on the secondary replica; is there any indication there of e.g. thread exhaustion that might be causing the log replay to slow down or stop? Any other errors on the secondary? Is the disk space on that server ok?
October 9, 2019 at 12:11 pm
Its half resolved. The logs growing coz the DR node was down. we brought dr node up. and resumed the database sync in alwayson. but its damn slow.. any suggestion?
i created test db with one table. tried adding db to current alwayson. its taking very long time to joining the db to avaialability group in DR node. anything i can check from server level and SQL level?
October 9, 2019 at 1:07 pm
This could be also a side-effect of what's called "Lazy Logging" in Availability Groups - there are lots of articles thrown up by a search - https://www.google.com/search?q=availability+group+lazy+logging
I would suggest reading these two as a start:
Thomas Rushton
blog: https://thelonedba.wordpress.com
October 10, 2019 at 8:57 pm
Perhaps,
You should see your AlwaysOn Dashboard
good luck
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply