Year: 2005

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 […]

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) ; […]

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 […]