Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / July 2008

Tip: Looking for answers? Try searching our database.

SQL 2000 Optimize Maint Step locks up server

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
TMurnion - 28 Jul 2008 22:57 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 sql error logs for
what happens.  The server is just locks up and has to be powered off to
restart

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. Whatever is happening does not have the chance to generate
a log.

I'm open to any ideas on what might be causing this.

Thanks in advance

Tony
Andrew J. Kelly - 29 Jul 2008 14:36 GMT
Well I would stop using the maintenance plan in the first place. You can do
the same thing with DBCC DBREINDEX.  With so little info it is impossible to
say what the actual problem is but I would eliminate the maint plan
altogether as a start. Creating your own job will allow you to have much
more control over the process and hopefully get better error logging or
tracing of what is happening. If you look in BooksOnLine under DBCC
SHOWCONTIG you will see a sample script that allows you to only reindex the
indexes that actually need reindexing. This will reduce significantly the
time it takes and load on the server.  I doubt this is a SQL Server issue to
begin with but again it is impossible to say. Since this is a very resource
intensive process that uses lots of memory, disk and cpu it can affect many
aspects of the server. If there is an issue under load this is a likely
candidate to bring it out.

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 - 16 lines]
>
> Tony
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.