June 22, 2010 at 6:15 am
Being a programmer with no database knowledge/experience, I am requesting answers to following questions as I am kind of stuck now:
1. I have a doubt regarding sql server 2005 (std edition, sync mode) mirroring. suppose if I have a database whose transaction log of 1 GB (due to some other reasons i have not taken tlog backup for months and we are not having periodical tlog backup scheduled, which we need to do soon anyway) and if I run some SQL updates of 200mb, will now JUST 200mb is going to be transferred to mirror or total of 1200 mb(1.2 GB) is going to be transferred to mirror? Ideally I suppose, only delta is going to transfer to mirror from principal. Or due to the fact we are not reducing tlog files is whole thing going to transfer to mirror hence performance of my database is going down day by day??
2. And how large/huge transaction logs is going to effect db mirroring performance. my dba told, if we have smaller transaction logs then db mirroring will have good performance. How do we justify this?
3. what's the best maintenance/backup plan for databases to give good performance while they are participating in mirroring.
June 22, 2010 at 7:36 am
sivaji (6/22/2010)
Being a programmer with no database knowledge/experience, I am requesting answers to following questions as I am kind of stuck now:1. I have a doubt regarding sql server 2005 (std edition, sync mode) mirroring. suppose if I have a database whose transaction log of 1 GB (due to some other reasons i have not taken tlog backup for months and we are not having periodical tlog backup scheduled, which we need to do soon anyway) and if I run some SQL updates of 200mb, will now JUST 200mb is going to be transferred to mirror or total of 1200 mb(1.2 GB) is going to be transferred to mirror? Ideally I suppose, only delta is going to transfer to mirror from principal. Or due to the fact we are not reducing tlog files is whole thing going to transfer to mirror hence performance of my database is going down day by day??
If your mirroring is setup and it is Synced (which you can verify with the Mirroring Monitor) all transactions are being mirrored in realtime. The fact that your tlog is so big is because of what you already mentioned (no backups) and it has no incidence on your mirror (as long as it is synced).
2. And how large/huge transaction logs is going to effect db mirroring performance. my dba told, if we have smaller transaction logs then db mirroring will have good performance. How do we justify this?
Not sure if the size matters, to an extent, it is just a matter of resource (memory/disk) and that has inherent associated penalties.
3. what's the best maintenance/backup plan for databases to give good performance while they are participating in mirroring.
It depends on your transaction load.... We have a 1.2 TB mirrored DB, and have a daily Full backup and t-log backups every 15 minutes just for the fact of the amount of transactions in the system.
June 22, 2010 at 7:41 am
hi richard
thank you very much for reply.
so from your notes, size of tlog does nt affect your db mirror slow performance. So how can i debug to know which is causing my mirroring to give slow performance (as applications running are slow when mirroring is enabled and quite fast when we turn off mirroring...)
June 22, 2010 at 8:07 am
As mentioned, having big tlog files because of lack of backups has its penalty on resources. It could be that it simply is trying to catch up to synchronize them?
June 22, 2010 at 9:55 pm
Mirroring doesn't actually use the transaction log (from what I understand).
The transactions are applied to the principal and the mirror database. This is different from log shipping which actually applies the transaction log to the secondary server.
Having said that, if big transactions are creating a big transaction log on your primary server, you will have a transaction log the same size in your secondary server when the same transactions are applied.
as the previous poster mentioned, the database mirroring monitor will help you determine the latency and how many transactions haven't yet been applied to the mirror.
June 22, 2010 at 10:05 pm
Check if you are synchronous or asynchronous with mirroring. If the connection is slow, synch mirroring can slow down the primary a little.
The transactions that are sent to your local log, are sent to the remote server and applied in the same way as they are on the primary. Typically it is not the mirroring which is slowing your server, but possibly the server is just slow because of a lack of resources.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 23, 2010 at 5:38 am
hi Steve
thank you for your reply. I am sure, we are using synchronous mode only. But one concern is mirroring "used to" work properly but on suddenly during business day system started moaning and by turning off the mirroring system seems to be working faster. There were no updates made to database and so there is no way i can debug here. To confuse us more, all unsent log, transaction commit overhead are zero and all other stats seems to be good that time (when we removed mirroring)
-> Main thing is today, to experiment more on mirroring I have setup 2 dbs on dev and while playing I made a huge update on 1gb (by importing a table to db from access) on principal without pausing mirroring..since then though 30mb of unsent log presents, oldest unsent transaction log for 00:40:59 with mirror commit overhead as 0 milli seconds, and mirroring state "suspended" (but I did not suspend it). However be the number of times, I am resuming mirroring, after few seconds again mirroring state is changing to "Suspended". Seems like full chaos or a Microsoft bug. I tried full backup, transaction backup for luck still its in suspended state. How can I get that to "Synchronizing" state again???
June 23, 2010 at 9:04 am
sivaji (6/23/2010)
However be the number of times, I am resuming mirroring, after few seconds again mirroring state is changing to "Suspended". Seems like full chaos or a Microsoft bug. I tried full backup, transaction backup for luck still its in suspended state. How can I get that to "Synchronizing" state again???
Have you checked the mirror to see if it has run out of space in the transaction log or data file? Just because you did a backup and transaction log backup on the principal doesn't mean that it cleared out the miror.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 23, 2010 at 9:16 am
you are right. I am having less space on mirror , so after clearing it has sorted out...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply