The schema differs from the last deployment file, and nobody knows when it changed. A change log for every database gives the team a dated reason, operator, and verification for each approved modification.
Define the Record You Need
A useful entry names the database, change identifier, summary, reason, requester, operator, time, approval, and verification result. Include the script or deployment artifact identifier. Do not put passwords or private customer data in the log. The record should answer what changed and why without requiring a long search through chat history.
I ask whether an unfamiliar DBA could explain the last schema change from the record alone. If not, add the missing context. Which application release depended on it? That relationship is more useful than the file name. A log is a shared memory for the team, not a diary.
Keep a Simple Change Log Table
A table inside an administrative database can store concise entries, but it should not be the only surviving evidence after a server loss. Export or replicate the record according to the organization’s recovery plan. Keep the schema small enough that an operator will use it. A long form with many optional fields invites blank entries.
The example creates a basic table in a dedicated administrative database. Adapt names and permissions to your environment. The table does not enforce a review process by itself; the team must still require an entry for each approved change.
CREATE TABLE dbo.DatabaseChangeLog
(
ChangeId bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
DatabaseName sysname NOT NULL,
ChangeTimeUtc datetime2(3) NOT NULL
CONSTRAINT DF_DatabaseChangeLog_ChangeTimeUtc
DEFAULT SYSUTCDATETIME(),
Summary nvarchar(300) NOT NULL,
Reason nvarchar(1000) NOT NULL,
ApprovalId nvarchar(100) NOT NULL,
OperatorLogin sysname NOT NULL,
Verification nvarchar(1000) NULL
);Record Who Without Guessing
ORIGINAL_LOGIN() can record the SQL login that opened a session. In an application using one service login, it does not identify the human who approved or requested a change. Keep operator, requester, and approver as separate fields when the workflow needs them. Use the approved identity source and avoid letting a script infer a person from a shared account.
I check the login value before inserting an entry. If it shows a service identity, the change record still needs a named operator from the deployment system. “Changed by appuser” does not settle an audit question. Identity quality matters as much as having a column called OperatorLogin.
Write the Entry With the Change
Create the log entry as part of the deployment workflow, not a note someone remembers later. Capture the intended reason before execution and the verification after it completes. If the change fails, record that outcome too. A log containing only successes gives a misleading history during incident review.
This example inserts a concise record for a reviewed change. Replace the sample identifiers and reason with the approved request. In practice, the workflow should update Verification after the validation runs. I do not present this row as evidence that a real deployment happened.
INSERT dbo.DatabaseChangeLog
(DatabaseName, Summary, Reason, ApprovalId,
OperatorLogin, Verification)
VALUES
(N'Sales', N'Add order status index',
N'Support approved order lookup workload',
N'CHG-EXAMPLE', ORIGINAL_LOGIN(),
N'Pending post-deployment validation');Capture Actual Schema Evidence
A short summary is useful, but keep the exact script and relevant before-and-after definitions in the deployment record. The log record can point to those artifacts. It should not attempt to embed an entire database diff in one text field. Check for changes made outside the approved process with a regular schema comparison.
I review sys.objects modify dates as a clue, not as a complete audit. A modify date cannot explain who changed an object or why. A robust change record combines the approved plan, executed script, and verified state.
SELECT SCHEMA_NAME(schema_id) AS schema_name,
name, type_desc, modify_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY modify_date DESC;Review Change Log Gaps Weekly
Compare the week’s deployments and emergency changes with log entries. Investigate an object change that has no corresponding record. Fix the process rather than inventing a retroactive reason. When emergency work is needed, allow a short initial entry and require a fuller review afterward. The urgent path still needs ownership.
I keep the review brief and regular. Which change would be hardest to explain to an auditor or a teammate today? Start there. The log becomes valuable when missing entries are visible and repaired, not when the table is created.
Keep the Change Log Findable and Durable
Make it easy to search by database, date, and change identifier. Give read access to the people who support the applications. Protect write access and retain the log for the required period. Include it in backup and restore exercises or export it to another controlled system. A log that disappears with the server it describes is a weak memory.
I link incidents back to relevant change entries when a deployment is a possible cause. That helps separate coincidence from evidence. A good change log does not prevent mistakes by magic; it shortens the path from an unexpected result to a responsible next action.
A change log becomes useful when it records intent and evidence together. For each change, include the request or ticket, affected database, object, operator, approval, execution time, before state, validation result and rollback reference. Do not store credentials or private payload data in the log. I prefer a small consistent record that people actually complete over a complicated form they skip during urgent work.
Distinguish planned changes from emergency work, but log both. An incident fix can be documented afterward with the same facts, including the reason the normal review path was shortened. The log should let a future DBA answer why a setting differs from baseline without searching chat history. A date and a person alone do not explain purpose.
Review the log during handover and incident follow-up. If an issue began after a deployment, the change record narrows the investigation; it does not prove causation. Keep the record findable and backed up with the rest of the operational evidence. A log that disappears with the instance cannot help explain its recovery.
A log entry should point to the exact script or deployment artifact that was approved and executed. If the executed version differs from the reviewed one, record that difference. I also include a validation query or result that another person can repeat. This turns the log from a diary into a practical investigation tool. During an incident, a precise change trail saves time without pretending every recent change caused the incident.
Related reading on this blog: Who ALTER'ed My Database? Catch Them Via DDL Trigger and Collecting Server Facts Into One Table Every Night.
A database change log is not a list of timestamps, it is a record of decisions and verified results.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
