To me it was that match with Brock Lesnar at Madison Square Garden. Lesnar absolutely dominated him and won decisively. After that i dont think i have been able to take The Big Show seriously. When was it for you?
For me, it was after the mid 2000's, like around 2010. He had moments where he could have made a comeback but the writers would fail to capitalize and turn him heel for no reason. A good example is during the authority story line, he could have been a great back up as a vet and have good feuds with the authority's, HHH included, but they turned him heel and made him cry. Yes the authority gave him a good reason to join them, do what we tell you or your fired, but there were two problems: 1) they already did that with Cody, Dustin, and Dusty Rhodes before and it starting to get boring, in my opinion. 2) Big show could have told them "I can go to any other wrestling company" or "I can go to Japan" or "I can become a strongman....I'm a giant"
Just due to this size and how long he's been around he'll draw some attention.
But, I can't really put my finger on any one date where I stopped looking at him as a threat to any title or expected him to win any feud. But it was sometime after the losses piled up enough that beating him didn't mean anything anymore because everyone had done it and he had switched from heel to face so many times it was clear they didn't know what to do with him. The crying in the ring thing didn't help him either.
When he jobbed clean as whistle to Steve Austin before Wrestlemania 15. He hadn't even been there a month. Still one of the biggest WTF? moves they ever did. Austin didn't need it at all. It helped no one
He'll always be an attraction. In terms of credibility it's always been up and down. Big Show has never had great consistent booking, some years he was strong like 04-05 or 2011, but mostly he's just there to make the top guys like Cena, Reigns, Triple H, Orton ect ect look good.
I think If Show was coming up now he would be booked as strong as Lesnar. He would probably go 2+ years unbeaten in NXT. Then do the same on the main roster.