Usually on the first of every month I would be preparing the Inventory Report for all the databases across the Enterprise. Highly technically challenging task. I need to compare the number of databases found in the enterprise with that of the last month report! The first thing that I would work on the 1st is this report.

I had almost finished preparing it. An audit report dropped in my inbox. “A DBA Team member’s id was seen running a trace on a very critical ans a controlled server”. The statement that was caught in the audit report was something like this

select @status=status  @curr_tracefilename=path from sys.traces where is_default = 1 ;
insert into @temp_trace.......

From this it was clear that he was not “running” a trace instead reading the default trace, is_default=1 in the where clause confirms it. This information was captured exactly four hours back. But the DBA confirmed that he had logged on to the server to find out the reasons for growth of a transaction log file. He had not run this script. So how did this information got logged against his name?

Started to rewind the time machine. He had logged on to the server and had opened SSMS. He had logged on to the server and verified the free space detail on the drive hosting the transaction log file. Then he had checked the database properties and the User Statistics report for that database. Thats all and he had logged off the server.

Like I mentioned earlier, some of the Standard Reports read the default trace running on the instance. I felt that he would have accidentally clicked on one of such reports which read the default trace. Looking at the Report menu confirmed my opinion.

The report that he had viewed was “User Statistics” and there is “Schema Changes History” report just above that. What happens when one click on this report? To verify that I ran a trace while clicking on the report. Here is the snip from the trace output.

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''; 

        insert into @temp_trace
        select ObjectName
        ,       ObjectID
        ,       DatabaseName
        ,       StartTime
        ,       EventClass
        ,       EventSubClass
        ,       ObjectType
        ,       ServerName
        ,       LoginName
        ,       NTUserName
        ,       ApplicationName
        ,       ''temp''
        from ::fn_trace_gettable( @base_tracefilename, default )
        where EventClass in (46,47,164) and EventSubclass = 0  and DatabaseID = db_id() ;

I guessed it right. The first two lines from this output matched that of the one captured in the Audit Log. I have already sent a lengthy explanation to the concerned. Most likely they will buy my theory