Query completed with errors

Question

Query completed with errors

iseedeadpeople

Mr or Mrs. 500

Points: 566
More actions
September 20, 2017 at 2:35 am

#325441

Hi, Guys i try to delete large data from my db about 1.5 mln rows ( 2012 Sql server), actually i delete this rows but strange i have completed with errors.I think is something wrong with script.Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

What are the errors you receive? We can't run your SQL and we don't have access to your data, server or environment, so I'm afraid only supplying your SQL doesn't really help us help you troubleshoot.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

iseedeadpeople Mr or Mrs. 500 Points: 566 More actions · Answer 2

This script delete data , but when finish message is Query Completed with errors., i think the problem is in this logic

The problem is i need to insert hude data in variable table where Id is clustered, which is gonna be deleted, but in this rows.I have to skip ids which have reference to another table.

Thom A SSC Guru Points: 99130 More actions · Answer 3

ndoee - Wednesday, September 20, 2017 3:07 AM
This script delete data , but when finish message is Query Completed with errors., i think the problem is in this logic

Yes, but what ARE the errors? If, for example, you're running the query in SSMS then the errors will be presented in Messages Pane of your Results window.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

iseedeadpeople Mr or Mrs. 500 Points: 566 More actions · Answer 4

I get it man i didnt see it because i hit some rows where have reference to another table.So the statement has been terminated, but the rows is delete it 🙂

Thom A SSC Guru Points: 99130 More actions · Answer 5

ndoee - Wednesday, September 20, 2017 4:00 AM
I get it man i didnt see it because i hit some rows where have reference to another table.So the statement has been terminated, but the rows is delete it 🙂

Ahh, I assume that there's some kind of foreign key pointing to your table, and as a result you can't delete the rows. You'll need to delete the rows in the child table (that are related to row you're going to delete in your parent) before deleting the rows in the parent.

Edit: Poor wording, could have been read as if I was saying the child table needs to be emptied, rather than only the relevant rows need to be deleted. Oops! :hehe:

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

iseedeadpeople Mr or Mrs. 500 Points: 566 More actions · Answer 6

Actually i take 50 rows in batch and when i see reference rollback but script dosnt stop move forward take another 50 and again and again, for now from 1.5 mln for 2 minutus i have 32 k when i add small number for batch like 1 ROW, query need 15 min but its good i have 5 k rows which have reference, anyway bro Thank you 🙂

aaron.reese SSChampion Points: 13498 More actions · Answer 7

If you are deleting the majority of the table, you may find it more performant to copy the records to DO want to a new (temp) table and then truncate the full table and insert the saved records back into it. Make sure you wrapt this all in a transaction in case it goes wrong.

kevaburg SSCoach Points: 18131 More actions · Answer 8

aaron.reese - Friday, September 22, 2017 7:41 PM
If you are deleting the majority of the table, you may find it more performant to copy the records to DO want to a new (temp) table and then truncate the full table and insert the saved records back into it. Make sure you wrapt this all in a transaction in case it goes wrong.

+1 to that. I would be more inclined to choose this method rather than such a large fully logged delete.