November 12, 2008 at 1:17 pm
Animal Magic (11/12/2008)
I have a feeling this is what my problem was. the amended table has 6 indexes including the PK clustered index, so the alter table would have rebuilt those indexes. With it being in full recovery model the log reader got too far behind, bulk logged as i normally have it probably wouldnt have caused the same issues.
Shouldn't have. Depends what the alter table was. Normally a column can't be altered if there's an index on it so the person would have had to drop the index, alter the table and then recreate the index.
What exactly was the table alteration in question?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2008 at 1:19 pm
Animal Magic (11/12/2008)
What is happening (and i should have clicked earlier) was one of the statements this app runs is an update with no where clause, every 3 seconds. so half a million updates every 3 seconds was why the log reader couldnt keep up (not surprising really!!)
Ow. That's insane.
May I suggest an INSTEAD OF UPDATE trigger on that table until the devs get the mess sorted out?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2008 at 1:20 pm
TRACEY (11/12/2008)
Oh someone changed it to FULL Recovery mode - oh that may do it if your not backing up logs every x minutes.
That would make the log grow, it wouldn't make the log reader choke though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply