Hi All,

I was running a sql script in Query Analyzer to delete
the rows from a table. The Deleting process was taking very long time
and I had to close the query analyzer.
I re-started the query analyzer and that process was still running.
When I try to kill the process (kill 51), I got the following message;

"SPID 51: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 155 seconds."

This table is locked and I cannot access the table.

Thanks in advance,

mcinar

__________________
MCinar

Love all the creatures because of the creator.

If your process have not stop and showing as executing. Stop and restart your SQL Server Agent

Jaime E. Maccou
Applications Analyst

Jemacc,
Thank you for your advice.
I tried to stop and restart "MS SQLSERVER",
but I get the following error message during the restart, "MS SQLSERVER stopping. An Error 1056
(An instance of the service is already running. Occured while performing the service operation on the
MS SQLSERVER service."

I also stopped and restarted SQL Server Agent.

Thanks,

MCinar

What this indicates is the you may have already tried to stop the SQL Server Services and it could not completed communication with the services.

you force a stop manually from the command line

net stop mssqlserver

and to restart a default instance
sqlservr.exe -c

and to restart a name instance
sqlservr.exe -c -s {instancename}



Jaime E. Maccou
Applications Analyst

Jemacc,

Thank you for the advice.
I tried to use "net stop mssqlserver", I got the following message;
"This service could not be controlled in its present state.
More help is available by typing NET HELPMSG 2189"

And this help command explains;
"Try the operation again in a minute or two.
If this problem persists,, contact tech support. Be prepared to give the name of the service and
other information about the system. Such as the services and applications that were running, and the type and amount of network activity on the computer at the time of the problem"

I am not sure what else can be tried?

Thanks,

MCinar

If you are familiar with task manager, check the status of the SQL server (sqlsrv.exe) and try to end the process from there.
Is this your production server? If no, reboot the server and this should clearly and refresh all services.

Jaime E. Maccou
Applications Analyst

Jemacc,
It is a test server and I will re-start the computer.
If that doesn't work, I will re-install SQL server and restore the database.

Thanks,

MCinar

This is also a good time to review your disaster and recovery plan. If you do not have one, I suggest that you put one in place.


I hope everything works out for you.

Jaime E. Maccou
Applications Analyst

Jemacc,

Thank you,

MCinar

HI,

U can use Current Activity from Enterpise manager to Kill the process selectivel (U must be a member of sysadmin fixed role).

Further I suggest u to use TRUNCATE Table instead of Delete. Its quick but be aware that it doesn't log the Transaction.

I am not sure if u r using transaction block to perform the delete. There may be chnaces where u r Beggining a Transaction and not commiting it. Such case will prevent even opening a table in firehose mode through enterprice manager.

Hope this helps

B. Anant