SQL Server Performance Forum – Threads Archive
Slow to apply tran logs
We’re running into problems with log shipping on 4 new servers that we just set up. While we are applying logs to the 4 new subscribers, the log applies fairly quickly, but then the database goes into a "LoadingRecoveringRead-Only" state and stays in that state for a riduculous amount of time, like 15 minutes to half an hour or more. The new servers are HP’s, and the old ones were Dells. I’m aware of the context switching problem on AMD dual-core processors running SQL 2000, but I don’t think that’s the problem. I can’t "see" what’s going on in that process during the time it’s in that LoadingRecoveringRead-Only state. The drive with the tran logs on it(seems to be) being written to, and the IO used by the spid coninues to rise during this time. It’s SQL Server 2000 (32-bit) running on 64-bit Windows 2003 Server. I was thinking maybe it might be related to a 64/32 bit executable mismatch (similar to the problem where you have to use the 32-bit executable of PerfMon.exe when running 32-bit sql on 64-bit Windows). Does anyone know what executables or libraries (ir any) are involved with the RESTORE LOG command? Better yet, has anyone seen this issue before? Does anyone know what the database being in that state means? CGilsoni would not jump to conclusions as to the cause, as this might send someone in the wrong direction run PSS DIAG to collect a full set of perfmon data
Looks like it was a hardware problem. They had installed different back planes (with much smaller IO cache) in these servers than on the other, similar servers. There was no available write cache on the drives that held the OS files, log files, program files. When we put in the new back planes that had bigger ID cards configured for 50% read/write, the problem with the db’s lingering in that status went away. It would still be nice to know if anyone out there knows the particulars of what happens to the status of a database during an apply log process that leaves the DB in STANDBY mode. Corey I’ve never used PSSDIAG… Any risk associated with that tool? Does it lock anything?
I have to take that back. Darn! After applying 2 sets of logs successfully, with good performance, the third time we did it, it started lingering in that "LoadingRecoveringRead-Only" state. I’m still looking for ideas about what steps are being walked through internally during a RESTORE LOG command. Corey
I just started having the same issue. In my environment, I log ship (SQL 2000 built-in Maint. Plan version) 4 databases from Primary to Secondary. 3 of the databases, backup, copy, and restore, quickly and efficiently. However, the 4th db (also, the largest and most frequently used) started acting up this morning. The largest log backups are b/w 15 and 40MB. I noticed this morning, that it takes 9 – 10 minutes to do any size log restore to my Secondary. It doesn’t matter if it’s a 512KB .trn or a 40MB .trn – it still takes the same time for the RESTORE LOG command to complete. All the while the CPU and I/O counters from sp_who2 increase. Through perfmon, I noticed a wierd thing though… The drive the Secondary DB resides on (aka the drive I’m restoring to) has no "write" activity and in fact the Disk Read Bytes/second counter is through the roof: 33MB/sec READ! Nothing is being read from this drive – only .trn restores for Log Shipping. I notice when a new log begins the restore, the backup device counter in perfmon gets some activity but just for a couple seconds…then it has a counter of 0.000 for the remainding 9+ minutes; and again the 2ndary db drive has very high read activity. This just started happend early this morning. Prior, it worked flawlessly with minimal latency (10 minutes) for 2 weeks. Now the latency b/w backup and recovery is 120 minutes. The other 3 DBs have minimal latency (10 minute). Any ideas?
]]>