I’m continuing a series on the Standard Reports you can find in SQL Server Management Studio (SSMS).
The Schema Changes History report shows the changes you’ve made using Data Definition Language statements, similar to the Configuration Changes History report, in that it tracks changes in an instance using the Default Trace mechanism. I mentioned this in yesterday's post and promised I would cover this mechanism in a little more depth.
SQL Server records certain events about your system in a “Default Trace”, which is a binary file that watches SQL Server for certain events. It uses the SQL Trace mechanism, which you can read more about here. Although the Default Trace uses SQL Trace, for performance and privacy reasons it doesn't track every single event on the server. You can run this script to find out what it gets:
SELECT cat.name AS Category
, b.name AS EventCaptured
, c.name AS ColumnCaptured
FROM fn_trace_geteventinfo(1) AS a
INNER JOIN sys.trace_events AS b
ON a.eventid = b.trace_event_id
INNER JOIN sys.trace_columns AS c
ON a.columnid = c.trace_column_id
INNER JOIN sys.trace_categories AS cat
ON b.category_id = cat.category_id
ORDER BY Category, EventCaptured, ColumnCaptured
It also "rolls over", so everything you do on the server isn't persisted forever. Because of this It would be useful to save off or print this report from time to time, although there are more thorough methods you can use to track DDL changes. This report is a quick and easy method to do that, however.
By default this mechanism is always running. If it is turned off, you won’t have any information in this report. As I mentioned yesterday, you can find out if it is on with this script:
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE WITH OVERRIDE;
Look for the default trace enabled value and check to see that the run value is 1.
If it is off and you want to turn it on (check first and find out why it is off), you can use this script:
EXEC master.dbo.sp_configure 'allow updates', 1;
EXEC master.dbo.sp_configure 'show advanced options', 1;
EXEC master.dbo.sp_configure 'default trace enabled', 1;
RECONFIGURE WITH OVERRIDE;
To find this report, open SSMS, right-click a Server, and then select “Reports” and then “Standard Reports”. This report doesn’t take long to render.
Shows the name of the database where the object was changed.
Shows the name of the database object that was changed.
Shows the database object type that was operated on.
Shows whether the CREATE, ALTER or DROP action was initiated.
Shows the date and time when the DDL was implemented.
|Login Name||Shows the account that was used to make the change. This can be less useful if you allow multiple people to use the same administrative account, so it’s a best practice for each administrator to have their own account.|
If you don't have any data in it, make sure the Default Trace is running as described above and then run this script (Note - run this on a test system only!):
CREATE DATABASE Test;
CREATE TABLE TestTable (c1 int);
ALTER TABLE TestTable ADD c2 int;
DROP TABLE TestTable;
DROP DATABASE Test;
Now you'll have some data to look at in the report.
If you want more information from the Default Trace than is showing here, you can read the entire thing with this command:
the Default Trace than is showing here, you can read the entire thing with this command:
('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default)
Basically this statement reads from a function that reads from th e binary file that SQL Trace creates.
e binary file that SQL Trace creates.