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
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Update Query 1.5 Million Records taking 5 hours...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kennethsia
Starting Member

Philippines
2 Posts

Posted - 11/06/2012 :  03:06:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1912 Posts

Posted - 11/06/2012 :  07:58:15  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

kennethsia
Starting Member

Philippines
2 Posts

Posted - 11/06/2012 :  09:20:39  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/16/2012 :  22:42:19  Show Profile  Reply with Quote
Jack has already explained.

Update in batches and make sure you do this in low activity time.
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