Structure Change Log

Overview

Who dropped that index? SQL Server does not keep the answer. The default trace holds a few object events for a few days on a busy instance and then rolls away, and the transaction log is not a readable audit trail.

The Structure Change Log is a DDL audit you switch on and leave running. A database level trigger in each database you choose writes every schema change into one table in the history database, and this page reads it back.

The point of the page is the Statement column. Not the index was dropped but the exact text of the statement, the login that ran it, the machine it came from and the application it came through.

The Structure Change Log
Every recorded schema change, newest first, with the statement that made it.

Where to find it

Two scopes, one page.

Route What you get
Server tree → Instance Level Reports → Structure Change Log Every recorded change on the instance.
Database node → Historic → Structure Change Log Only changes in that database.

The page title reads Structure Change Log for <server name> at instance scope and Structure Change Log for <database name> at database scope.

Requirements

  • A historic database configured for this instance. The page says so and stops without one.
  • The audit objects installed in DBHealthHistory, which the setup button below relies on.
  • A DDL trigger in each database you want audited. Nothing is recorded for a database that does not have one, and the page cannot tell the difference between a quiet database and an unaudited one.
  • Permission to create the trigger, for the setup step. db_owner on each database, or sysadmin.

Turning it on

Setup Structure Tracking, top left, opens a list of every online user database on the instance with a checkbox beside each one. A checkbox that is already ticked means that database has the audit trigger.

Tick the databases you want audited, untick the ones you do not, and press the button to apply. Ticked databases get the trigger created, unticked databases have it removed. master, model, msdb and tempdb are not offered.

This is the one place in the product that installs something into your user databases, so it is worth knowing exactly what it is: one database level DDL trigger named trg_DDLAudit per database, which writes a row into the history database each time a DDL statement runs.

The trigger records from the moment it exists and not before. Turning it on today gives you an audit trail from today. There is no back-fill, because there is nothing to back-fill from.

Reading the grid

Column What it is
Audit ID The row’s identity in the audit table.
Event Time When the change happened.
Event Type The DDL event, for example ALTER_TABLE, CREATE_INDEX, DROP_PROCEDURE.
Server Name Which instance.
Database Name Which database.
Schema Name The object’s schema.
Object Name The object that changed.
Object Type What kind of object it was.
Statement The text of the statement that ran.
Login Name The login that ran it.
User Name The database user it mapped to.
Host Name The machine it came from.
Application Name The application it came through.

Rows are ordered by Event Time, newest first.

Double-click a row to open the Statement in a window you can read and copy. The grid column holds one line; a real ALTER TABLE is usually more than that.

The structure change log grid
Login Name, Host Name and Application Name together are usually enough to identify who.

The last three columns are the answer

Login Name, Host Name and Application Name are what turn an audit row into a name.

  • Application Name of Microsoft SQL Server Management Studio with a personal login and a workstation host name is a person, at a keyboard, at that time.
  • Application Name of SQLAgent - TSQL JobStep is a job. Job History will say which one.
  • An application’s own name with a service account login is a deployment or an ORM. Whether that is expected is the question worth asking.

Host Name and Application Name are supplied by the client and can be set to anything, so they are strong evidence rather than proof. Login Name comes from the connection and is the reliable one.

How to read the report

  1. Sort by Event Time and read the most recent changes. Most investigations start with what changed recently.
  2. Read the Application Name column before the Login Name. Person, job or application is the first fork in every one of these investigations.
  3. Double-click the statement. The summary columns say what kind of change; only the statement says what the change was.
  4. Use the database scope when you already know where. Instance scope is for something changed somewhere.
  5. Check whether the database is audited at all before concluding nothing happened. An empty page for an unaudited database looks exactly like an empty page for a quiet one.

Common patterns

A burst of changes from one login at one time. A deployment. Compare the timestamps against your release record.

Repeated CREATE_INDEX and DROP_INDEX on the same object. Either an index maintenance script doing it the expensive way, or two people disagreeing. Duplicate Indexes and Unused Indexes usually show the result.

Changes from Management Studio on a production instance. Worth a conversation regardless of what the change was.

Changes with an application name you do not recognise. The starting point for an access review. Security Posture and Orphan Users are the follow-ups.

Nothing since a particular date. Either genuinely nothing changed, or the trigger was dropped. A DROP_TRIGGER row on that date makes that clear, and its absence usually means the database was restored over, which replaces the whole database including the trigger.

What it costs

The trigger fires on DDL only, so it costs nothing on a normal workload of selects, inserts, updates and deletes. DDL is rare on most databases, and one insert per DDL statement is not a measurable overhead.

Two things are worth knowing anyway.

  • A failure inside a DDL trigger rolls back the statement that fired it. If the history database becomes unreachable, DDL against an audited database can fail. That is the trade a DDL audit makes, and it is why the databases you audit should be a decision rather than a habit.
  • A restore replaces the trigger. Restoring a database over an audited one brings the source database’s triggers with it. Re-check the setup dialog after any restore.

Where the data comes from

  • [DBHealthHistory].[Audit].[DDLAuditLog], written by the trg_DDLAudit database trigger.

At database scope the same query is filtered to that database name. At instance scope it is not filtered at all, so the page shows every database whose trigger writes into that history database.

The audit table also stores the full event XML SQL Server hands the trigger. The page does not show it, because the columns it shows are extracted from it already and the raw XML is not readable in a grid.

This page writes nothing. The setup dialog does: it creates and drops the triggers you ask it to.

Report Why you would go there
What Changed The broader view of what has changed on the instance, including things no DDL trigger sees.
Schema Search Finding the object before you look up what happened to it.
Job History Which job made the change, when the application name says SQL Agent.
Security Posture Who can make changes at all.
Duplicate Indexes and Unused Indexes The state left behind by repeated index changes.
Error Log What else the instance logged around the same time.

Frequently asked questions

The page is empty. Either no DDL has happened since tracking was turned on, or that database has no trigger. Open Setup Structure Tracking and see whether it is ticked.

Does this record data changes? No. It records schema changes only. Inserts, updates and deletes are not DDL and are not recorded.

Can I get the history from before I switched it on? No. The trigger records from the moment it exists.

What exactly gets installed in my database? One database level DDL trigger named trg_DDLAudit. Unticking the database in the setup dialog removes it.

Is Host Name reliable? It is supplied by the client and can be set to anything. Login Name is the column to trust.

Why did tracking stop on a database? Most often the database was restored over, which replaces the trigger along with everything else. Re-tick it in the setup dialog.

What is the difference between the instance and database versions? Only the filter. Instance scope shows every database; database scope shows one.

Can DDL fail because of this? Yes, in the case where the trigger cannot write its row. A DDL trigger that errors rolls back the statement that fired it. That is inherent to auditing DDL with a trigger.