sql server delete statement

Pol 86 Reputation points
2021-01-10T02:25:50.01+00:00

Hi,

I am running delete commend with where clause. The session is still running. what ever the target count we achieved.

can i kill the delete query session to stop the activity? I have not used any begin and commit transaction. if i kill the session will the deleted rows will be rollbacked? or it will commit?

Please suggest whether i can kill the session or not as the target values got deleted.

Thanks,

Pol

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,656 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 112.7K Reputation points MVP
    2021-01-10T11:02:11.307+00:00

    Yes, a statement in SQL Server is atomic, so if you kill the session, everything will be rolled back. Beware, though, that the rollback can take quite some time.

    I like to add that when you talk about "target values" I have no idea what that means, because I don't know the context where you run this DELETE.

    0 comments No comments

  2. MelissaMa-MSFT 24,201 Reputation points
    2021-01-11T02:15:04.893+00:00

    Hi @Pol

    Thank you so much for posting here in Microsoft Q&A.

    The session is still running. what ever the target count we achieved.

    Could you please provide more details about above? What is the output message?

    Normally we could have one (XX rows affected) message once delete completed.

    If you kill the session, all have to rollback, which is even more expensive with a huge session.

    So normally you just have to wait, and it completes or eventually it rolls back.

    Adding begin and commit transaction together with try ...catch could be better to you.

    You could also try to restore from a backup if failed with deleting or killing.

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. SQLZealots 276 Reputation points
    2021-01-12T04:17:16.663+00:00

    First off, You may try to find out the reason for long running, if there are any lock or block happening. You can refer the below link to get the query used for monitoring.

    https://sqlzealots.com/2015/02/24/live-monitoring-queries-to-troubleshoot-issues-in-sql-server/

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.