problem with timeouts

[wireless]

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.

-David

[John Bell]

Re: problem with timeouts

Hi

If you have checked that it is not being locked then it sounds like you may
need some form of maintainance plan for defragging the indexes?


John

"wireless" <wireless200@ya hoo.com> wrote in message
news:90446ee7.0 408290934.1faf1 e1c@posting.goo gle.com...[color=blue]
> 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.
>
> -David[/color]

[Erland Sommarskog]

Re: problem with timeouts

wireless (wireless200@ya hoo.com) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[wireless]

Re: problem with timeouts

"John Bell" <jbellnewsposts @hotmail.com> wrote in message news:<41321b73$ 0$28238$afc38c8 7@news.easynet. co.uk>...
[color=blue]
> If you have checked that it is not being locked then it sounds like you may
> need some form of maintainance plan for defragging the indexes?[/color]


Okay I figured out what it was, sort of.

The weekly backup and optimization runs every weekend.

I noticed the optimization job was still running this afternoon after
it had begun at 1 am. Normally the opto takes just a few minutes.

I tried to stop the job but it wouldn't so I stopped and restarted the
database and that ended the job.

Next, I expanded the size of the database and reran the jobs.
Everything seemed to work okay after that.

After getting everything running again, the database backup was about
25% larger than before.

Last week I gave a guy rights to create views on the database. He
created about 4 or 5.

All I can guess it that those views took up space and when the opto
started their wasn't enough space to run everything and it hung.

regards,
-David

[Erland Sommarskog]

Re: problem with timeouts

wireless (wireless200@ya hoo.com) writes:[color=blue]
> Last week I gave a guy rights to create views on the database. He
> created about 4 or 5.
>
> All I can guess it that those views took up space and when the opto
> started their wasn't enough space to run everything and it hung.[/color]

Views don't take up space (save for the source text), unless they are
indexed.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[wireless]

Re: problem with timeouts

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns9554F3 2E4271AYazorman @127.0.0.1>...[color=blue]
> 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.[/color]

Erland, as always, thanks for the tutorial.

I found out what was causing the problem or at least the process that
was.

Every Sunday morning three jobs run - backup, opto, and error
checking.

For some reason the opto job was not able to finish - or stop. This
was preventing any querying of the table. Other tables could be
queried.

I tried stopping the job but was unsucessful so I stopped and
restarted the database. This stopped the job.

I increased the percentage of free space that is left after opto and
reran successfully.

The table was accessible after that.

All I've come up with is a few days ago I gave a dba at our company
rights to create a view (but nothing else). He created 4 views. He
said he tried to create a 5th the day before this problem occurred but
said it would never complete and I think he said he killed it. He's
an oracle dba so maybe it didn't work the way he thought.

I'm not sure if doing this used up all the room before the database
automatically increased the size and then the opto didn't have enough
room to run and hung. That's all I've been able to come up with.

Now that I think about it, maybe his create view process was still
running and therefore opto was waiting on it to complete which it
never did.

regards,
-David

[Erland Sommarskog]

Re: problem with timeouts

wireless (wireless200@ya hoo.com) writes:[color=blue]
> All I've come up with is a few days ago I gave a dba at our company
> rights to create a view (but nothing else). He created 4 views. He
> said he tried to create a 5th the day before this problem occurred but
> said it would never complete and I think he said he killed it. He's
> an oracle dba so maybe it didn't work the way he thought.[/color]

Creating a view takes no time at all. And neither does views take up
space. Unless, as I think I pointed out in a previous post, they are
indexed.

It seems there is a whole lot of information of what happened, to tell
anything for sure.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp