Friday, March 22, 2019

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

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

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
  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.