Update Query 1.5 Million Records taking 5 hours...

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Update Query 1.5 Million Records taking 5 hours...

Author

Topic

kennethsia
Starting Member

2 Posts

Posted - 2012-11-06 : 03:06:58

Hello,

I am trying to update my table that has 1.5 million records on it right now its running for 5 hours and still executing query is it normal to be this long? my other database that has 500k records only took 22 minutes.

This is my query

ALTER TABLE [Character] ALTER COLUMN Inventory BINARY(3776)
ALTER TABLE [Character] ADD [ExtInventory] TINYINT NOT NULL DEFAULT 0
ALTER TABLE [Character] ADD [S6EP3_PS_FIX] TINYINT NOT NULL DEFAULT 0
GO
UPDATE [Character] SET S6EP3_PS_FIX = 0

Any advice is highly appreciated

Thanks!

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-11-06 : 07:58:15

Some ideas - which may apply to your situation:
1) If the table has too many non clustered indices, it is better to disable them during update and enable it again after update
2) Batch the Updates
3) Ensure all login are disconnected
4) Turn off services connecting to database

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

kennethsia
Starting Member

2 Posts

Posted - 2012-11-06 : 09:20:39

Problem is im not good in ms sql im just running this update query for my gameserver and im really frustrated that this query is running 6 hours and nothing is happening, is there anyone here kind enough to assist me i will be willing to pay for your service.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-16 : 22:42:19

Jack has already explained.

Update in batches and make sure you do this in low activity time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources