All of my friends say, “How much longer will it run?”

I have frequently needed to see how much longer a backup was going to run, or how much longer a restore was going to run.  This little bit of code is something I use to help me know how to plan.  I replace “Backup” with “Restore” if I want to see how much longer to plan for a restore.  I also use it for when I am tracking a rollback.  I remove the where clause and get a large list, the one that has a percentage and not a clear explanation is usually the one I want.   Sometimes the time remaining is lie.  I have had it imply 4 hours, when it took 13.  The comfort is that I can tell it is still working.


SELECT command, percent_complete,
'elapsed' = total_elapsed_time / 60000.0,
'remaining' = estimated_completion_time / 60000.0
FROM sys.dm_exec_requests
WHERE command like 'BACKUP%'

The song for this post is “Just Another Girl” by The Killers

You must not know about me, I can kill connections in a minute

It is still snowy and cold outside so short post today about killing connections.  I love this quick little script and can’t remember where I got it from, so huge Thank You to the person that created it.  I use it when I need to restore a database that still has active connections.  This will first print a list of the connections it wants to kill for the database in the where clause, if you uncomment the “EXEC” part, you will get the fastest connection killer you have ever used. 

DECLARE @SQL VARCHAR(8000)

SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '
FROM sys.sysprocesses
WHERE DBID=DB_ID('DatabaseName')

PRINT @SQL
--EXEC(@SQL) /*Replace the print statement with exec to execute*/

Enjoy and Have a Magical Day!