Goline Logo

News

  • New Partnership Between Goline and EaseUS: Technology and Innovation at Your Service. We are excited to announce our collaboration with EaseUS, a leading company in data management, file recovery, and disk cloning software. This collaboration allows us to provide our users with reliable and cutting-edge tools to manage and protect their devices efficiently. Exploring the Benefits of Our New Partnership with EaseUS This collaboration enhances our capability to deliver top-notch services to our community. With EaseUS, you can optimize storage space, recover lost files, and clone disks easily and securely. Whether you're a home user or an IT professional, you'll...
  • In the supply and logistics sectors, email communication is pivotal. However, organizations face threats like email fraud and phishing. GOLINE SA's clients struggled with configuring email authentication protocols manually. To address this challenge, GOLINE SA partners with PowerDMARC as an MSP Partner, collaborating to streamline implementation and management. PowerDMARC's cloud-based platform automated DMARC, SPF, and DKIM protocols for GOLINE SA's clients. This streamlined the transition to DMARC enforcement policies, bolstering domain protection without compromising email deliverability. The intuitive platform facilitated easy navigation and provided detailed reporting for proactive issue resolution. Strategic Collaboration: GOLINE SA Partners with PowerDMARC GOLINE SA's clients...

MS SQL Server: sp_processi, estensione della sp_who

Estensione della sp_who

Per lanciarla:

Exec sp_processi 'active'

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_processi] Script Date: 24/04/2015 09:47:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_processi] –'active' — 2004/04/15
@loginame sysname = NULL
as
set nocount on
declare
@retcode int
declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int
declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)
declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)
—–per campo dbcc
declare @dbcc varchar(255),
@spid_v varchar(3)
——–
select
@retcode = 0 — 0=good ,1=bad.
——–defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
select
@spidlow = 0
,@spidhigh = 32767
————————————————————–
IF (@loginame IS NULL) –Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
——–
– select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame
IF (@sid1 IS NOT NULL) –Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end
——–
IF (lower(@loginame) IN ('active')) –Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end
——–
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) –Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end
——–
RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN
LABEL_17PARM1EDITED:
——————– Capture consistent sysprocesses. ——————-
SELECT
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'
, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'
INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)
——–Screen out any rows?
IF (@loginame IN ('active'))
DELETE #tb1_sysprocesses
where lower(status) IN (
'sleeping'
,'background')
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
,'TASK MANAGER',
'RESOURCE MONITOR',
'XE TIMER ',
'XE DISPATCHER ',
'LOG WRITER ',
'LOCK MONITOR ',
'SIGNAL HANDLER ',
'TRACE QUEUE TASK',
'BRKR EVENT HNDLR',
'BRKR TASK ',
'CHECKPOINT '
)
and blocked = 0
——–Prepare to dynamically optimize column widths.
Select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)
SELECT
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)
,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)
,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
)
,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
)
,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
)
,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
)
,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
)
,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
where
spid >= @spidlow
and spid <= @spidhigh
—Aggiunta di un campo che proviene dalla dbcc inputbuffer
declare @val int
create table #dbcc (c1 varchar(4000),par int,cmd varchar(4000), spid int)
declare q cursor for
select distinct spid
from #tb1_sysprocesses
open q
fetch next from q into @val
WHILE @@FETCH_STATUS=0
BEGIN
set @spid_v = @val
insert into #dbcc (c1,par,cmd) Exec ('dbcc inputbuffer('+@spid_v+')')

update #dbcc
set spid = @val
where spid is null
FETCH NEXT FROM q INTO @val
END
CLOSE q
DEALLOCATE q
——–Output the report.
EXECUTE(
'
SET nocount off
SELECT
SPID = convert(char(5),a.spid)
,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END
,Login = substring(loginname,1,' + @charMaxLenLoginName + ')
,BlkBy =
CASE isnull(convert(char(5),blocked),''0'')
When ''0'' Then '' .''
Else isnull(convert(char(5),blocked),''0'')
END
,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
,Command = substring(a.cmd,1,' + @charMaxLenCommand + ')
,Sql = b.cmd
,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')
,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END
from
#tb1_sysprocesses a
left join #dbcc b on –Usually DB qualification is needed in exec().
a.spid = b.spid and
a.spid >= ' + @charspidlow + '
and a.spid <= ' + @charspidhigh + '
–and a.spid > 49 and b.cmd is not null
order by Login, substring(last_batch_char,1,' + @charMaxLenLastBatch + '),a.spid
– (Seems always auto sorted.) order by spid_sort
SET nocount on
'
)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
sid >= ' + @charsidlow + '
and sid <= ' + @charsidhigh + '
and
**************/
LABEL_86RETURN:
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
return @retcode — sp_processi

GO

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x
| Reply