Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am investigating some performance issues on my SQL Server 2008 R2 server. Some queries are being recompiled due to a "schema change". I am pretty sure no one is changing the schema. All databases on this server are full text enabled.

The question is: how can I find what is causing the schemas to change? Is there any query oy profiler column that can tell me what is going on?

share|improve this question
1  
By "schemas" it means the metadata (i.e., Object Definitions), not the owner-schemas specifically. –  RBarryYoung Oct 21 '12 at 15:28
add comment

3 Answers

up vote 1 down vote accepted

You can use a DDL trigger to log schema changes.

CREATE DATABASE AuditDB;
GO

USE AuditDB;
GO

CREATE TABLE dbo.DDLEvents
(
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EventType    NVARCHAR(64),
    EventDDL     NVARCHAR(MAX),
    EventXML     XML,
    DatabaseName NVARCHAR(255),
    SchemaName   NVARCHAR(255),
    ObjectName   NVARCHAR(255),
    HostName     VARCHAR(64),
    IPAddress    VARCHAR(32),
    ProgramName  NVARCHAR(255),
    LoginName    NVARCHAR(255)
);

Then in your database, you can add events to a DDL trigger:

CREATE TRIGGER DDLTrigger_Sample
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
        CREATE_TABLE,     ALTER_TABLE,     DROP_TABLE,
        CREATE_VIEW,      ALTER_VIEW,      DROP_VIEW
        --, etc etc
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA();
    DECLARE @ip VARCHAR(32) = (SELECT client_net_address
        FROM sys.dm_exec_connections WHERE session_id = @@SPID);

    INSERT AuditDB.dbo.DDLEvents
    (
        EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData,
        DB_NAME(),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END
GO

The advantage over audit is that this does not require Enterprise Edition. The advantages over the default trace are that (a) you can control which events you capture and (b) you can keep your history as long as you want (the default trace rolls over).

This is culled from a tip I wrote here.

share|improve this answer
add comment

In SQL Server 2008, there is a default trace that will capture schema changes. If that option has not been turned off, then you can look through those trace files. You can check this by running sp_configure.

EXEC sp_configure 'default trace enabled';

If the run_value is 1, then it's still enabled. The default location of the trace files will be in your LOG folder. You can search through each of those trace files to find out if any of the schema or tables have been altered.

For details on the default trace, check out Books Online.

share|improve this answer
add comment

If you're using Enterprise ed you could enable SQL Server Audit to capture DDL changes.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.