SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame
select t.name as TableName, Min(t.create_date) as CreateDate, ds.name as FileGroupName, SUM(u.total_pages) * 8 / 1024 as SizeMB from sys.tables as t inner join sys.partitions as p on t.object_id = p.object_id inner join sys.allocation_units as u on p.partition_id = u.container_id inner join sys.data_spaces as ds on u.data_space_id = ds.data_space_id group by t.name, ds.name order by SizeMB desc
set nocount on declare @databasename varchar(100) declare @query varchar(max) set @query = '' set @databasename = 'databasename' if db_id(@databasename) < 4 begin print 'system database connection cannot be killeed' return end select @query=coalesce(@query,',' )+'kill '+convert(varchar, spid)+ '; ' from master..sysprocesses where dbid=db_id(@databasename) if len(@query) > 0 begin print @query exec(@query) end
CREATE TRIGGER [IP2user_block] ON ALL SERVER FOR LOGON AS BEGIN /* Логирование отработки тригера DECLARE @IP NVARCHAR(MAX)=(SELECT DISTINCT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID); DECLARE @body1 NVARCHAR(MAX)='Login: ' + ORIGINAL_LOGIN() + 'IP: ' + @IP; */ IF ORIGINAL_LOGIN()!= 'User' AND (SELECT DISTINCT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID)='xxx.xxx.xxx.xxx' BEGIN ROLLBACK; INSERT INTO test.dbo.test2 (test) values (@body1); END; END; GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER [IP2user_block] ON ALL SERVER GO