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 |
|
lbusuioc
Starting Member
4 Posts |
 Posted - 2006-11-16 : 17:15:16
|
| 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
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-11-16 : 23:17:35
|
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 bigtableI 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 forSQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-17 : 00:41:54
|
| "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 |
|
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
|
|
Insert/Update hanging