wireless (wi*********@yahoo.com) writes:
I've had my SQL server database running for two years now without a
problem.
However, just today one of the main tables started returning an error.
The table is contained within a database called engineering. I back
it up once a week and the file size is up to about 40 MB.
The error returned when trying to return data from one table
(DbLucent) is:
"[Microsoft][ODBC SQL Server Driver]Timeout expired"
I can open/query any of the other tables in the database. I can open
design table for this table. But it won't return any query.
I'm debating whether to restore the database from the last backup.
Any suggestions would be appreciated. Being located reomotely, I
rather not fly back to the city where the server is and work on it
there either.
The table is not returning any timeout. The client is. And the timeout
is settable for most clients. From your talking of opening the table,
I assume that you are using Enterprise Manager to look at the table. EM
does not seem to always care about the timeout you can set.
In any case, you can try a SELECT * from the table in Query Analyzer. Since
QA by default does not have a timeout, you will not get an error. Then
again, if John's suspicion is right that there is blocking, then you can
wait forever. Nevertheless, do run that query, and pay attention to the
spid which you find in the status bar of QA, in parentheses after the
server name. Then open a second query window, and run sp_who. Find your
spid, and check the Blk column. If that column has a non-zero value,
the value in spid is the blocking process. Use KILL to terminate that
process, and you will get data back in the first query window.
If there is no blocking, I would suspect that the query is in fact a
view, and complex enough to not be computed within the 30 seconds that
is the default timeout. But for small 40 MB database it has to be
quite a wild view to get there.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp