Welsh Corgi (11/6/2013)
The Transaction Log Backup is 145 GB. Tried shrinking it but it did not make much of a difference.
No, it won't because there's no free space in the log because of an active transaction (the delete). Shrink removes unused space, if the log is growing then it follows there's no unused space for shrink to remove.
They tried canceling the Query from SSMS and closed the window so I can not stop the query.
You can use the KILL command to cancel the query and start a rollback, though if they tried cancelling from SSMS it it's probably already in rollback.
What about ROLLBACK IMMEDIATE?
From Books Online (ALTER DATABASE)
Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely if there is any lock on the database. Only one termination clause can be specified, and it follows the SET clauses.
ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
Specifies whether to roll back after the specified number of seconds or immediately.
The IMMEDIATELY is when to start any necessary rollback.
If I kill the process it will take at least 9 hours to rollback.
Probably yes, maybe longer.
If I restart SQL Server Service that could corrupt the Database.
No it won't. It will however continue the rollback after the restart, with the database unavailable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability