Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kevin Snow
Posting Yak Master
149 Posts |
 Posted - 2002-05-06 : 13:49:50
|
| I'm running a scheduled DB Maintenance on a Clustered 2000 Server. This job is set for USER databases only. Optimizations are set for Reorg on data and index pages, reorg with original amount of free space.Remove unused free space from database files is active beyond 50MB, with 20% free space to remain after shrinking.On other servers with larger databases, this might take 1/2 hour or so.However, when this job kicks off on our 8-way 700Mhz server, I get 5 processors pegged at 100%, with 3 banging hard between 0 and 100 percent usage. SQL Server becomes unresponsive. EM cannot get a connection, Activity cannot be accessed, Querymanager cannot get its attention long enough to kill the SPID. Server Manager cannot shut down the server. Our only recourse has been to reboot the node. It has been allowed to run for 7 hours without completing.The only error listed in the Maintenance Plan History is that it could not execute a repair statement because the database was not in single user mode. I removed the repair option, and now get the same result sans error. DBCC Integrity checks come back with 0 errors.I have seen locking occur during the execution of some optimization plans, but nothing like this. Has anyone seen this kind of behavior in a maintenance plan? Any suggestions? |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-05-07 : 10:29:45
|
| Heres More.I've isolated the problem to a single database. Although DBCC CheckDB reveals no errors, If I try to run optimizations on this database, I lose SQL.What other diagnostics might I run? |
 |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-05-07 : 13:41:06
|
| I never uncovered an error using Diagnostics.However, I did run the database shrinkage optimization option on the database by itself. I also updated statistics. When I next ran the isolated Index optimization, I got an error stating the Transaction log was full. After expanding the size and backing up the Transaction Log, I was able to run the optimization successfully.I had used the default scheduling, which runs the optimizations and data integrity checks at the same time. These may have combined with the full transaction log to leave the maintenance job in a hung state. |
|
|
|
|
|
|
|
|
SQL Runs Away on Optimization Job