Kill all connections to SQL Server database -- fast

What's the fastest way to kill all database connections?  Properly, I should say... without pulling the cord on the server.

I'm glad you asked! 😉  The easiest/fastest way that I am aware of is to set the database into SINGLE_USER.  This will sever all active connections to the database aside from your own.  Like this: 

-- kill all connections to a database

USE master;

ALTER DATABASE SSISDB -- change to your dbname

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE; 

When you've done your thing and you're ready to let them back in, you'll set it back to MULTI_USER, like this:

-- set it back into multi user

USE master;

ALTER DATABASE SSISDB -- change to your dbname

SET MULTI_USER;

Easy peasy.

You could also use a cursor to go through all active processes in the database, killing them sequentially... but, I don't often recommend the cursor method, if it can be avoided.

How long is that SQL Server backup going to take?

Good question.  I was backing up a customer database today and let them know about how long to expect it to take.  They said 'No... it should only take a couple hours!'  I had no experience w/this particular database before, so I thought I'd query the system to see how long the last one ran for.  This post is the query I used.  Just a super quick way to get the duration of the last backup for the given database.  

DECLARE @dbname sysname

SET @dbname = 'JDE_PRODUCTION' -- your dbname here

SELECT

  bup.user_name ByWhom,

  bup.database_name DatabaseName,

  bup.server_name ServerName,

  bup.backup_start_date StartTime,

  bup.backup_finish_date EndTime,

  CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS INT))/3600 AS VARCHAR) + ' hours, '

       + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS INT))/60 AS VARCHAR)+ ' minutes, '

       + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS INT))%60 AS VARCHAR)+ ' seconds' Duration

FROM msdb.dbo.backupset bup

WHERE bup.backup_set_id IN (

   SELECT MAX(backup_set_id)

   FROM msdb.dbo.backupset

   WHERE database_name = @dbname

   AND type = 'D' -- change for the type you need;  I = DIFF, L = LOG, D = FULL

   GROUP BY database_name

   );

And there you have it.  Short and sweet.  Just the kind of tSQL I favor.  :)   

It's not a guaranteed duration going forward, but it will certainly give you very close expectations.  Unless of course the thing increased or decreased in size dramatically... but that's another story.