Как узнать активные сеансы пользователей в MS SQL 2008/2012

Иногда бывает необходимо узнать, кто именно сейчас работает в базе данных или в базах данных на сервере MSSQL 2008/2012. Например, для того чтобы принудительно завершить все эти сеансы или просто узнать, кто именно нагружает сервер запросами. Сегодня мы научимся с Вами это делать, используя при этом простые запросы к системным представлениям на Transact-SQL.

Как Вы уже поняли, сегодня речь пойдет об активных сеансах и процессах в СУБД MSSQL, которые мы будем получать, используя системное представление sys.sysprocesses.

Содержит сведения о процессах, которые выполняются в экземпляре SQL Server. Эти процессы могут быть клиентскими или системными. Для доступа к sysprocesses либо необходимо быть в контексте главной базы данных, либо следует использовать трехчастное имя master.dbo.sysprocesses.

Для того чтобы понимать, что такое системное представление, советую Вам для начала ознакомиться с понятием простого представления, которое рассматривается в статье - Зачем нужны представления (views) в базах данных. Также мы будем писать пусть простые, но все запросы, с основами которых Вы естественно должны быть знакомы, если нет, то можете прочитать статью основы языка SQL - оператор select.

Вроде бы все, что необходимо знать, перед тем как что-то пробовать из ниже приведенных примеров на практике я указал, переходим непосредственно к самой сути сегодняшнего вопроса.

Как узнать активные сеансы пользователей

Системное представление sys.sysprocesses содержит текущее состояние сервера на предмет запущенных процессов, исходя из этого, напишем простенький запрос:

select db_name(dbid) as db, spid as idproc, loginame, program_name, status
from sys.sysprocesses

где,

db – это база данных, в которой запущен процесс;
idproc – идентификатор процесса;
loginame – логин, т.е. кто именно запустил;
program_name – приложение, из которого запущен процесс;
status – соответственно статус.
Статусы бывают разные, например,

Runnable – активный процесс, т.е. например, в данный момент выполняется какой-нибудь запрос;
Sleeping – режим ожидания, т.е. например, окно запроса открыто, но в данный момент он не запущен;
Background – запущен в фоновом режиме.
Если необходимо узнать, кто именно работает конкретно в той или иной базе можно добавить условие:

select db_name(dbid) as db, spid as idproc, loginame, program_name, status
from sys.sysprocesses
where db_name(dbid) = 'name_db'

Соответственно вместо name_db пишите свою нужную базу данных, и результат будет получен только по этой базе данных.

Как завершить все активные сеансы пользователей

Это необходимо, например, для того чтобы получить монопольный доступ к базе данных, а для чего он Вам может понадобится это уже Ваше дело, например, чтобы восстановить базу данных. Мы кстати рассматривали процесс восстановления базы данных, правда, на MSSql 2000.

Для того чтобы завершить все подключения пользователей к бд, выполним следующий запрос:

set nocount on
declare @dbname varchar(100)
declare @query varchar(max)
set @query = ''
set @dbname = 'name_db'
select @query=coalesce(@query,',' )+'kill '+convert(varchar, spid)+ '; '
from sys.sysprocesses where dbid=db_id(@dbname)
if len(@query) > 0
begin
exec(@query)
end

где,

@dbname – переменная, для того чтобы указать к какой базе необходимо завершить все подключения;
@query – переменная для хранения запроса;
В конструкции select мы динамически формируем запрос с идентификаторами процессов, которые необходимо завершить. Далее в переменной @query будет храниться запросы вида

kill 58; kill 61; kill 70;

которые мы выполним через exec(@query) и тем самым завершим все процессы.

 

Из всего выше сказанного Вы легко можете узнавать, кто именно сейчас выполняет запрос и даже завершить его, что в свою очередь иногда бывает полезно.

Также хотелось бы сказать, что в MSSQL существует встроенный «Монитор активности». Его можно вызвать, нажав правой кнопкой по серверу, в окне «обозреватель объектов» и вызвать окно «Монитор активности», на котором будет располагаться список свойств, которые Вы можете развернуть для подробного просмотра, где в свою очередь и будет отображать вся текущая активность на сервере.

Описание всех полей sysprocesses

Имя столбца Тип данных Описание
spid smallint Идентификатор сеанса SQL Server.
kpid smallint Идентификатор потока Windows.
blocked smallint Идентификатор сеанса, блокирующего данный запрос. Если этот столбец содержит значение NULL, то запрос не блокирован или сведения о сеансе блокировки недоступны (или не могут быть идентифицированы).

-2 = Блокирующий ресурс принадлежит потерянной распределенной транзакции.

-3 = Блокирующий ресурс принадлежит отложенной транзакции восстановления.

-4 = Идентификатор сеанса владельца кратковременной блокировки не может быть определен из-за внутренних переходов состояния кратковременной блокировки.
waittype binary(2) Зарезервировано.
waittime bigint Текущее время ожидания в миллисекундах.

0 = процесс не является ожидающим.
lastwaittype nchar(32) Строка, обозначающая имя последнего или текущего типа ожидания.
waitresource nchar(256) Текстовое представление ресурса блокировки.
dbid smallint Идентификатор базы данных, используемый процессом в данный момент.
uid smallint Идентификатор пользователя, выполнявшего команду. Вызывает переполнение или возвращает значение NULL, если количество пользователей и ролей превышает 32 767.
cpu int Совокупное время ЦП для процесса. Запись обновляется для всех процессов независимо от значения параметра SET STATISTICS TIME (ON или OFF).
physical_io bigint Совокупное количество операций чтения и записи для процесса.
memusage int Число страниц в кэше процедур, выделенных в данный момент для этого процесса. Отрицательное значение показывает, что процесс освобождает память, выделенную другим процессом.
login_time datetime Время регистрации клиентского процесса на сервере.
last_batch datetime Время последнего вызова удаленной хранимой процедуры или инструкции EXECUTE клиентским процессом.
ecid smallint Идентификатор контекста выполнения используется с целью идентифицировать подпроцессы, действующие от имени одного процесса, уникальным образом.
open_tran smallint Количество транзакций, открытых для данного процесса.
status nchar(30) Состояние идентификатора процесса. Возможные значения:

Неактивные = SQL Server сбрасывает сеанс.

под управлением = сеанс запущен один или несколько пакетов. Если включен режим MARS, в сеансе может выполняться несколько пакетов.

фон = сеанса запущена фоновая задача, например обнаружение взаимоблокировок.

откат = сеанс имеет отката транзакции в процессе.

Ожидание = сеанс ожидает доступности рабочего потока.

готов к запуску = задача в сеансе находится в очереди исполнителей планировщика, ожидая времени такта.

spinloop = задача сеанса ожидает освобождения объекта взаимоблокировки.

Приостановить = сеанс ожидает события, например ввода-вывода для завершения.
sid binary(86) Идентификатор GUID для этого пользователя.
hostname nchar(128) Имя рабочей станции.
program_name nchar(128) Имя приложения.
hostprocess nchar(10) в формате Идентификационный номер процесса рабочей станции.
cmd nchar(16) Команда, выполняемая в данный момент.
nt_domain nchar(128) Домен Windows для клиента, если применяется проверка подлинности Windows или доверительное соединение.
nt_username nchar(128) Имя пользователя Windows для процесса, если применяется проверка подлинности Windows или доверительное соединение.
net_address nchar(12) Связанный уникальный идентификатор для сетевого адаптера рабочей станции каждого пользователя. При входе пользователя в систему этот идентификатор вставляется в столбец net_address.
net_library nchar(12) Столбец, в котором хранится библиотека клиентской сети. Каждый клиентский процесс подключается к сетевому подключению. С сетевыми подключениями связана сетевая библиотека, позволяющая им устанавливать соединение.
loginame nchar(128) Имя входа.
context_info binary(128) Данные, которые хранятся в пакете с помощью инструкции SET CONTEXT_INFO.
sql_handle binary(20) Представляет пакет или объект, который выполняется в настоящий момент.

Примечание это значение формируется из адреса или в пакете памяти объекта. Оно не вычисляется с помощью алгоритма SQL Server на основе хэша.
stmt_start int Начальное смещение текущей инструкции SQL для заданной sql_handle.
stmt_end int Конечное смещение текущей инструкции SQL для заданной sql_handle.

-1 = текущая инструкция переходит к концу результатов, возвращаемому функцией fn_get_sql для заданной sql_handle.
request_id int Идентификатор запроса. Применяется для идентификаций запросов, выполняемых в текущем сеансе.

 

Комментарии  
+4 #1 Rafael 24.09.2020 10:09
Спасибо, пригодилось! 8)

You have no rights to post comments