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

4 Answers 4

up vote 2 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

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

share|improve this answer

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

The following DDL trigger is from the latest free Redgate tool, DLM Dashboard. This tool has an accompanying dashboard in which all historical schema changes are logged and can be configured to send emails on schema changes. We're looking for ways to improve it, so please let us know what you think!

CREATE TRIGGER [RG_SQLLighthouse_DDLTrigger]
    ON ALL SERVER
    WITH EXECUTE AS SELF
    AFTER DDL_EVENTS
AS
    IF OBJECT_ID(N'master.dbo.RG_SQLLighthouse_WriteEvent', N'P') IS NOT NULL
    BEGIN
        -- see http://msdn.microsoft.com/en-us/library/ms190763.aspx
        DECLARE @restoreXACT_ABORT bit = 16384 & (SELECT @@OPTIONS);
        SET XACT_ABORT OFF
        DECLARE @restoreANSI_PADDING bit = 16 & (SELECT @@OPTIONS);
        SET ANSI_PADDING ON
        DECLARE @restoreANSI_WARNINGS bit = 8 & (SELECT @@OPTIONS);
        SET ANSI_WARNINGS ON
        DECLARE @restoreCONCAT_NULL_YIELDS_NULL bit = 4096 & (SELECT @@OPTIONS);
        SET CONCAT_NULL_YIELDS_NULL ON
        DECLARE @restoreNUMERIC_ROUNDABORT bit = 8192 & (SELECT @@OPTIONS);
        SET NUMERIC_ROUNDABORT OFF

        BEGIN TRY
            DECLARE @eventdata XML = EVENTDATA();
            EXECUTE MASTER.dbo.RG_SQLLighthouse_WriteEvent @eventdata
        END TRY
        BEGIN CATCH
            IF OBJECT_ID(N'master.dbo.RG_SQLLighthouse_WriteError', N'P') IS NOT NULL
            BEGIN
                BEGIN TRY
                    DECLARE @error_number INT = ERROR_NUMBER(),
                        @error_severity INT = ERROR_SEVERITY(),
                        @error_state INT = ERROR_STATE(),
                        @error_procedure SYSNAME = ERROR_PROCEDURE(),
                        @error_line INT = ERROR_LINE(),
                        @error_message NVARCHAR(4000) = ERROR_MESSAGE();

                    EXECUTE MASTER.dbo.RG_SQLLighthouse_WriteError @error_number,
                        @error_severity, @error_state, @error_procedure,
                        @error_line, @error_message;
                END TRY
                BEGIN CATCH
                    -- Left intentionally blank :(
                END CATCH
            END
        END CATCH

        IF @restoreXACT_ABORT = 1
        BEGIN
            SET XACT_ABORT ON
        END
        IF @restoreANSI_PADDING = 0
        BEGIN
            SET ANSI_PADDING OFF
        END
        IF @restoreANSI_WARNINGS = 0
        BEGIN
            SET ANSI_WARNINGS OFF
        END
        IF @restoreCONCAT_NULL_YIELDS_NULL = 0
        BEGIN
            SET CONCAT_NULL_YIELDS_NULL OFF
        END
        IF @restoreNUMERIC_ROUNDABORT = 1
        BEGIN
            SET NUMERIC_ROUNDABORT ON
        END
    END
GO

ENABLE TRIGGER [RG_SQLLighthouse_DDLTrigger] ON ALL SERVER
GO
share|improve this answer

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.