/*

Pliki śledzenia

*/

USE AdventureWorks2008;
GO
SELECT * INTO temp_trc
FROM fn_trace_gettable(' C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ log_217.trc', default);

/*

Profiler i sledzenie aktywnosci uzytkownikow

*/

SELECT TextData, Duration
FROM fn_trace_gettable(N'C:\plik.trc', NULL)
ORDER BY Duration DESC;

/*

Śledzenie aktywności serwera SQL z poziomu kodu T-SQL

*/
--utworzenie kolejki  
DECLARE @rc INT;
DECLARE @TraceID INT;
DECLARE @maxfilesize BIGINT;

set @maxfilesize = 5 
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error

--wybranie zdarzen  
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on

-- ustawienie filtrow 
Set the Filters 
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'AdventureWorks2008'
--wlaczenie sledzenia  
exec sp_trace_setstatus @TraceID, 1

-- zachowanie identyfikatora (TraceID)  
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go


sp_trace_setstatus @TraceID, 2
sp_trace_setstatus @TraceID, 0

/*

Widoki i funkcje dynamiczne

*/

SELECT name
FROM sys.system_objects
WHERE name like 'dm_%' 
ORDER BY 1;



/*

Performance Data Collection - tworzenie własnej kolekcji

*/

BEGIN TRAN;
BEGIN TRY
	DECLARE @params xml;
	DECLARE @collection_item_id int;
	DECLARE @collection_set_id int;
	DECLARE @collector_type_uid uniqueidentifier;
	EXEC [msdb].[dbo].[sp_syscollector_create_collection_set] 
		@name=N'Sesje wg loginow', 
		@collection_mode=1, 
		@description=N'Liczba sesji otwartych w serwerze SQL kazdy login', 
		@logging_level=1, 
		@days_until_expiration=7, 
		@schedule_name=N'CollectorSchedule_Every_60min', 
		@collection_set_id=@collection_set_id OUTPUT;
	SELECT @collector_type_uid = collector_type_uid 
	FROM [msdb].[dbo].[syscollector_collector_types] 
	WHERE name = N'Generic T-SQL Query Collector Type';
	SET @params = N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
        <Query>
          <Value>SELECT login_name, COUNT(*) [sessions]
                  FROM sys.dm_exec_sessions
                  WHERE session_id &gt; 50
                  GROUP BY login_name</Value>
          <OutputTable>sessions_by_logins</OutputTable>
        </Query>
      </ns:TSQLQueryCollector>';
	
	EXEC [msdb].[dbo].[sp_syscollector_create_collection_item] 
		@collection_set_id = @collection_set_id, 
		@collector_type_uid = @collector_type_uid, 
		@name = N'Sesje wg loginow',
		@frequency = 60, 
		@parameters = @params, 
		@collection_item_id = @collection_item_id OUTPUT;
	IF @@TRANCOUNT > 0
		COMMIT TRAN;
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
		ROLLBACK TRAN;
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	DECLARE @ErrorNumber INT;
	DECLARE @ErrorLine INT;
	DECLARE @ErrorProcedure NVARCHAR(200);
	SELECT 
		@ErrorLine = ERROR_LINE(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE(),
		@ErrorNumber = ERROR_NUMBER(),
		@ErrorMessage = ERROR_MESSAGE(),
		@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
	RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
END CATCH
