Category «SQL»

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 …

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

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 …