Post reply

DDL Trigger

  • March 22, 2016 at 8:52 am

    #309335

    I have a set of triggers to keep track of database changes. I capture create, alter and delete. It has been tested with manual creation, file size modification and delete of a test database. Under those circumstances, it works. But the reason why I have this trigger is to catch when a third party application creates and add space to a database. The delete trigger is there for completeness for the deletions are done by me when the users are done with the database and the audit retention period expires. The application must be handling the growth because there is no auto-growth event but the ALTER DATABASE trigger is not fired. Has anyone experienced this?

    Thanks in advance,

    Eduardo

  • March 22, 2016 at 9:04 am

    #1866827

    Perhaps you could use Profiler to find out what the app is doing?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • March 22, 2016 at 9:07 am

    #1866831

    Profiler would have to be filtered at the most because it is an auditing application and it captures too much data.

    Thanks

  • March 22, 2016 at 9:23 am

    #1866835

    I started extended events session to capture that file size change. That would be more to the point than a profiler trace.

  • March 22, 2016 at 9:59 am

    #1866850

    Eduardo Olivera (3/22/2016)

    I started extended events session to capture that file size change. That would be more to the point than a profiler trace.

    I was only trying to help.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • March 22, 2016 at 10:44 am

    #1866865

    I know and I thank you for that. But I was trying to avoid that. I do appreciate your help. I didn't mean to come as I didn't really appreciate your suggestion. 🙂

  • March 22, 2016 at 1:51 pm

    #1866937

    Data/Log file auto grow will be in the default trace, FWIW

  • March 22, 2016 at 1:54 pm

    #1866940

    Thanks

  • March 24, 2016 at 10:02 am

    #1867500

    The Extended Event that used is based on an entry at Brent Ozar by Erik Darling ( https://www.brentozar.com/archive/2015/12/tracking-tempdb-growth-using-extended-events/ ). I modified it to suit my purposes and it allowed me to find what the standard disk usage report supplied by Microsoft had been hiding. Autogrowth was not reported but it indeed occurred. My suspicions that the application might have been growing the database file were disproved. This is the extended event that I used to catch the file size growth event.

    CREATE EVENT SESSION [DBFileGrowth] ON SERVER

    ADD EVENT sqlserver.database_file_size_change(

    ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text))

    ADD TARGET package0.event_file(SET filename=N'D:\DBA\DB_File_Growth.xel',max_file_size=(50))

    WITH (STARTUP_STATE=OFF)

    GO

    My thanks to all who contributed their knowledge here and to Erik Darling at Brent Ozar for facilitating the creation of the extended event.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply