SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Insert/Update hanging
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lbusuioc
Starting Member

4 Posts

Posted - 11/16/2006 :  17:15:16  Show Profile  Reply with Quote
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.

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 11/16/2006 :  23:17:35  Show Profile  Visit jezemine's Homepage  Reply with Quote
Not sure if this will help you, but just in case:

I had a similar problem recently (on 2005), but it was on a select statement, not insert/update. I had a large table, about 700m rows, and this simple query would just hang: select top 1 * from bigtable

I finally tried rebuilding the index on the table (it had just one index, clustered) and that fixed it.


SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 11/17/2006 :  00:41:54  Show Profile  Reply with Quote
"unlimited growth , 10% file growth"

That's going to make a 20GB extension ... which will take SQL Server a long time, and users will start to see timeouts and all sorts.

How much does your databases grow in a month? Consider dividing that by 30 and using that as a fixed size file extension so you get one extension a day - or if that is a really small extension size round it up. 100MB - 500MB seems like a sensible size for extension to me.

You can use DBCC SHOWCONTIG to see how much your indexes are fragmented. If they are badly fragmented then look into rebuilding them.

Maybe the cached query plans for your queries are stale, and no longer representative of the "shape" of the data? Make sure you Update Statistics at regular intervals - and use the FULLSCAN option. (We do ours daily)

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 11/17/2006 :  00:42:27  Show Profile  Reply with Quote
P.S. If that turns out to the the issue there is more info here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Rebuilding%20Indexes,DBCC%20CHECDB,Reindex,INDEXDEFRAG,Tara%20Blog%20Houskeeping%20Routines

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000