SELECT * FROM no longer works?

Question

SELECT * FROM no longer works?

Swafnil

SSC Enthusiast

Points: 107
More actions
July 11, 2006 at 8:04 am

#366639

Hi folks,
I recently recognized that some of our SQL Servers (Version 8.0 with SPs varying from 2 to 4) all of a sudden fail to display database tables in the Enterprise manager, and if i try to issue a "select * from table" through osql or Query Analyser, I receive a timeout after approx. 2 minutes, although the table only contains about 20 columns and 30 rows! A query with specified column names (i.e. "SELECT ID, Name FROM Table") finishs within a few seconds with correct results.
There are no obvious errors in the SQL Server log nor are there any entries in the eventlog that would help me fix the problem.
I can reproduce this misbehaviour through connecting from several other machines.
What could I do?
Thank you and best regards from Germany,
Sascha

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Gladden Old Hand Points: 303 More actions · Answer 1

Wow, that's a tough one. My guess would be security/privileges. Has someone recently changed group policy for your systems?

Swafnil SSC Enthusiast Points: 107 More actions · Answer 2

Yeah it definitely is!

Today we found out that the phenomenon only occurs on Windows XP systems (it's reproducable), whereas all Win 2k Workstation have no problems? And several systems show this behaviour, it's not occurring on a single machine.

I guess it has to do with some patches that could have been installed through unattended installation (we use a Windows SUS server in our company so updates are automatically downloaded and delivered to the clients), but I'm not sure about it.

If I'll find out more, I'll let you know! Oh, thanks anyway for your hint!

Ian Yates SSCoach Points: 19738 More actions · Answer 3

Try looking for index/table corruption. When you use a where clause SQL Server might be using an index which lets the results come back to you but a full table scan mightn't work (and explain the timeout).

Run dbcc checkdb from within Query Analyser or other querying tool (eg osql).

Swafnil SSC Enthusiast Points: 107 More actions · Answer 4

After a lot of research we found out that a change in our VPN gateway caused the problem - the MTU size had been changed and our Windows XP systems discarded the returned packages because of a default MTU size of 1536 (approx.), whereas the Windows 2000 systems where configured to expect a MTU size of 1300.

Thanks for you help!

Best regards!

Ian Yates SSCoach Points: 19738 More actions · Answer 5

Wow - congrats for finding that bug. Wouldn't have thought to look there for a while More out of curiousity than anything, what led you on the path to look at the MTU of the machines?

Swafnil SSC Enthusiast Points: 107 More actions · Answer 6

Our network admin found out that a patch had been installed on the Win 2k systems that changed the MTU size from 1536 to about 1300 - our Win XP systems were not patched, because we started lately switching from 2K to XP.

He had the idea of looking at the MTU size, because he had his private XP notebook at work and did not have any problems using Enterprise Manager; he knew that he optimized the network settings a while ago and one of the altered settings was the MTU size!

So it was mainly an accident that he found it