Browsed by
Category: SQL

How to kill all connections to a SQL Server DB

How to kill all connections to a SQL Server DB

Script to accomplish this, replace ‘DB_NAME’ with the database to kill all connections to:

USE master
GO

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DB_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END
Check all SQL table size

Check all SQL table size

With this simple query, you can find size of all tables on sql database

EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”

otherwise if you need to check a single table

you can use the following query

exec sp_spaceused tablename;

Orphaned Users

Orphaned Users

If you migrate DB between sql server or if you restore a DB on new sql server, you may get orphaned sql user.
To detect if you’re in this condition, run this sp.
EXEC sp_change_users_login ‘Report’
This will report all login inserted into db and not in sql server.

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login ‘Auto_Fix’, ‘user’
“Auto Fix” work only with windows users!

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login ‘Auto_Fix’, ‘user’, ‘login’, ‘password’