Znalezione na stronie The SQL Guy by Norm Enger
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
USE [master]; GO BEGIN TRY 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, ServerName AS [SQL_Instance], --CONVERT(INT, EventClass) AS EventClass, DatabaseName AS [Database_Name], Filename AS [Logical_File_Name], (Duration / 1000) AS [Duration_MS], CONVERT(VARCHAR(50), StartTime, 100) AS [Start_Time], --EndTime, CAST((IntegerData * 8.0 / 1024) AS DECIMAL(19, 2)) AS [Change_In_Size_MB] FROM::fn_trace_gettable(@base_tracefilename, DEFAULT) WHERE EventClass >= 92 AND EventClass <= 95 --AND ServerName = @@SERVERNAME --AND DatabaseName = 'myDBName' ORDER BY DatabaseName, StartTime DESC; END; ELSE SELECT -1 AS l1, 0 AS EventClass, 0 DatabaseName, 0 AS Filename, 0 AS Duration, 0 AS StartTime, 0 AS EndTime, 0 AS ChangeInSize; END TRY BEGIN CATCH SELECT -100 AS l1, ERROR_NUMBER() AS EventClass, ERROR_SEVERITY() DatabaseName, ERROR_STATE() AS Filename, ERROR_MESSAGE() AS Duration, 1 AS StartTime, 1 AS EndTime, 1 AS ChangeInSize; END CATCH; |
