His "recognition" (or fear) that his wife has given her soul to the devil certainly represents a terrifying moment for this young husband. Note the language Hawthorne uses: "The husband cast one look at his pale wife, and Faith at him. . . . "Faith! Faith! cried the husband. "Look up to heaven, and resist the Wicked One!" Whether Faith obeyed he knew not." The scene suggests the worst nightmare possible: the monster is just about to attack someone you love, and you awake as he reaches out with his sharp claws and ugly mouth inches away from him or her! And of course Faith is both Brown's wife, whom he loves, and the virtue that he wants to keep--his dream puts him in the position of being able to protect neither. He is completely helpless in relation to the power of evil and doubt.
I'm not sure if you're asking what is the scariest part of the story for Goodman Brown or what's the scariest part for the reader. If you're asking the latter question, that is certainly a personal question only you could answer. It would depend on what scares you. Remember, Goodman Brown isn't sure that his night in the forest really happened or if he dreamed it. He allows that night, whether real or a nightmare, to change his life forever.
If you're asking what the scariest part was for Goodman Brown, I would say it was when he saw his wife going to the devil's meeting in the forest. At the beginning, Goodman believes his wife is an "angel" and is one of the few who will be chosen to go to heaven. When he sees Faith, his wife, in the forest, it changes his life forever. Goodman Brown loses his Puritan faith and never gets it back. He lives the remainder of his life alienated from his wife and the rest of the people. In this respect, he becomes the symbolism of Hawthorne's belief in the isolation of the human spirit. Goodman Brown resists evil and retains his faith, but he loses all hope, living isolated from everyone.