A Update Data Optimization job freezed the server

[Yan Li]

Hi, everyone,

I have a SQL2000(SP1)/Windows2000, and a database - 4GB. The server physical memory is 1GB, and about 900MB is allocated to the SQL2000 through the memory dynamical configuration screen.

A scheduled database maintenance job was created using the standard maintenance planner tool. In this job, only Updata Data Optimization was included. Everytime, this job freezed the whole server - all screens, SQL client accesses, and, even the keyboard. A server machine reboot had to be made.

My understanding about Optimizatoin is that it does reindex for each index of each table in the database. So I picked the largest table in the database - about 1.5 million rows - to run DBCC DBREINDEX manually against each index one at a time, using the Query Analyzer. They were all running through, without causing server lockup. However, I noticed that each time, the DBREINDEX statement made the CPU and Physical I/O climbed extreamlly high. I suspected that, since the Optimization job put all tables together to DBREINDEX, it might exhaused the system resource that caused the server problem, but I am not 100% sure, neither do I know where to confirm this.

Confusing enough, other databases on the same server didn't have the problem. They all have maintance plan and run ok. I did all sorts of DBCC checks against this database which I had problem with, they were no error reported. I even loaded the database, using the backup, to another SQL 2000 server, and run Optimization there, and it had no problem.

I am not sure it is a server issue? or a database issue? or a SQL bug? Anybody had same experience?

Appreicate!

Regards! Yan Li

[Kurt]

Yan,
Are you allowing SQL Server to allocate it's own memory or have you fixed the amount SQL Server's memory at 900M? If you have I would suggest that you allow SQL Server to allocate and deallocate memory dynamically. Also, make sure that no other processes are accessing your database when you do the update. It sounds to me like you are running out of resources, but it could also be that another process may be trying to update the same table at the same time. The fact that the screens and keyboard lock up sounds as if you're running out of memory and the update is hogging up all the CPUs. How long have you let it run? If you did, how long did it take to finally complete. Or when you noticed the lock up did you assume that the system was dead? You should try letting it go, if possible for at least an hour. It may end up going back to normal.



------------
Yan Li at 7/2/01 12:44:26 PM


Hi, everyone,

I have a SQL2000(SP1)/Windows2000, and a database - 4GB. The server physical memory is 1GB, and about 900MB is allocated to the SQL2000 through the memory dynamical configuration screen.

A scheduled database maintenance job was created using the standard maintenance planner tool. In this job, only Updata Data Optimization was included. Everytime, this job freezed the whole server - all screens, SQL client accesses, and, even the keyboard. A server machine reboot had to be made.

My understanding about Optimizatoin is that it does reindex for each index of each table in the database. So I picked the largest table in the database - about 1.5 million rows - to run DBCC DBREINDEX manually against each index one at a time, using the Query Analyzer. They were all running through, without causing server lockup. However, I noticed that each time, the DBREINDEX statement made the CPU and Physical I/O climbed extreamlly high. I suspected that, since the Optimization job put all tables together to DBREINDEX, it might exhaused the system resource that caused the server problem, but I am not 100% sure, neither do I know where to confirm this.

Confusing enough, other databases on the same server didn't have the problem. They all have maintance plan and run ok. I did all sorts of DBCC checks against this database which I had problem with, they were no error reported. I even loaded the database, using the backup, to another SQL 2000 server, and run Optimization there, and it had no problem.

I am not sure it is a server issue? or a database issue? or a SQL bug? Anybody had same experience?

Appreicate!

Regards! Yan Li