July 10, 2015 at 2:29 pm
Hi everyone,
I hope everyone is doing well,
Im facing difficult issue in my Prod environment.
we observed the status of few databases in suspended on Principal and Mirror servers .
as the principal server continues functioning, but the mirroring state is suspended.
as backend process is still in Database mirroring state.As i understand due to this reason log file keeps on growing continuously on the Principal server and it eventually fills the disk. Please let me know if I'm wrong.
also, observed that both Principle and Mirror servers drive sizes are different..as per best practice to maintain same Drive sizes on both servers.
.
also observed that log reuse wait description for that few databases status was DATABASE_MIRRORING and the transaction log for this database is growing very large and it eventually fills the disk.
Please note we have already scheduled the transaction log backup on this database every 15 mins. now the transaction log backup size is 15GB (10GB,5GB,8GB….etc) for 15mins and the data is backed up in its compressed format.
observed that initial log file size is too big for few databases and consumed more drive space
still i do not understand why principal server log files growing continuously which is suspend status on both Principal and Mirror servers and log reuse descriptions showing as Database_mirroring.
what I need to check and how do i know the log files growing on database_morring.
please give me your comments and advise.
Thanks & Regards,
DBA
SQL server DBA
July 10, 2015 at 3:14 pm
Hi,
As long as the mirroring is not removed, the log will not be truncated on the principal side. You need to identify what exactly causing the mirroring to suspend. Check the space on DR(mirror) side and see if there is enough space for the transactions to flow through, you mentioned something about space being different on prd/dr so, I am assuming that is the main cause.
If you do not remove mirroring, and if prd log file grows and runs out of space, then it will affect PRD DB and eventually it will go down.
So, I would suggest try to resume the mirroring and see what happens, if it fails again, break the mirroring, then shrink the log of PRD.....then re-configure mirroring once you fix the space issue on DR side.
Regards,
SQLisAwe5oMe.
July 10, 2015 at 3:19 pm
SQLisAwE5OmE (7/10/2015)
Hi,As long as the mirroring is not removed, the log will not be truncated on the principal side. You need to identify what exactly causing the mirroring to suspend. Check the space on DR(mirror) side and see if there is enough space for the transactions to flow through, you mentioned something about space being different on prd/dr so, I am assuming that is the main cause.
If you do not remove mirroring, and if prd log file grows and runs out of space, then it will affect PRD DB and eventually it will go down.
So, I would suggest try to resume the mirroring and see what happens, if it fails again, break the mirroring, then shrink the log of PRD.....then re-configure mirroring once you fix the space issue on DR side.
Good advice. The mirror side should have the same storage space as the principal side.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 10, 2015 at 4:07 pm
Thank you for your quick response.
shrinking the log files on primary server. As i understand it will impact on performance and it will increase the log file again.
because i have done same thing as suggested,
i have shrink the log file and reconfigured the mirroring then after the log file extremely growing on Principal server.
Earlier initial size of log file is 500MB but now 200GB on both principal server and mirror server.
Now im facing issue wid space because principal server 1TB drive and Mirror server 500 Gb
there is no enough space to grow the log file on on mirror server
Now verified that initial log file size is too big for few databases and consumed more drive space.
What is the reasons log file is growing ? please suggest. (Log reuse status showing as database mirroring only)
what needs to be checked from DBA side?
Thanks in advance!!
Thanks & Regards,
DBA
SQL server DBA
July 10, 2015 at 4:16 pm
First I would suggest you take care of space issue on DR side immediately.
Second, how often you run your log backups?.....it seems like highly transactional database, you need to run log backups more frequently so, logs will be truncated frequently. That could be one of the reason why the log is growing so fast.
Regards,
SQLisAwe5oMe.
July 10, 2015 at 4:30 pm
Thank you very much your time and help.
I understood.
We are in pipeline increasing the space on mirror server.
if both server drive sizes are same ,then log file wil growing continuously what we needs to be check other things and any action plan ? even we have already scheduled the backups for every 20 mins.
please do not hesitate because i need to understand if if both server drive sizes are same also log file growing?
Thank you in advance for any help you can provide.
Thanks & Regards,
DBA
SQL server DBA
July 10, 2015 at 4:53 pm
Is the mirroring currently configured or suspended? Can you check the mirroring monitor and see how much log need to be transferred?
I can't think of any other reason...
Regards,
SQLisAwe5oMe.
July 10, 2015 at 5:10 pm
Database admin(DBA) (7/10/2015)
Thank you for your quick response.shrinking the log files on primary server. As i understand it will impact on performance and it will increase the log file again.
because i have done same thing as suggested,
i have shrink the log file and reconfigured the mirroring then after the log file extremely growing on Principal server.
Earlier initial size of log file is 500MB but now 200GB on both principal server and mirror server.
Now im facing issue wid space because principal server 1TB drive and Mirror server 500 Gb
there is no enough space to grow the log file on on mirror server
Now verified that initial log file size is too big for few databases and consumed more drive space.
What is the reasons log file is growing ? please suggest. (Log reuse status showing as database mirroring only)
what needs to be checked from DBA side?
Thanks in advance!!
Thanks & Regards,
DBA
The reason for the log file growing is probably due to a run-away transaction that never completed getting transferred to the mirror side.
Or as was initially said, because you have gotten to a point where the "mirror" server is differently sized in storage.
Shrinking the tlog is not the answer.
If the mirror cannot keep up with the transactions from the principal, then you are going to end up with a growing tlog.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 11, 2015 at 10:58 am
Database admin(DBA) (7/10/2015)
Hi everyone,I hope everyone is doing well,
Im facing difficult issue in my Prod environment.
we observed the status of few databases in suspended on Principal and Mirror servers .
as the principal server continues functioning, but the mirroring state is suspended.
as backend process is still in Database mirroring state.As i understand due to this reason log file keeps on growing continuously on the Principal server and it eventually fills the disk. Please let me know if I'm wrong.
also, observed that both Principle and Mirror servers drive sizes are different..as per best practice to maintain same Drive sizes on both servers.
.
also observed that log reuse wait description for that few databases status was DATABASE_MIRRORING and the transaction log for this database is growing very large and it eventually fills the disk.
Please note we have already scheduled the transaction log backup on this database every 15 mins. now the transaction log backup size is 15GB (10GB,5GB,8GB….etc) for 15mins and the data is backed up in its compressed format.
observed that initial log file size is too big for few databases and consumed more drive space
still i do not understand why principal server log files growing continuously which is suspend status on both Principal and Mirror servers and log reuse descriptions showing as Database_mirroring.
what I need to check and how do i know the log files growing on database_morring.
please give me your comments and advise.
Thanks & Regards,
DBA
Before you do anything you need to find out why the mirror session is suspended
Check the sql server logs and free disk space.
Either fix the mirror session or remove it before going any further
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 14, 2015 at 10:43 am
Hello Folks,
Thank you for the valuable suggestions.currently im working on this issue and will update you accordingly.
Thanks a lot.
Thanks & Regards,
DBA
SQL server DBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply