Recent Objects – Without Triggers

I was thinking about writing a script to capture the objects that were either CREATED/DELETED or MODIFIED in the last 24hrs. Instead of directly querying the sys.objects I from where I can only get minimal information I decided to get a whole bunch of useful information using the SQL Server default trace.

By default the default trace will be running in all the SQL Servers since SQL 2005 onwards. It will capture minimal information without overloading the database. It will be placed in the same location as that of the SQL Server errorlog. The maximum file size is 20MB after which the trace will roll over into a new file. However, SQL Server can maintain only the 5 most recently created trace files. In addition a new trace file is generated whenever SQL Server is restarted.


Ive created the below stored procedure to accomplish what I wanted. In addition the procedure will give the info on who made the changes, what is the host name and when was the changes made and what was the hostname etc. Ofcourse, one might wonder why in the whole world one should read from the trace flag when there are various simple options like DDL trigger available. Also with the advent of SQL 2008 a DBAs job has been made relatively easy! ! with the excellent Auditing features. However, with triggers be it DDL or DML performance will certainly take a beating due to synchronous processing. As a result I decided to write a procedure to query the default trace to get all those information.

I am querying not only the current trace file but also the previous one so that If the Server was restarted in the last 24hrs and if someone made changes just prior to restarting then those changes will be available in the older trace files. So the below script will capture all those changes by reading both the trace files.

IF NOT EXISTS(SELECT name FROM sys.objects WHERE Name='RecentObjectInfo' AND Type='P')

BEGIN

EXEC('CREATE PROCEDURE dbo.RecentObjectInfo AS RETURN')

END 

GO


ALTER PROCEDURE dbo.RecentObjectInfo

AS

BEGIN 


DECLARE @currentfilename nvarchar(max)

DECLARE @logvalue nvarchar(1000) 

DECLARE @oldfilename nvarchar(max) 

DECLARE @oldlogId smallint 

DECLARE @endpos smallint

DECLARE @startpos smallint

DECLARE @diffpos smallint 


SELECT @currentfilename=CAST(value AS nvarchar(max)) 

FROM fn_trace_getinfo(default)

WHERE property=2 -- Get the current trace file name


SET @endpos=CHARINDEX('.trc',@currentfilename)

SET @startpos=CHARINDEX('log_',@currentfilename)

SET @diffpos=@endpos-@startpos


SET @logvalue=SUBSTRING(SUBSTRING(@currentfilename,@startpos,@diffpos),1,10)

SET @oldlogId=SUBSTRING(SUBSTRING(@currentfilename,@startpos,@diffpos),5,4) - 1 

SET @oldfilename='log_' + CAST(@oldlogId AS varchar(1000)) 

SET @oldfilename=REPLACE(@currentfilename,@logvalue,@oldfilename) 


SELECT * FROM 

(

SELECT 

DatabaseName,

ObjectName,

CASE 

WHEN EventClass=46 THEN 'OBJECT CREATED'

WHEN EventClass=47 THEN 'OBJECT DELETED'

ELSE 'OBJECT MODIFIED'

END AS EventDescription, 

NTUserName,

NTDomainName,

LoginName,

HostName,

StartTime

FROM fn_trace_gettable(@currentfilename,DEFAULT) AS Trace 

WHERE StartTime BETWEEN GETDATE()-1 AND GETDATE() AND EventClass IN (46,47,164) -- Object Created/Deleted/Altered 

AND ApplicationName NOT LIKE 'SQLAgent%' AND ApplicationName NOT LIKE '.Net%' 

AND DatabaseName NOT IN ('master','model','msdb','tempdb') AND objectname IS NOT NULL 

AND Trace.ObjectType <> '21587' -- Ignoring the statistics 


UNION 


SELECT 

DatabaseName,

ObjectName,

CASE 

WHEN EventClass=46 THEN 'OBJECT CREATED'

WHEN EventClass=47 THEN 'OBJECT DELETED'

ELSE 'OBJECT MODIFIED'

END AS EventDescription, 

NTUserName,

NTDomainName,

LoginName,

HostName,

StartTime

FROM fn_trace_gettable(@oldfilename,DEFAULT) AS Trace 

WHERE StartTime BETWEEN GETDATE()-1 AND GETDATE() AND EventClass IN (46,47,164) -- Object Created/Deleted/Altered 

AND ApplicationName NOT LIKE 'SQLAgent%' AND ApplicationName NOT LIKE '.Net%' 

AND DatabaseName NOT IN ('master','model','msdb','tempdb') AND objectname IS NOT NULL 

AND Trace.ObjectType <> '21587' -- Ignoring the statistics 

) 

AS tmp 

ORDER BY StartTime DESC 

END 


Posted

in

by

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *