Si desea obtener información de las conexiones existentes a una base de datos, por el nombre del host, por el nombre de la base de datos o el inicio de sesión.
Drop table #TMP_TABLE
CREATE TABLE #TMP_TABLE (
SPID INT,
ecid int,
STATUS VARCHAR(32),
LOGINAME VARCHAR(32),
HOSTNAME VARCHAR(32),
BLK CHAR(8),
DBNAME VARCHAR(32),
CMD VARCHAR(255),
request_id int )
INSERT INTO #TMP_TABLE EXEC sp_who
SELECT COUNT(*)
FROM #TMP_TABLE
WHERE DBNAME = (nombre de la base de datos)
AND LEN(LTRIM(RTRIM(HOSTNAME))) > 0
AND HOSTNAME <> (maquina)
SELECT *
FROM #TMP_TABLE
-- Por base de datos
SELECT dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by dbname
order by conexiones desc
SELECT COUNT(*) as total
FROM #TMP_TABLE
where dbname is not null
/*
SELECT dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null and status <> 'background'
group by dbname
order by conexiones desc
SELECT COUNT(*) as total
FROM #TMP_TABLE
where dbname is not null and status <> 'background'
*/
-- por usuarios
SELECT hostname, loginame, dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by hostname, loginame, dbname
order by hostname, loginame, dbname, conexiones
SELECT hostname, loginame, dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by hostname, loginame, dbname
order by conexiones desc, hostname, loginame, dbname
-- por maquina y base
SELECT hostname, dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by hostname, dbname
order by conexiones desc, hostname, dbname
-- por maquina
SELECT hostname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by hostname
order by conexiones desc, hostname
-- activas
SELECT *
FROM #TMP_TABLE
where dbname is not null
and status='runnable'
SELECT *
FROM #TMP_TABLE
where dbname is not null
and hostname = 'una aplicación'
