What can cause SQL 2000 to stop responding for a while

5 Dec 2008 3:05 AM

JN

I have a SQL Server 2000 that has a couple of databases on it. One is
couple of gig with about 30 tables and another is about 60 gig with about a
dozen tables but almost all of it is in 3 tables.

At totally random times the db with about 60 GB of data will just stop
accepting inserted data. I can still query data from varioius apps written
in Visual Studio, Access 2003, and Labview, but none of these programs can
add records for a while. Sometimes this will happen 4 times in a week and
sometimes we don't see it happen for months. When it does happen it could
last just a few minute or as much as an hour or so.

Also, when this happens every table in the db is affected. Even small table
with just a couple of records. The strange part is none of the other DBs on
that server experience any problems nor do I notice a slow down.

Nothing runs randomly in my maintenance plans. I backup all dbs to tape at
1:00 AM (finishes by 1:45). At 2:00 I do an Integrity Check on all user
databases and have it set to fix any minor problems. Finally at 5:00 AM I
schedule and optimization to reorganize data and index pages and change the
free space to 10%. Each of these takes from 10 minutes to 45 minutes to
complete so none of them should be running during the day when this error
happens.

Any ideas on what could be causing this.

5 Dec 2008 5:56 AM

Geoff N. Hiten

In importance order:

"Fix minor problems" = "Hide little I/O errors until my dataase crashes".
Remove that step immediately. (Note, Microsoft removed that option starting
in SQL 2005 for very good reason)
"Shrink database" = refragment my database. -- Optimize for I/O
performance, not for space.
Autogrow = hold everything in this database until I am done expanding the
data file. Very bad. Autogrow is an emergency safety valve at best.

Pre-expand your data and log files and leave them. Disk space is cheap.

As an aside, I haven't seen a Labview app in over a decade. I remember them
from NT 3.51 days. "Draw your own solution". (I took the classs and still
have the shirt). Nice to see they are still in business.

Access front ends are a whole 'nother problem, but I would bet on the
maintenance issues as the cause.

--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP

Show quoteHide quote

"JN" <m*@here.com> wrote in message
news:OQJ9HZoVJHA.4376@TK2MSFTNGP04.phx.gbl...
>I have a SQL Server 2000 that has a couple of databases on it. One is
>couple of gig with about 30 tables and another is about 60 gig with about a
>dozen tables but almost all of it is in 3 tables.
>
> At totally random times the db with about 60 GB of data will just stop
> accepting inserted data. I can still query data from varioius apps
> written in Visual Studio, Access 2003, and Labview, but none of these
> programs can add records for a while. Sometimes this will happen 4 times
> in a week and sometimes we don't see it happen for months. When it does
> happen it could last just a few minute or as much as an hour or so.
>
> Also, when this happens every table in the db is affected. Even small
> table with just a couple of records. The strange part is none of the
> other DBs on that server experience any problems nor do I notice a slow
> down.
>
> Nothing runs randomly in my maintenance plans. I backup all dbs to tape
> at 1:00 AM (finishes by 1:45). At 2:00 I do an Integrity Check on all
> user databases and have it set to fix any minor problems. Finally at 5:00
> AM I schedule and optimization to reorganize data and index pages and
> change the free space to 10%. Each of these takes from 10 minutes to 45
> minutes to complete so none of them should be running during the day when
> this error happens.
>
> Any ideas on what could be causing this.
>
>

5 Dec 2008 6:02 AM

George Wen

Did you look into the server to see whethere deadlocks occur?

I would suggest you to do some performance auditing using performance
monitor & sql profiler as well..

JN wrote:
Show quoteHide quote

> I have a SQL Server 2000 that has a couple of databases on it. One is
> couple of gig with about 30 tables and another is about 60 gig with about a
> dozen tables but almost all of it is in 3 tables.
>
> At totally random times the db with about 60 GB of data will just stop
> accepting inserted data. I can still query data from varioius apps written
> in Visual Studio, Access 2003, and Labview, but none of these programs can
> add records for a while. Sometimes this will happen 4 times in a week and
> sometimes we don't see it happen for months. When it does happen it could
> last just a few minute or as much as an hour or so.
>
> Also, when this happens every table in the db is affected. Even small table
> with just a couple of records. The strange part is none of the other DBs on
> that server experience any problems nor do I notice a slow down.
>
> Nothing runs randomly in my maintenance plans. I backup all dbs to tape at
> 1:00 AM (finishes by 1:45). At 2:00 I do an Integrity Check on all user
> databases and have it set to fix any minor problems. Finally at 5:00 AM I
> schedule and optimization to reorganize data and index pages and change the
> free space to 10%. Each of these takes from 10 minutes to 45 minutes to
> complete so none of them should be running during the day when this error
> happens.
>
> Any ideas on what could be causing this.
>
>
>

6 Dec 2008 2:05 AM

JN

The only locks I see at the time are under the "Locks / Object" group and
none of them are on the database that is stuck. They are sometimes on or
two on another db, but there is always on on the tempdb. Under the Process
info there is never a process showing as "Blocking" or "Blocked".

Show quoteHide quote

"George Wen" <wind***@hotmail.com> wrote in message
news:Oa8bG8pVJHA.3740@TK2MSFTNGP06.phx.gbl...
> Did you look into the server to see whethere deadlocks occur?
>
> I would suggest you to do some performance auditing using performance
> monitor & sql profiler as well..
>
>
> JN wrote:
>> I have a SQL Server 2000 that has a couple of databases on it. One is
>> couple of gig with about 30 tables and another is about 60 gig with about
>> a dozen tables but almost all of it is in 3 tables.
>>
>> At totally random times the db with about 60 GB of data will just stop
>> accepting inserted data. I can still query data from varioius apps
>> written in Visual Studio, Access 2003, and Labview, but none of these
>> programs can add records for a while. Sometimes this will happen 4 times
>> in a week and sometimes we don't see it happen for months. When it does
>> happen it could last just a few minute or as much as an hour or so.
>>
>> Also, when this happens every table in the db is affected. Even small
>> table with just a couple of records. The strange part is none of the
>> other DBs on that server experience any problems nor do I notice a slow
>> down.
>>
>> Nothing runs randomly in my maintenance plans. I backup all dbs to tape
>> at 1:00 AM (finishes by 1:45). At 2:00 I do an Integrity Check on all
>> user databases and have it set to fix any minor problems. Finally at
>> 5:00 AM I schedule and optimization to reorganize data and index pages
>> and change the free space to 10%. Each of these takes from 10 minutes to
>> 45 minutes to complete so none of them should be running during the day
>> when this error happens.
>>
>> Any ideas on what could be causing this.
>