USE: To capture the result from executing sp_who2 system stored procedure.

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#SP_WHO2') IS NOT NULL
   DROP TABLE #SP_WHO2
GO

CREATE TABLE #SP_WHO2 (
       SPID varchar(128)
     , [Status] char(12)
     , [Login] varchar(128)
     , [HostName] varchar(128)
     , [BlkBy] varchar(128)
     , [DBName] varchar(128)
     , [Command] varchar(128)
     , [CPUTime] varchar(128)
     , [DiskIO] varchar(128)
     , LastBatch varchar(128)
     , ProgramName varchar(128)
     , SPID2 varchar(128)
     , REQUESTED varchar(128))

INSERT #SP_WHO2
EXEC SP_WHO2
GO

 

USE: To find out recent database files growth.

IF(SELECT CONVERT(int,value_in_use) FROM sys.configurations WHERE [name] = 'default trace enabled' ) = 1 
BEGIN
  declare @curr_tracefilename varchar(500) ; 
  declare @base_tracefilename varchar(500) ; 
  declare @indx int ;

  SELECT @curr_tracefilename = [path] 
    FROM sys.traces where is_default = 1 ;
    
    SET @curr_tracefilename = REVERSE(@curr_tracefilename);
    
    SELECT @indx  = PATINDEX('%%', @curr_tracefilename) ;

    SET @curr_tracefilename = REVERSE(@curr_tracefilename) ;
    SET @base_tracefilename = LEFT( @curr_tracefilename,LEN(@curr_tracefilename) - @indx) + 'log.trc' ;  

  SELECT (DENSE_RANK() OVER(ORDER BY StartTime DESC))%2 AS [l1],
         CONVERT(int, EventClass) [EventClass],
         DatabaseName,
         [Filename],
         (Duration/1000) [Duration],
         StartTime,
         EndTime,
         (IntegerData*8.0/1024) [ChangeInSize]
    FROM ::fn_trace_gettable( @base_tracefilename, default ) 
   WHERE EventClass >= 92 AND
         EventClass <= 95 AND
         ServerName = @@SERVERNAME AND
         DatabaseName = DB_NAME()  
   ORDER BY StartTime DESC;   
END
GO

 

DECLARE @TracePath nvarchar(256)

SELECT @TracePath =  [path]
  FROM master.sys.traces

SELECT *
  FROM ::fn_trace_gettable (@TracePath,default)

SELECT T.EventID
     , T.ColumnID
     , E.Name [EventDescription]
     , C.Name [ColumnDescription]
  FROM ::fn_trace_geteventinfo(1) T
 INNER JOIN sys.trace_events E ON T.EventID = E.trace_event_id
 INNER JOIN sys.trace_columns C ON T.columnid = C.trace_column_id

SELECT T.EventID
     , E.Name [EventDescription]
     , COUNT(*) [DataColumnCount]
  FROM ::fn_trace_geteventinfo(1) T
 INNER JOIN sys.trace_events E ON T.EventID = E.trace_event_id
 GROUP BY T.EventID, E.Name