Deleting large number of rows times out

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

Express Edition and Compact Edition (2005)

Deleting large number of rows times out

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-14 : 05:28:46

I have a table with about 10 million rows, its been logging data incorrectly and I want to start again.

DELETE FROM tblLog

I just get a message about the server timing out, what can I do?

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 05:31:47

If you want to delete all rows then try truncation

Truncate table tblLog

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 05:31:54

Try "TRUNCATE tblLog"

Or read about "BULK DELETE" or "Deleting a massive number of rows" here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 05:32:07

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 05:37:46

If you cant delete then do batch deletion


declare @rows int
set @rows=10000
while @rows>0
begin
	set rowcount 10000
	delete from tblLog
	set @rows=@@rowcount
	set rowcount 0
end

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 05:40:53

I think even that may cause timeouts Madhi. The

delete from tblLog

is liable to make a working list, and then take the 10,000 rows from that. (Actually without any WHERE clause it will probably just start-in using any Clustered Index ... but if no CI available, or a Where clause required, then I think a more complex solution is called for (which I think is discussed in the link Peso posted)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources