--Listing 4. spProcessTrace
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spProcessTraceFile' and type = 'P')
DROP PROC spProcessTraceFile
GO
-------------------------------------------------------------------------------------------------------
-- Name spProcessTraceFile
-- Description Aggregates data from a SQL Server trace file and sends reports with top consumers and
-- long-runners by email
-- Input @ServerName - server name a trace file was created for
-- @ReportDate (optional) - date of the trace file (default - current)
-- @TraceFilePath - path to the trace file
-- @recipients - list of email recipient divided by [;]
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spProcessTraceFile
@ServerName sysname,
@ReportDate varchar(20) = null,
@TraceFilePath varchar(1000) = '\\Process02\D$\Program Files\Microsoft SQL Server\MSSQL\LOG\',
@recipients varchar(4000) = 'Manager1@company.com;Manager2@company.com'
AS
SET NOCOUNT ON
DECLARE @TabName sysname
DECLARE @Sqlstr varchar(8000)
declare @m varchar(8000)
declare @s varchar(8000)
declare @q varchar(8000)
declare @ReportDateShort varchar(20)
SET @ReportDate = CASE WHEN @ReportDate is null
THEN CONVERT(varchar(20),getdate(),107)
ELSE CONVERT(varchar(20),CONVERT(datetime,@ReportDate),107)
END
SET @ReportDateShort = REPLACE(LEFT(@ReportDate,6),' ','')
SET @TabName = 'Trace_' + @ServerName + @ReportDateShort
SET @Sqlstr = '
SELECT EventClass, TextData, DatabaseId, SPID, Duration, StartTime, Reads, Writes, CPU
INTO '+@TabName+' FROM ::fn_trace_gettable('''+@TraceFilePath+@TabName+'.trc'', default)'
EXEC (@Sqlstr)
IF @@ERROR = 0
PRINT 'Loading the trace file into a table succeeded'
-- CPU consumers
SET @Sqlstr = '
SELECT *
into '+@TabName+'_CPU
FROM (
select dbo.fnExtractSPNameFromTextData(TextData) ''SP'', COUNT(*) ''TimesExecuted'',
SUM(CPU) ''TotalCPU'', MIN(CPU) ''MinCPU'', MAX(CPU) ''MaxCPU''
from '+@TabName+'
WHERE DATALENGTH(TextData) > 0
AND EventClass in (10,12)
GROUP BY dbo.fnExtractSPNameFromTextData(TextData)) t
WHERE SP not in (''--'',''COMMIT'',''select'',''insert'',''delete'',''update'',''trace'',''set'',
''use'',''if'',''@retcode'')
ORDER BY 3 DESC'
EXEC (@Sqlstr)
IF @@ERROR = 0
PRINT 'Aggregation for CPU has been succeeded'
-- Sending top CPU consumers by e-mail
set @m = 'Top Overlord.dev CPU consumers for '+@ReportDate+':'+CHAR(13)+CHAR(10)
set @s = 'Top Overlord.dev CPU consumers for '+@ReportDate
set @q = 'select LEFT(SP,40) ''SP'',
TimesExecuted,
TotalCPU,
TotalCPU/TimesExecuted ''AverageExecTime'',
MinCPU,
MaxCPU
from dbo.'+@TabName+'_CPU
WHERE LEFT(SP,1) like ''[a-z]''
AND TimesExecuted > 1
ORDER BY TotalCPU DESC'
exec master.dbo.xp_sendmail @recipients =@recipients,
@message=@m,
@query =@q,
@subject = @s,
@width = 200,
@dbuse='Traces'
-- Long-runners
SET @Sqlstr = '
SELECT *
into '+@TabName+'_Duration
FROM (
select dbo.fnExtractSPNameFromTextData(TextData) ''SP'', COUNT(*) ''TimesExecuted'',
SUM(Duration) ''TotalDuration'', MIN(Duration) ''MinDuration'', MAX(Duration) ''MaxDuration''
from '+@TabName+'
WHERE DATALENGTH(TextData) > 0
AND EventClass in (10,12)
GROUP BY dbo.fnExtractSPNameFromTextData(TextData)) t
WHERE SP not in (''--'',''COMMIT'',''select'',''insert'',''delete'',''update'',''trace'',''set'',
''use'',''if'',''@retcode'')
ORDER BY 3 DESC'
EXEC (@Sqlstr)
-- Sending top Long-Runners result by e-mail
set @m = 'Top Overlord.dev Long-Runners for '+@ReportDate+':'+CHAR(13)+CHAR(10)
set @s = 'Top Overlord.dev Long-Runners for '+@ReportDate
set @q = 'select LEFT(SP,40) ''SP'',
TimesExecuted,
TotalDuration,
TotalDuration/TimesExecuted ''AverageExecTime'',
MinDuration,
MaxDuration
from dbo.'+@TabName+'_Duration
WHERE LEFT(SP,1) like ''[a-z]''
AND TimesExecuted > 1
ORDER BY TotalDuration DESC'
exec master.dbo.xp_sendmail @recipients =@recipients,
@message=@m,
@query =@q,
@subject = @s,
@width = 200,
@dbuse='Traces'
GO
--Listing 5. fnExtractSPNameFromTextData
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fnExtractSPNameFromTextData' and type = 'FN')
DROP FUNCTION fnExtractSPNameFromTextData
GO
---------------------------------------------------------------------------------
-- Name fnExtractSPNameFromTextData
-- Description Extracts SP name from profiler's textdata
-- Input @TextData
-- Output SP name
-- Created By Viktor Gorodnichenko
---------------------------------------------------------------------------------
CREATE FUNCTION fnExtractSPNameFromTextData
(@TextData nvarchar(4000))
RETURNS varchar(128)
AS
BEGIN
DECLARE @Name varchar(128)
IF CHARINDEX('sp_execute',@TextData) > 0
BEGIN
SET @Name = 'sp_execute'
GOTO exit_fn
END
IF CHARINDEX('exec',@TextData) > 0
SET @TextData = RIGHT(@TextData,LEN(@TextData)-CHARINDEX('exec',@TextData)+1)
SET @TextData = LTRIM(REPLACE(@TextData,'execute ',''))
SET @TextData = LTRIM(REPLACE(@TextData,'exec ',''))
IF CHARINDEX(' ',@TextData) > 0
BEGIN
IF LEFT(@TextData,1) = '@'
BEGIN
SET @TextData = RIGHT(@TextData,LEN(@TextData)-CHARINDEX('=',@TextData)-1)
END
SET @Name = CASE WHEN CHARINDEX(' ',@TextData) > 0
THEN LEFT(@TextData,CHARINDEX(' ',@TextData)-1)
ELSE @TextData
END
END
ELSE
SET @Name = @TextData
SET @Name = CASE WHEN CHARINDEX(CHAR(9),@Name) > 0
THEN LEFT(@Name,CHARINDEX(CHAR(9),@Name)-1)
ELSE @Name
END
IF CHARINDEX('.',@Name)>0
SET @Name = REVERSE(LEFT(REVERSE(@Name),CHARINDEX('.',REVERSE(@Name))-1))
exit_fn:
RETURN @Name
END
--Listing 6. SpActivityGraph
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spActivityGraph' and type = 'P')
DROP PROC spActivityGraph
GO
-------------------------------------------------------------------------------------------------------
-- Name spActivityGraph
-- Description Builds a graph of code activities. Uses data from a trace table
-- Input @TraceTable - name of the trace table
-- @TraceStart/@TraceEnd - trace period to cover in the graph
-- @SPNameLayoutLen = 20 - size of the column "name" in the report
-- @DurationLayoutLen = 6 - size of the column "duration" in the report
-- @LayoutWidth int = 115 - width of the report
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spActivityGraph
@TraceTable varchar(128),
@TraceStart varchar(40),
@TraceEnd varchar(40),
@SPNameLayoutLen int = 20,
@DurationLayoutLen int = 6,
@LayoutWidth int = 115
AS
DECLARE @GraphStart datetime
DECLARE @GraphEnd datetime
DECLARE @TimeToMark datetime
DECLARE @Coeff numeric(7,3)
DECLARE @strCoeff varchar(20)
DECLARE @StartTime datetime
DECLARE @Duration int
DECLARE @TextData varchar(8000)
DECLARE @sqlstr nvarchar(4000)
SET @sqlstr = N'SELECT @GraphStart = MIN(StartTime), @GraphEnd = MAX(StartTime) from '+@TraceTable+'
WHERE StartTime > '''+@TraceStart+''' and StartTime < '''+@TraceEnd+'''
and TextData not like ''%sp_getactiveprocesses%''
and EventClass in (10,12)
and Duration > 5000'
EXEC sp_executesql @sqlstr, N'@GraphStart datetime OUTPUT, @GraphEnd datetime OUTPUT',
@GraphStart = @GraphStart OUTPUT, @GraphEnd = @GraphEnd OUTPUT
IF @GraphStart is null
BEGIN
PRINT 'No data for the period'
RETURN
END
SET @Coeff = CONVERT(numeric(10,2),@LayoutWidth) / CASE WHEN DATEDIFF(ss, @GraphStart, @GraphEnd)>0
THEN CONVERT(numeric(10,2),DATEDIFF(ss, @GraphStart, @GraphEnd)) ELSE 1800 END -- 1800 sec in 30 min
-- The smaller @Coeff the less number of dashes will be used to represent a long-runner.
Fo ex, 0.2 means that a 5 sec process will get 1 dash.
SET @strCoeff = STR(@Coeff,7,3)
EXEC ('DECLARE SPIDs CURSOR FOR
select StartTime, Duration, CONVERT(varchar(8000),TextData) from '+@TraceTable+'
WHERE StartTime > '''+@TraceStart+''' and StartTime < '''+@TraceEnd+'''
and TextData not like ''%sp_getactiveprocesses%''
and EventClass in (10,12)
and (Duration/1000)*'+@strCoeff+' > 1
order by StartTime')
-- Printing time scale
PRINT 'StartTime Duration Text'+
SPACE(12)+LEFT(CONVERT(varchar(10),@GraphStart,108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),DATEADD(ss,DATEDIFF(ss,@GraphStart,@GraphEnd)/3,@GraphStart),108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),DATEADD(ss,DATEDIFF(ss,@GraphStart,@GraphEnd)*2/3,@GraphStart),108),5)+
SPACE(31)+LEFT(CONVERT(varchar(10),@GraphEnd,108),5)
IF @TimeToMark is not null
PRINT REPLICATE(' ',ROUND(DATEDIFF(ss,@GraphStart,@TimeToMark)*@Coeff,0))+'*'
OPEN SPIDs
FETCH NEXT FROM SPIDs INTO @StartTime, @Duration, @TextData
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT RIGHT(CONVERT(varchar(10),@StartTime,108),8) + ' ' + STR(@Duration,@DurationLayoutLen) + ' ' +
LEFT(dbo.fnExtractSPNameFromTextData(@TextData),@SPNameLayoutLen)+
REPLICATE(' ',@SPNameLayoutLen-LEN(LEFT(dbo.fnExtractSPNameFromTextData(@TextData),@SPNameLayoutLen)))+
REPLICATE(' ',ROUND(DATEDIFF(ss,@GraphStart,@StartTime)*@Coeff,0))+
REPLICATE('-',ROUND((@Duration/1000)*@Coeff,0))
FETCH NEXT FROM SPIDs INTO @StartTime, @Duration, @TextData
END
DEALLOCATE SPIDs
GO
--Listing 7. spShowActivityGraphByChunks
USE Traces
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spShowActivityGraphByChunks' and type = 'P')
DROP PROC spShowActivityGraphByChunks
GO
-------------------------------------------------------------------------------------------------------
-- Name spShowActivityGraphByChunks
-- Description Shows activity graphs by small portions
-- Input @ServerName - name of the production server the trace was collected for
-- @ReportDate - date of the trace
-- @StartTime/@EndTime - trace period to cover
-- @ChunkSize - size in minutes of a single graph
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spShowActivityGraphByChunks
@ServerName sysname,
@ReportDate datetime,
@StartTime varchar(7) = '8:30AM',
@EndTime varchar(7) = '5:30PM',
@ChunkSize int = 30
AS
DECLARE @TraceStart datetime
DECLARE @TraceEnd datetime
DECLARE @ReportDateShort varchar(20)
DECLARE @SqlStr varchar(2000)
SET @ReportDateShort = REPLACE(LEFT(CONVERT(varchar(20),@ReportDate,107),6),' ','')
SET @TraceStart = CONVERT(varchar(20),@ReportDate,107)+' '+@StartTime
WHILE @TraceStart <= CONVERT(varchar(20),@ReportDate,107)+' '+@EndTime
BEGIN
SET @TraceEnd = DATEADD(mi,@ChunkSize,@TraceStart)
SET @SqlStr = 'spActivityGraph Trace_'+@ServerName+@ReportDateShort+ ', '''+
CONVERT(varchar(40),@TraceStart)+''', '''+CONVERT(varchar(30),@TraceEnd)+''''
EXEC (@SqlStr)
SET @TraceStart = DATEADD(mi,60,@TraceStart)
END
--------------------
http://www.sqlservercentral.com/columnists/vgorod/monitoringperformance_scripts.txt
donderdag 20 november 2008
spTraceBuild
--Listing 3. spTraceBuild
USE msdb
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spTraceBuild' and type = 'P')
DROP PROC spTraceBuild
GO
-------------------------------------------------------------------------------------------------------
-- Name spTraceBuild
-- Description Starts a SQL Server trace. The code based on sp build_trace from the Microsoft
-- Knowledge Base Article Q283790.
-- Input From 'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
-- Modified By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spTraceBuild
@traceini nvarchar (245) = N'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
as
SET NOCOUNT ON
declare @traceid int, @options int, @tracefile nvarchar (245), @maxfilesize bigint
, @stoptime datetime, @minMBfree bigint, @rc int, @on bit, @cmd1 nvarchar(512)
, @events varchar(512), @columns varchar(512), @event int, @column int, @estart int, @enext int
, @cstart int, @cnext int, @le int, @lc int, @filter nvarchar(245), @filter_num int
create table #t1 ([c1] nvarchar(512))
set @cmd1 = 'bulk insert #t1 FROM '''
--select @cmd1 + @traceini
exec (@cmd1 + @traceini + '''')
select @tracefile = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1 where left(c1,3) = '@tr'
select @maxfilesize = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as bigint)
from #t1 where left(c1,3) = '@ma'
select @stoptime = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as datetime)
from #t1 where left(c1,3) = '@st'
SET @stoptime = CONVERT(datetime, CONVERT(varchar(20),getdate(),107) + ' ' + @stoptime)
select @options = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as int)
from #t1 where left(c1,3) = '@op'
select @events=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@ev'
select @columns=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@co'
set @on = 1
set @traceid = 0
select @tracefile = @tracefile + '_' + @@SERVERNAME + REPLACE(LEFT(CONVERT(varchar(20),getdate(),107),6),' ','')
--select @tracefile
SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefile
if @traceid != 0 goto finish
set @cmd1 = 'if exist "' + @tracefile + '.trc" ' + 'del "' + @tracefile + '*.trc"'
exec @rc = master.dbo.xp_cmdshell @cmd1, no_output
exec @rc = sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptime
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_create failed. Unknown error.'
WHEN @rc = 10 THEN 'sp_trace_create failed. Invalid options. Options specified are incompatible.'
WHEN @rc = 12 THEN 'sp_trace_create failed. File not created.'
WHEN @rc = 13 THEN 'sp_trace_create failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_create failed. Invalid stop time. The stop time specified has already happened.'
WHEN @rc = 15 THEN 'sp_trace_create failed. Invalid parameters. Supplied incompatible parameters.'
END
RETURN
END
select @estart = 1
select @enext = charindex(',',@events,@estart)
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
set @le = len(@events)
set @lc = len(@columns)
while @enext > 0
begin
select @event = cast(substring(@events,@estart,@enext-@estart) as int)
while @cnext > 0
begin
select @column = cast(substring(@columns,@cstart,@cnext-@cstart) as int)
exec @rc = sp_trace_setevent @traceid, @event, @column, @on
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_setevent failed. Unknown error.'
WHEN @rc = 2 THEN 'sp_trace_setevent failed. The trace is currently running.'
WHEN @rc = 3 THEN 'sp_trace_setevent failed. The specified Event is not valid. The Event may not exist or it
is not an appropriate one for the store procedure.'
WHEN @rc = 4 THEN 'sp_trace_setevent failed. The specified Column is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setevent failed. The specified Trace Handle is not valid.'
WHEN @rc = 11 THEN 'sp_trace_setevent failed. The specified Column is used internally and cannot be removed. '
WHEN @rc = 13 THEN 'sp_trace_setevent failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_setevent failed. The function is not valid for this trace.'
END
RETURN
END
select @cstart = @cnext + 1
select @cnext = charindex(',',@columns,@cstart)
if @cnext = 0 set @cnext = @lc + 1
if @cstart >@lc set @cnext = 0
end
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
select @estart = @enext + 1
select @enext = charindex(',',@events,@estart)
if @enext = 0 set @enext = @le + 1
if @estart > @le set @enext = 0
end
set @cmd1 = 'exec sp_trace_setfilter '
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter1'
set @filter_num = 1
while @filter != N'none'
begin
exec (@cmd1 + @traceid + ','+@filter)
set @filter_num = @filter_num + 1
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter'
+ cast(@filter_num as nvarchar(3))
--select @filter
end
finish:
drop table #t1
exec @rc = sp_trace_setstatus @traceid, 1
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_setstatus. Unknown error.'
WHEN @rc = 8 THEN 'sp_trace_setstatus. The specified Status is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setstatus. The specified Trace Handle is not valid.'
WHEN @rc = 13 THEN 'sp_trace_setstatus. Out of memory. Returned when there is not enough memory to perform the specified action.'
END
RETURN
END
SELECT 'Trace '+LTRIM(STR(@traceid)) + ' has been lanched. The trace details:'
SELECT * FROM ::fn_trace_getinfo (@traceid)
GO
exec spTraceBuild
USE msdb
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spTraceBuild' and type = 'P')
DROP PROC spTraceBuild
GO
-------------------------------------------------------------------------------------------------------
-- Name spTraceBuild
-- Description Starts a SQL Server trace. The code based on sp build_trace from the Microsoft
-- Knowledge Base Article Q283790.
-- Input From 'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
-- Modified By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spTraceBuild
@traceini nvarchar (245) = N'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
as
SET NOCOUNT ON
declare @traceid int, @options int, @tracefile nvarchar (245), @maxfilesize bigint
, @stoptime datetime, @minMBfree bigint, @rc int, @on bit, @cmd1 nvarchar(512)
, @events varchar(512), @columns varchar(512), @event int, @column int, @estart int, @enext int
, @cstart int, @cnext int, @le int, @lc int, @filter nvarchar(245), @filter_num int
create table #t1 ([c1] nvarchar(512))
set @cmd1 = 'bulk insert #t1 FROM '''
--select @cmd1 + @traceini
exec (@cmd1 + @traceini + '''')
select @tracefile = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1 where left(c1,3) = '@tr'
select @maxfilesize = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as bigint)
from #t1 where left(c1,3) = '@ma'
select @stoptime = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as datetime)
from #t1 where left(c1,3) = '@st'
SET @stoptime = CONVERT(datetime, CONVERT(varchar(20),getdate(),107) + ' ' + @stoptime)
select @options = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as int)
from #t1 where left(c1,3) = '@op'
select @events=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@ev'
select @columns=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512))
from #t1 where left(c1,3) = N'@co'
set @on = 1
set @traceid = 0
select @tracefile = @tracefile + '_' + @@SERVERNAME + REPLACE(LEFT(CONVERT(varchar(20),getdate(),107),6),' ','')
--select @tracefile
SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefile
if @traceid != 0 goto finish
set @cmd1 = 'if exist "' + @tracefile + '.trc" ' + 'del "' + @tracefile + '*.trc"'
exec @rc = master.dbo.xp_cmdshell @cmd1, no_output
exec @rc = sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptime
IF @rc <> 0
BEGIN
CASE
WHEN @rc = 1 THEN 'sp_trace_create failed. Unknown error.'
WHEN @rc = 10 THEN 'sp_trace_create failed. Invalid options. Options specified are incompatible.'
WHEN @rc = 12 THEN 'sp_trace_create failed. File not created.'
WHEN @rc = 13 THEN 'sp_trace_create failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_create failed. Invalid stop time. The stop time specified has already happened.'
WHEN @rc = 15 THEN 'sp_trace_create failed. Invalid parameters. Supplied incompatible parameters.'
END
RETURN
END
select @estart = 1
select @enext = charindex(',',@events,@estart)
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
set @le = len(@events)
set @lc = len(@columns)
while @enext > 0
begin
select @event = cast(substring(@events,@estart,@enext-@estart) as int)
while @cnext > 0
begin
select @column = cast(substring(@columns,@cstart,@cnext-@cstart) as int)
exec @rc = sp_trace_setevent @traceid, @event, @column, @on
IF @rc <> 0
BEGIN
CASE
WHEN @rc = 1 THEN 'sp_trace_setevent failed. Unknown error.'
WHEN @rc = 2 THEN 'sp_trace_setevent failed. The trace is currently running.'
WHEN @rc = 3 THEN 'sp_trace_setevent failed. The specified Event is not valid. The Event may not exist or it
is not an appropriate one for the store procedure.'
WHEN @rc = 4 THEN 'sp_trace_setevent failed. The specified Column is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setevent failed. The specified Trace Handle is not valid.'
WHEN @rc = 11 THEN 'sp_trace_setevent failed. The specified Column is used internally and cannot be removed. '
WHEN @rc = 13 THEN 'sp_trace_setevent failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_setevent failed. The function is not valid for this trace.'
END
RETURN
END
select @cstart = @cnext + 1
select @cnext = charindex(',',@columns,@cstart)
if @cnext = 0 set @cnext = @lc + 1
if @cstart >@lc set @cnext = 0
end
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
select @estart = @enext + 1
select @enext = charindex(',',@events,@estart)
if @enext = 0 set @enext = @le + 1
if @estart > @le set @enext = 0
end
set @cmd1 = 'exec sp_trace_setfilter '
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter1'
set @filter_num = 1
while @filter != N'none'
begin
exec (@cmd1 + @traceid + ','+@filter)
set @filter_num = @filter_num + 1
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1
where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245))= N'@filter'
+ cast(@filter_num as nvarchar(3))
--select @filter
end
finish:
drop table #t1
exec @rc = sp_trace_setstatus @traceid, 1
IF @rc <> 0
BEGIN
CASE
WHEN @rc = 1 THEN 'sp_trace_setstatus. Unknown error.'
WHEN @rc = 8 THEN 'sp_trace_setstatus. The specified Status is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setstatus. The specified Trace Handle is not valid.'
WHEN @rc = 13 THEN 'sp_trace_setstatus. Out of memory. Returned when there is not enough memory to perform the specified action.'
END
RETURN
END
SELECT 'Trace '+LTRIM(STR(@traceid)) + ' has been lanched. The trace details:'
SELECT * FROM ::fn_trace_getinfo (@traceid)
GO
exec spTraceBuild
sp_BlockedProcesses
--Listing 2. sp_BlockedProcesses
USE [master]
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_BlockedProcesses' and type = 'P')
DROP PROC sp_BlockedProcesses
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_BlockedProcesses
-- Description Returns list of blocked processes and buffers for blocked and blocking processes
-- Input None
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC sp_BlockedProcesses
AS
SET NOCOUNT ON
DECLARE @Blocked int
DECLARE @BlockedBy int
DECLARE @SqlStr varchar(1000)
CREATE TABLE #TmpSysprocesses
(BlockedSPID smallint,
BlockedBuffer nvarchar(255) null,
BlockingSPID smallint,
BlockingBuffer nvarchar(255) null,
waitresource nchar(256),
dbid smallint,
BlockedHostname nchar(128),
BlockedProgram_name nchar(128),
BlockedCmd nchar(16),
BlockedLoginame nchar(128),
BlockingHostname nchar(128),
BlockingProgram_name nchar(128),
BlockingCmd nchar(16),
BlockingLoginame nchar(128))
INSERT INTO #TmpSysprocesses
SELECT blocked.spid 'BlockedSPID', null 'BlockedBuffer', blocked.blocked 'BlockingSPID',
null 'BlockingBuffer', blocked.waitresource, blocked.dbid,
blocked.hostname 'BlockedHostname', blocked.program_name 'BlockedProgram_name',
blocked.cmd 'BlockedCmd', blocked.loginame 'BlockedLoginame',
Blocking.hostname 'BlockingHostname', Blocking.program_name 'BlockingProgram_name',
Blocking.cmd 'BlockingCmd', Blocking.loginame 'BlockingLoginame'
FROM master..sysprocesses blocked
JOIN master..sysprocesses blocking ON blocking.SPID = blocked.blocked
WHERE blocked.Blocked > 0
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE Processes CURSOR FOR
SELECT BlockedSPID, BlockingSPID FROM #TmpSysprocesses
OPEN Processes
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@Blocked))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockedBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@BlockedBy))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockingBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
END
SELECT * FROM #TmpSysprocesses
exec sp_BlockedProcesses
USE [master]
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_BlockedProcesses' and type = 'P')
DROP PROC sp_BlockedProcesses
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_BlockedProcesses
-- Description Returns list of blocked processes and buffers for blocked and blocking processes
-- Input None
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC sp_BlockedProcesses
AS
SET NOCOUNT ON
DECLARE @Blocked int
DECLARE @BlockedBy int
DECLARE @SqlStr varchar(1000)
CREATE TABLE #TmpSysprocesses
(BlockedSPID smallint,
BlockedBuffer nvarchar(255) null,
BlockingSPID smallint,
BlockingBuffer nvarchar(255) null,
waitresource nchar(256),
dbid smallint,
BlockedHostname nchar(128),
BlockedProgram_name nchar(128),
BlockedCmd nchar(16),
BlockedLoginame nchar(128),
BlockingHostname nchar(128),
BlockingProgram_name nchar(128),
BlockingCmd nchar(16),
BlockingLoginame nchar(128))
INSERT INTO #TmpSysprocesses
SELECT blocked.spid 'BlockedSPID', null 'BlockedBuffer', blocked.blocked 'BlockingSPID',
null 'BlockingBuffer', blocked.waitresource, blocked.dbid,
blocked.hostname 'BlockedHostname', blocked.program_name 'BlockedProgram_name',
blocked.cmd 'BlockedCmd', blocked.loginame 'BlockedLoginame',
Blocking.hostname 'BlockingHostname', Blocking.program_name 'BlockingProgram_name',
Blocking.cmd 'BlockingCmd', Blocking.loginame 'BlockingLoginame'
FROM master..sysprocesses blocked
JOIN master..sysprocesses blocking ON blocking.SPID = blocked.blocked
WHERE blocked.Blocked > 0
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE Processes CURSOR FOR
SELECT BlockedSPID, BlockingSPID FROM #TmpSysprocesses
OPEN Processes
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@Blocked))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockedBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@BlockedBy))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET BlockingBuffer = EventInfo
FROM #tmpBuffer
WHERE BlockedSPID = @Blocked and BlockingSPID = @BlockedBy
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM Processes INTO @Blocked, @BlockedBy
END
SELECT * FROM #TmpSysprocesses
exec sp_BlockedProcesses
Sp_ActiveProcesses
--Listing 1. Sp_ActiveProcesses.
USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_ActiveProcesses' and type = 'P')
DROP PROC sp_ActiveProcesses
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_ActiveProcesses
-- Description Returns list of active processes and their buffer contents (what they execute)
-- A process is considered as active if it has some changes of cpu time consumed or number
-- of io operation in specified period.
-- Input (optional) @Delay - Time interval to catch activity
-- Output Result set with active processes
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC sp_ActiveProcesses
@Delay smallint = 5
AS
SET NOCOUNT ON
IF @Delay > 59
SET @Delay = 59
IF @Delay < 1
SET @Delay = 1
print @Delay
DECLARE @SPID int
DECLARE @DelayClock char(8)
DECLARE @SqlStr varchar(1000)
DECLARE @Internal_Value int
SET @DelayClock = '00:00:'+LTRIM(STR(@Delay))
CREATE TABLE #TmpSysprocesses
(EventTime datetime,
FragmentDuration int,
spid smallint,
cpu int,
physical_io int,
cpuUpdate int null,
physical_ioUpdate int null,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint,
dbname sysname null,
IsActive bit null,
SPIDBuffer nvarchar(255) null)
INSERT INTO #TmpSysprocesses
SELECT getdate(), null 'FragmentDuration', spid, SUM(cpu), SUM(physical_io), null, null, MAX(hostname), MAX(program_name),
MAX(loginame), MAX(dbid), null, null, null
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
WAITFOR DELAY @DelayClock
CREATE TABLE #TmpSysprocesses2
(EventTime datetime,
spid smallint,
cpu int,
physical_io int,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint)
INSERT INTO #TmpSysprocesses2
SELECT getdate(), spid, SUM(cpu), SUM(physical_io), MAX(hostname), MAX(program_name), MAX(loginame), MAX(dbid)
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
UPDATE #TmpSysprocesses SET FragmentDuration = DATEDIFF(ms,t.EventTime,t2.EventTime), IsActive = 1,
cpuUpdate = t2.cpu - t.cpu,
physical_ioUpdate = t2.physical_io - t.physical_io, dbname = d.name
FROM #TmpSysprocesses t
JOIN #TmpSysprocesses2 t2 ON t2.spid = t.spid
and t2.hostname = t.hostname
and t2.loginame = t.loginame
and (t2.cpu <> t.cpu or t2.physical_io <> t.physical_io)
JOIN sysdatabases d ON d.dbid = t.dbid
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE ActiveProcesses CURSOR FOR
SELECT spid FROM #TmpSysprocesses WHERE IsActive = 1
OPEN ActiveProcesses
FETCH NEXT FROM ActiveProcesses INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@SPID))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET SPIDBuffer = EventInfo
FROM #tmpBuffer
WHERE spid = @SPID
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM ActiveProcesses INTO @SPID
END
DEALLOCATE ActiveProcesses
CREATE TABLE #xp_msver (
[Index] int,
[Name] varchar(1000) null,
Internal_Value int null,
Character_value varchar(1000) null)
INSERT INTO #xp_msver EXEC master..xp_msver 'ProcessorCount'
SELECT @Internal_Value = Internal_Value FROM #xp_msver
WHERE [Name] = 'ProcessorCount'
SELECT spid 'ProcessId',
cpu 'TotalCPU',
cpuUpdate 'CPU_ConsumedInTheTimeFragment',
physical_io 'TotalPhysical_IO',
physical_ioUpdate 'Physical_IO_InTheTimeFragment',
LEFT(hostname,12) 'Hostname',
LEFT(program_name,30) 'ApplicationName', LEFT(loginame,30) 'NT_LoginName', dbname 'DatabaseName', SPIDBuffer
FROM #TmpSysprocesses s WHERE IsActive = 1
SELECT MAX(FragmentDuration) 'TheFragmentDuration', @Internal_Value 'NumberOfCPUs',
SUM(cpuUpdate) 'SUM CPU_ConsumedInTheTimeFragment',
SUM(physical_ioUpdate) 'SUM Physical_IO_InTheTimeFragment'
FROM #TmpSysprocesses s WHERE IsActive = 1
DROP TABLE #xp_msver
USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_ActiveProcesses' and type = 'P')
DROP PROC sp_ActiveProcesses
GO
-------------------------------------------------------------------------------------------------------
-- Name sp_ActiveProcesses
-- Description Returns list of active processes and their buffer contents (what they execute)
-- A process is considered as active if it has some changes of cpu time consumed or number
-- of io operation in specified period.
-- Input (optional) @Delay - Time interval to catch activity
-- Output Result set with active processes
-- Created By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC sp_ActiveProcesses
@Delay smallint = 5
AS
SET NOCOUNT ON
IF @Delay > 59
SET @Delay = 59
IF @Delay < 1
SET @Delay = 1
print @Delay
DECLARE @SPID int
DECLARE @DelayClock char(8)
DECLARE @SqlStr varchar(1000)
DECLARE @Internal_Value int
SET @DelayClock = '00:00:'+LTRIM(STR(@Delay))
CREATE TABLE #TmpSysprocesses
(EventTime datetime,
FragmentDuration int,
spid smallint,
cpu int,
physical_io int,
cpuUpdate int null,
physical_ioUpdate int null,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint,
dbname sysname null,
IsActive bit null,
SPIDBuffer nvarchar(255) null)
INSERT INTO #TmpSysprocesses
SELECT getdate(), null 'FragmentDuration', spid, SUM(cpu), SUM(physical_io), null, null, MAX(hostname), MAX(program_name),
MAX(loginame), MAX(dbid), null, null, null
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
WAITFOR DELAY @DelayClock
CREATE TABLE #TmpSysprocesses2
(EventTime datetime,
spid smallint,
cpu int,
physical_io int,
hostname sysname,
program_name sysname,
loginame sysname,
dbid smallint)
INSERT INTO #TmpSysprocesses2
SELECT getdate(), spid, SUM(cpu), SUM(physical_io), MAX(hostname), MAX(program_name), MAX(loginame), MAX(dbid)
FROM sysprocesses
GROUP BY spid
HAVING SUM(dbid) > 0
UPDATE #TmpSysprocesses SET FragmentDuration = DATEDIFF(ms,t.EventTime,t2.EventTime), IsActive = 1,
cpuUpdate = t2.cpu - t.cpu,
physical_ioUpdate = t2.physical_io - t.physical_io, dbname = d.name
FROM #TmpSysprocesses t
JOIN #TmpSysprocesses2 t2 ON t2.spid = t.spid
and t2.hostname = t.hostname
and t2.loginame = t.loginame
and (t2.cpu <> t.cpu or t2.physical_io <> t.physical_io)
JOIN sysdatabases d ON d.dbid = t.dbid
CREATE TABLE #tmpBuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))
DECLARE ActiveProcesses CURSOR FOR
SELECT spid FROM #TmpSysprocesses WHERE IsActive = 1
OPEN ActiveProcesses
FETCH NEXT FROM ActiveProcesses INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStr = 'DBCC INPUTBUFFER ('+LTRIM(STR(@SPID))+')'
INSERT INTO #tmpBuffer EXEC (@SqlStr)
UPDATE #TmpSysprocesses SET SPIDBuffer = EventInfo
FROM #tmpBuffer
WHERE spid = @SPID
TRUNCATE TABLE #tmpBuffer
FETCH NEXT FROM ActiveProcesses INTO @SPID
END
DEALLOCATE ActiveProcesses
CREATE TABLE #xp_msver (
[Index] int,
[Name] varchar(1000) null,
Internal_Value int null,
Character_value varchar(1000) null)
INSERT INTO #xp_msver EXEC master..xp_msver 'ProcessorCount'
SELECT @Internal_Value = Internal_Value FROM #xp_msver
WHERE [Name] = 'ProcessorCount'
SELECT spid 'ProcessId',
cpu 'TotalCPU',
cpuUpdate 'CPU_ConsumedInTheTimeFragment',
physical_io 'TotalPhysical_IO',
physical_ioUpdate 'Physical_IO_InTheTimeFragment',
LEFT(hostname,12) 'Hostname',
LEFT(program_name,30) 'ApplicationName', LEFT(loginame,30) 'NT_LoginName', dbname 'DatabaseName', SPIDBuffer
FROM #TmpSysprocesses s WHERE IsActive = 1
SELECT MAX(FragmentDuration) 'TheFragmentDuration', @Internal_Value 'NumberOfCPUs',
SUM(cpuUpdate) 'SUM CPU_ConsumedInTheTimeFragment',
SUM(physical_ioUpdate) 'SUM Physical_IO_InTheTimeFragment'
FROM #TmpSysprocesses s WHERE IsActive = 1
DROP TABLE #xp_msver
Using SSIS to monitor SQL Server Databases
Analyzing SQL Server Connections Over Time Using SSIS :
=======================================================
http://www.simple-talk.com/sql/database-administration/using-ssis-to-monitor-sql-server-databases-/
I wanted to know if there were any long-running jobs or processes in any of the eighty-odd SQL Servers that I manage. These are a sign of a problem that has to be investigated and put right. I’d developed a simple query to get the process Information from each database, but I wasn’t sure if this would interfere with its performance if it ran once every hour in a SQL Agent job: if so, was it worth it?
I soon realized that this could do much more than to isolate long running processes: I found that I could now deep dive into the data for trend analysis, capacity planning, concurrency issues, and even potential security breaches. In this article I will demonstrate the simple SSIS package that I use to collect and gather all the process information for these servers. I’ll also show you the queries that I use to analyze the hundreds of thousands of records I collected in just the first week .
Connection Collection
I’ve just said that the query to return the results that I needed was simple. Listing 1 should back me up on that. Notice, though, that even though it is simple, almost everything that you could want is returned. Sure, there are other fields that I could have used, like a “blocked_by” column, the same as the sp_who2 “blkby” column would provide. Also, it would have been useful to know the actual query that was being executed at the time, but this is not a server trace after all. To use a drummer’s cliché, “Less is more.” What I needed was a way to identify potential problems and then use other tools to get the details.
Listing 1: Simple Connections Query
SELECT GetDate() as Run_Date,
rtrim(Cast(@@ServerName as varchar(100))) as Server,
spid,
blocked,
waittime,
sb.name,
lastwaittype,
sp.cpu,
sp.login_time,
sp.last_batch,
sp.status,
sp.hostname,
sp.program_name,
sp.cmd,
sp.loginame,
getdate() - last_batch as duration
FROM master..sysprocesses sp
INNER JOIN master..sysdatabases sb ON sp.dbid = sb.dbid
More Information
http://msdn.microsoft.com/en-us/library/ms139805.aspx
=======================================================
http://www.simple-talk.com/sql/database-administration/using-ssis-to-monitor-sql-server-databases-/
I wanted to know if there were any long-running jobs or processes in any of the eighty-odd SQL Servers that I manage. These are a sign of a problem that has to be investigated and put right. I’d developed a simple query to get the process Information from each database, but I wasn’t sure if this would interfere with its performance if it ran once every hour in a SQL Agent job: if so, was it worth it?
I soon realized that this could do much more than to isolate long running processes: I found that I could now deep dive into the data for trend analysis, capacity planning, concurrency issues, and even potential security breaches. In this article I will demonstrate the simple SSIS package that I use to collect and gather all the process information for these servers. I’ll also show you the queries that I use to analyze the hundreds of thousands of records I collected in just the first week .
Connection Collection
I’ve just said that the query to return the results that I needed was simple. Listing 1 should back me up on that. Notice, though, that even though it is simple, almost everything that you could want is returned. Sure, there are other fields that I could have used, like a “blocked_by” column, the same as the sp_who2 “blkby” column would provide. Also, it would have been useful to know the actual query that was being executed at the time, but this is not a server trace after all. To use a drummer’s cliché, “Less is more.” What I needed was a way to identify potential problems and then use other tools to get the details.
Listing 1: Simple Connections Query
SELECT GetDate() as Run_Date,
rtrim(Cast(@@ServerName as varchar(100))) as Server,
spid,
blocked,
waittime,
sb.name,
lastwaittype,
sp.cpu,
sp.login_time,
sp.last_batch,
sp.status,
sp.hostname,
sp.program_name,
sp.cmd,
sp.loginame,
getdate() - last_batch as duration
FROM master..sysprocesses sp
INNER JOIN master..sysdatabases sb ON sp.dbid = sb.dbid
More Information
http://msdn.microsoft.com/en-us/library/ms139805.aspx
view database connections in sql server
view database connections in sql server:
=======================================
To view database connections in SQL Server you can query the sysprocesses table in master database. This is a SQL Server 2000 table which is included as view in 2005 for backward compatibility. The script will show total number of connections to each database including internal connections, if you want to view only external connections change the WHERE clause to add " AND spid > 51 " to view only external connections to each database.
If you are running SQL Server 2000 use this script.
SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
If you are using SQL Server 2005 edition then use sys.sysprocesses to get the same results.
SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM sys.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
=======================================
To view database connections in SQL Server you can query the sysprocesses table in master database. This is a SQL Server 2000 table which is included as view in 2005 for backward compatibility. The script will show total number of connections to each database including internal connections, if you want to view only external connections change the WHERE clause to add " AND spid > 51 " to view only external connections to each database.
If you are running SQL Server 2000 use this script.
SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
If you are using SQL Server 2005 edition then use sys.sysprocesses to get the same results.
SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM sys.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
Find Largest size Tables in a Database
IF EXISTS
(
SELECT 1 FROM master.dbo.sysobjects
WHERE name = 'sp_LargestTables' AND type = 'P'
)
DROP PROC sp_LargestTables
GO
CREATE PROC sp_LargestTables(@n int = NULL,@IsSystemAllowed bit = 0)
AS
/*=========================================================================
CREATE DATE : Hari N Sharma
CREATION DATE : 10-09-2007
LAST MODIFICATION DATE : 11-10-2007
PURPOSE : To get a list of User/System tables according to their size.
=========================================================================*/
BEGIN
SET NOCOUNT ON
DECLARE @LOW int
SELECT @LOW = LOW FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'
IF @n > 0 SET ROWCOUNT @n
SELECT TableName,[Row Count],[Size (KB)] FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS TableName,SUM(i.rowcnt) [Row Count],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * @LOW) / 1024.0))) AS [Size (KB)]
FROM sysindexes i INNER JOIN sysobjects o (NOLOCK) ON i.id = o.id AND
((@IsSystemAllowed = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) AS Z
ORDER BY [Size (KB)] DESC
SET ROWCOUNT 0
END
GO
----------------------------------------------
http://www.sqlservercentral.com/scripts/Administration/63646/
(
SELECT 1 FROM master.dbo.sysobjects
WHERE name = 'sp_LargestTables' AND type = 'P'
)
DROP PROC sp_LargestTables
GO
CREATE PROC sp_LargestTables(@n int = NULL,@IsSystemAllowed bit = 0)
AS
/*=========================================================================
CREATE DATE : Hari N Sharma
CREATION DATE : 10-09-2007
LAST MODIFICATION DATE : 11-10-2007
PURPOSE : To get a list of User/System tables according to their size.
=========================================================================*/
BEGIN
SET NOCOUNT ON
DECLARE @LOW int
SELECT @LOW = LOW FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'
IF @n > 0 SET ROWCOUNT @n
SELECT TableName,[Row Count],[Size (KB)] FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS TableName,SUM(i.rowcnt) [Row Count],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * @LOW) / 1024.0))) AS [Size (KB)]
FROM sysindexes i INNER JOIN sysobjects o (NOLOCK) ON i.id = o.id AND
((@IsSystemAllowed = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) AS Z
ORDER BY [Size (KB)] DESC
SET ROWCOUNT 0
END
GO
----------------------------------------------
http://www.sqlservercentral.com/scripts/Administration/63646/
Abonneren op:
Posts (Atom)