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
Leave a Reply