SQL 2000 Optimize Maint Step locks up server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TMurnion - 23 Jul 2008 23:36 GMT

We have an sql 2000 sp4 server that locks up whenever we try to run a
maintenance plan with only the option to Reorganize data and index pages
option check. There are no event logs or entries in the activity logs for
what happens. The server is just locks up and has to be powered off to
restart

Any assistance is greatly appreciated.
Tony

Reply to this Message

Erland Sommarskog - 23 Jul 2008 23:45 GMT

> We have an sql 2000 sp4 server that locks up whenever we try to run a
> maintenance plan with only the option to Reorganize data and index pages
> option check. There are no event logs or entries in the activity logs for
> what happens. The server is just locks up and has to be powered off to
> restart

Have you checked the SQL Server error log for messages?

You say that the server has to be powered off. Does that mean that
Windows in entirety is not unresponsive?

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

TMurnion - 24 Jul 2008 00:01 GMT

I've checked both the Event Logs via Adminstrative Tools and the Activity
Logs within SQL. Is there another place to check.

And yes Windows is not responsive. Whatever is happening does not have the
chance to generate a log.

Thanks.

Tony

>> We have an sql 2000 sp4 server that locks up whenever we try to run a
>> maintenance plan with only the option to Reorganize data and index pages
[quoted text clipped - 7 lines]
> You say that the server has to be powered off. Does that mean that
> Windows in entirety is not unresponsive?

Reply to this Message

Erland Sommarskog - 24 Jul 2008 10:13 GMT

> I've checked both the Event Logs via Adminstrative Tools and the Activity
> Logs within SQL. Is there another place to check.

C:\Program Files\Microsoft SQL Server\MSSQL\LOG, files ERRORLOG to
ERRORLOG.6, given that you have SQL Server installed in the default
location. I don't if this what you refer to as the "Activity logs",
but these are the files I'm curious about.

Signature

Reply to this Message

TMurnion - 24 Jul 2008 21:33 GMT

Same thing, different way to access. I went in through Enterprise
Managerunder the SQL Server Logs. I misnamed it "Activity log". sorry
about that. That's what I get for writing the email from memory. ;-)

Thanks

Tony

>> I've checked both the Event Logs via Adminstrative Tools and the Activity
>> Logs within SQL. Is there another place to check.
[quoted text clipped - 3 lines]
> location. I don't if this what you refer to as the "Activity logs",
> but these are the files I'm curious about.

Reply to this Message

Andrew J. Kelly - 24 Jul 2008 01:13 GMT

It sounds like you may be doing too much I/O that your system can't keep up
with it. If the disks are pegged the system will seem unresponsive. How
large is your db?

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> We have an sql 2000 sp4 server that locks up whenever we try to run a
> maintenance plan with only the option to Reorganize data and index pages
[quoted text clipped - 4 lines]
> Any assistance is greatly appreciated.
> Tony

Reply to this Message

TMurnion - 24 Jul 2008 21:30 GMT

The database is about 5.8GB including transaction log. I don't think it is
an IO thing. I set this to go a 4am and it was unresponsive still at
7:55am. The tran log had not grown during that time so I don't think it got
past starting up.

Thanks

Tony

> It sounds like you may be doing too much I/O that your system can't keep
> up with it. If the disks are pegged the system will seem unresponsive. How
[quoted text clipped - 8 lines]
>> Any assistance is greatly appreciated.
>> Tony

Reply to this Message