This is what happened to me.

A database in MS SQL Server 2005 is being used for a forum software. It's been running flawlessly for years.

In the DB tables, all entries are correct and all messages are there. I can see them.

BUT, for example, when searching for a specific post with a simple query like

SELECT * from pgd_messages where threadId = 10990 (=return all posts belonging to thread 10990)

I only get one record as a result, even though I know and I found other messages being in the threadId 10990 . In other threads I may get more than one results, but not all, and in others I will get all results.
Also, those same messages/entries that are not returned by the query, are not shown in the forum, which is in fact suffering from some "missing message" problem.

What is wrong? I am not an expert in SQL Server, but the problem must not be in the forum software, and more likely be somwhere "between" the table containing all data and some index which is corrupted, and which is used by the SELECT function.

Has this happened to anyone? Any help is greatly appreciated!

When you say "found other messages being in the threadId 10990" how did you "find" them, if it wasn't though a query very similar to the one you say doesn't work ?

Oops, Sorry for not being clear.
I found them by opening the message table itself. It's not a huge forum and with 10 minutes research scrolling the table on the screen I could find them. The records are there, with correct author, message number, thread number, etc.

You need to be more precise. How did you "open the message table"? Did you do a "SELECT * from pgd_messages"? Did you open the raw database file with a binary editor? Is there some sort of direct access utility in MSSQL?

I imagine he did "Open table -> return all rows" in SQL Server Enterprise Manager, which basically does a "select * from..." and displays it in grid.

It's even a GUI option:

How to: Rebuild an Index (SQL Server Management Studio)
http://msdn2.microsoft.com/en-us/library/ms187874.aspx

That was it, Roger. The index was corrupted. I dropped it and recreated it, and now everything seems to be working.
Thanks you all for your help.

Quote:
You need to be more precise. How did you "open the message table"?

It's a MSSQLServer feature. You can "show" a table, as Roger described, by simply using a specific GUI option.
Not being a SQL Server expert at all, I too believed that "showing" a table simply meant doing a "select..." .
It seemss that's not the case, instead. While a "select" uses the index (which was corrupted), "showing" a table reads the table directly.

And, thanks a_attrill for pointing to the GUI option to rebuild the index.

I very, very nearly suggested that it could be a corrupted index as well. The reason I decided not to mention it is that although I know a corrupted index is theoretically possible I had never actually seen it happen in 10 years of SQL Server usage.

Many of the larger databases I have worked with have regular jobs to rebuild indexes, I guess that is why I have never seen a corruption.

Thanks Roger for the explanation.

I just wanted to confirm it was really it. After days of full usage, and several searches of very old and new messages, all seems to be there.

And, for the record, what caused the index to break, I believe, is a bad power failure coupled bwith misconfigured UPS.