clients sometimes get locked and can't write

I have an SQL 2000 back end. We have a couple of databases with about 12-20
tables a piece. About once or twice a month there are times for up to an
hour or so where no one can write from any of the front end applications to
the SQL 2000 back end. Each application gives their own error which all are
basically a time out waiting to write. Often times the system will just
start working again on its own.
When this happens the first thing we check in the Enterprise Manager is for
any locks and there are none. We then check to see if one of the other
applications like Access 2003, Labview or others can write to the database
and we see they cannot. Next we see if we can open the table on the SQL
server and edit or append and we CAN from the Enterprise Manager?
Thinking that there might be someone locking a record or a page we kick
everyone out and just let one person (sometimes myself as an admin) try to
work and they cannot. We then restart the SQL server and the workstations
and have someone try again and they still fail.
The only thing that I have seen help get it out of this state is when I go
to my daily maintenance jobs and run both my integrity check and my
optimization plan. The Integrety Check plan was done with the wizard and
it:
1. Checks the integrity of all user database
2. Includes indexes
3. Attempts to repair minor problems.
4. Run every day at 12:00 AM
The Optimization plan is set to:
1. Reorganize Data and Index Pages
2. Change free space to 10%
3. Run every day at 1:30 AM
Finally, when I look in the Event Viewer logs for anything strange I really
do not see anything in the app, security, or system logs anywhere near the
time of the start of the errors.
Sounds like the SQL Server Agents for the SQL Server instances aren't
running sometimes. Goto start->administrative tools->services and
look for the SQL Server Agent (your server instance name) entries and
see their status. Should be in "Started" state. If not started,
click on the entry and click start in top left corner of pane.
SQL Server Agents are the programs that act as proxies to enable
remote access to a SQL server instance. If they are not running,
nobody will be able to gain access to the server, and will receive
time out errors instead.
Some programs, such as backups, may stop the agents so they can take a
snapshot of the database. These programs should start the agents as
soon as they are done. If you find the agents in a stopped state in
the next outage, you should try to trace down what program was
executing at the time they were stopped, and check if that program is
configured properly.
|||I don't think it is the agent because the users are able to read the data
remotely. Also, the only maintenance that is done is done at 1-3 AM. This
problem happens randomly mid day.
"Andy" <anedza@.infotek-consulting.com> wrote in message
news:e1159a72-4a2a-449d-a8d7-d22be76d6595@.q78g2000hsh.googlegroups.com...
> Sounds like the SQL Server Agents for the SQL Server instances aren't
> running sometimes. Goto start->administrative tools->services and
> look for the SQL Server Agent (your server instance name) entries and
> see their status. Should be in "Started" state. If not started,
> click on the entry and click start in top left corner of pane.
> SQL Server Agents are the programs that act as proxies to enable
> remote access to a SQL server instance. If they are not running,
> nobody will be able to gain access to the server, and will receive
> time out errors instead.
> Some programs, such as backups, may stop the agents so they can take a
> snapshot of the database. These programs should start the agents as
> soon as they are done. If you find the agents in a stopped state in
> the next outage, you should try to trace down what program was
> executing at the time they were stopped, and check if that program is
> configured properly.
>
>
>