June 16, 2009 at 2:25 am
Hi Guys,
I seem to have a strange problem.
I am using SQL Server 2005 Standard Edition and recently installed sp3 with 8GB RAM and good Hard drive space available. I have setup a mirror combination with transaction safety (without witness) and synchronous).
The problem is that everytime a query generates heavy transaction logs, which might range from 250 MB to 2 GB and sometimes more, the mirroring just about comes to a halt.
I just discovererd that the mirror is taking too much time in redoing those logs that are being sent to it. Would that cause mirroring to slow down?
Is this some kind of a know issue, or can I do something about it.
Please help!!
Regards
Akhil
June 24, 2009 at 5:08 am
Yes, abolutely. With a Synchronous Mirror, the logs have to be applied to the Mirror before they can be committed on the Principal.
You could try using "Database Mirroring Monitor" to identify where the delays are occurring. If that doesn't help, use Performance Monitor (or the WMI namespace directly) where you have more parameters available.
If your network, hard disks, etc (wherever the problem lies) cannot handle the demand, then I suggest you have the options of either upgrading your hardware, or changing the Mirror from Synchronous (high safety) to Asynchronous.
Andy
June 24, 2009 at 7:48 am
akhil,
As you mentioned 8GB with SQL StD Edition. What is your base OS like (Windows Standard or enterprise 2003)
with a windows Standard 2003, SQL is still able to use the MEmory, whatever is available to the OS.
With Enterprise this limit is aboished and you can use more than 4GB of ram for SQL Standard Edition:
So this can play a Role in how mirroring operates also, the disk Speed and how it is spread out.. with RAID.
So everything in terms of Hardware matter.. Just the space of the disk is not enough to conclude....
let us know all the details about SQL/OS and Hardware...
Maninder
www.dbanation.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply