Category: Windows

SQL script to kill all connections to a SQL Server database

I have finally perfected a nice script for killing connections to databases. Although basic, I found many of the ones I google’d, had one problem or another that stopped them working straight away, which just wastes time. This one works! I have just used it.

Just replace the database name and it’s good. It is easy to alter it to work across a list of databases too.

-- =============================================
-- Author: Nicholas Rogoff
-- Create date: 04/03/2010
-- Description: Script that Kills all connections to a database
-- except the current one
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @execSql NVARCHAR(1000)
DECLARE @databaseName VARCHAR(100)
DECLARE @NoKilled INT

-- *** CHANGE THE NAME OF THE DATABASE *** ---
SET @databaseName = '#### Replace this with a database name here ####'
PRINT 'START: Killing active connections to the "' + @databaseName + '" database'

-- Count Connections
select @NoKilled = COUNT(*)
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
 and
 DBID <> 0
 and
 spid <> @@spid

-- Create the sql to kill the active database connections
set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
 and
 DBID <> 0
 and
 spid <> @@spid

exec (@execSql)

PRINT 'END: Killed "' + CAST(@NoKilled AS VARCHAR(4))
+ '" active connections to the "' + @databaseName + '" database'
GO

The free online div table generator allows you to create nice grids for your websites. Please subsribe for a htmlg membership to stop adding promotional messages to the edited documents.

How to bulk unblock files in Windows 7 or Server 2008

In Windows 7 (actually any of the latest Windows releases) you will find that files copied to your local drives are not trusted until you right-click on them and in the properties click the ‘Unblock’ button. This is all very well for one or two files, but gets extremely tedious with more. It turns out to be related to NTFS’s ability to allow alternate data streams.

So here is a simple method of mass or bulk unblocking files.

  1. Download the Sysinternals Streams.exe from http://technet.microsoft.com/en-us/sysinternals/bb897440.aspx
  2. I find Sysinternals so useful I create a ‘SysInternals’ folder on my C: drive and then put this into my ‘path’ environment variable so that I can run them from anywhere. However, if this is too much then the easiest is to copy the streams.exe to the root folder you wish to have files unblocked.
  3. In a command windows (with Admin privileges), use ‘cd’ to navigate to your folder.
  4. Type the command ‘streams –s –d subfoldername’ and press enter.