SELECT INTO running for 22 hours

  • Some executed a SELECT INTO and it has been running for 22 hours.

    He was running an SSIS Package and he tried to stop it. SSIS did not respond so he killed the package so it retained the connection.

    It involves a large number of record. I replaced the columns with a COUNT(*) and I got an overflow on a int column.

    It is impacting other processes.

    If I kill the SPID it will go into a rollback for at least 22 hours.

    Any ideas on a safe course of action?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • If it is inserting, and you want it to stop, I don't think you can avoid a rollback.

    If you're lucky , it hasn't been doing inserts the entire 22 hours, so maybe the rollback will be quicker. Perhaps it has spent some time blocked or running slowly due to other processes. As I recall after running "kill", if you run it again, it shows you a percent complete. Not sure how accurate it is.

    Your post was 3 days ago, so I assume you've resolved it somehow ?

  • homebrew01 (9/9/2013)


    If it is inserting, and you want it to stop, I don't think you can avoid a rollback.

    If you're lucky , it hasn't been doing inserts the entire 22 hours, so maybe the rollback will be quicker. Perhaps it has spent some time blocked or running slowly due to other processes. As I recall after running "kill", if you run it again, it shows you a percent complete. Not sure how accurate it is.

    Your post was 3 days ago, so I assume you've resolved it somehow ?

    The code was awful.

    Missing JOINs etc. It was attempting to insert over a billion records.:w00t:

    I let it run and it finally finished in about 26 hours.

    There was no blocking.

    I have had problems with running the KILL two or more times. You have to look at the Log or Windows event viewer.

    I did not want to put the Database in recovery mode for fear it would not recover.

    Thank you for your input.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 3 posts - 1 through 2 (of 2 total)

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