Windows - SysadminFAQ

MSSQL. Количество подключений к каждой базе

#mssql #sql

08 май, 2019

admin

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

AD FS. Замена/обновление сертификата

#windows #adfs #сертификат

08 май, 2019

admin

Импортируем новые сертификаты в хранилище. Запоминаем отпечаток нового сертификата:

Далее требуется удалить старые хеши сертификатов с рабочих служб: ...

MSSQL 2008. Размер таблиц базы

#mssql #windows #sql

17 апр, 2019

admin

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

MSSQL. Завершение всех активных соединений с базой

#sql #mssql

11 апр, 2019

admin

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

MSSQL. Ограничение входа по ip

#mssql #windows #sql

11 апр, 2019

admin

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