I got russtled when they turned the show into Farscape without the cool puppets/Harvey. Thankfully we got Shepard around that time. The whole Ori arc was balls.
Re-occuring villains was its deathknell for me. Humanizing these utterly alien gods, totally removed all threat from them. As well as the complete and total focus on making SG:1 characters demigods. The entire galaxy revolves around 4 humans.. particularly by the end. the series would have been MUCH better if the people were more disposable, and the series focused more on the organization as a whole. by the end of the series they all have fucking magic powers.
Still upset the Asgard blew themselves up. Thor was win. I loved that one "clip episode" where they are debating whether to end the SG program/the SG team, then they get Thor to beam in and basically say "Keep SG command or we won't be pleased" and all the politicians piss themselves and vote to keep the SG program open ;p
Window of Opportunity is one of those magical tv episodes I can put on that never fails to cheer me up. MacGuyver and Teal'c learning how to juggle just hits the perfect happy spot.