Let's say that, in SQL Server 2012, you start a manual backup of a database. But, during the backup, you realize that the database is 95GB and the drive that you're backing up to only has approximately 20.0 GB of space. And, the drive contains some of the production SQL database files!
So, I needed to cancel this manual backup, as quick as possible.
I found the following script online that led me to session 101:
SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
So, I ran "kill 101".
But, at least at first, it did not work. I mean, I think that it stopped the backup, eventually, but the backup began again at 10%. (It was at 60%, originally.)
So, I ran that script again and it led me to sessions 101, 103, and 104. So, I simply ran the same "kill" statement again against each of those session IDs. It worked! The backup stopped, and I received an error message saying that the backup could not run on the server.
Good. The drive did not fill up with extra space, because the backup file that I was making disappeared, the database did not go into "Suspect" mode, and the manual backup stopped. I was able to proceed with my work in the application that integrates with that database.
So, I have two questions. Is the scripting that I ran "good enough" for killing a manual database backup?
Secondly, will this scripting and kill statement cause damage to the database?