SQL Server Performance Forum – Threads Archive

Updates hanging

I have couple of SQL Server 2000 databases, each of them having the size around 200GB.
Recently I started experiencing some problems while inserting/updating data.
The problem is that it seems like it just hangind and it never finish the insert/update. I tried a simple update that affects just few records and it still hangs without any reason. There is no process locking the update process.
If I go to the database properties and increase the size of the database and then perform the update/insert all works well. My databases are set for unlimited growth , 10% file growth. I have plenty of space free on the disk.
In any case, it looks like it is not able to grow by itself.
I use SQL Server 2000, Enterprise Edition. I would appreciate your help.

---

Maybe you get timeouts during the growth operation. Growing 10 % on a 200 GB database is 20 Gb, which can take a lot of time. Try changing the autogrowth to a fixed size in Mb, e.g. 100 Mb. If that works, you’ll have to decide what a good amount is for growth: with a smaller size the files will have to grow more often, but the performance hit each time will be smaller.

---

Is Auto update statatistics enabled? If not try afterenabling it. Mohammed.

---

Hey Mohammed, good to see you here…keep up the good work.
I would also suggest to update the statistics on the tables that are inserted and updated frequently, also recompile the SPs and triggers in this case. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

]]>