SQL 記錄資料庫連線數資訊

從master.dbo.sysprocesses取連線數資訊並記錄的SQL。[@more@]

-- create connection_log
create table connection_log
(
time datetime,
date datetime,
hour int,
minute int,
second int,
dbname varchar(56),
connect_count int
)

-- catch data
set nocount on
declare @v_time datetime
while 1=1
begin
select @v_time = GETDATE()
select
@v_time as time,
convert(varchar(10), @v_time, 120) as date,
DATEPART(hour, @v_time) as hour,
DATEPART(minute, @v_time) as minute,
DATEPART(second, @v_time) as second,
DB_NAME(dbid) as datasbase,
count(*) as connect_count
from master.dbo.sysprocesses
where dbid <> 0
group by dbid

waitfor delay '00:00:01'
end