Do DDL Triggers in TempDB Survive Restarts?

Of Course Not

Nothing survives in tempdb. But this kind of stinks for those of us who don’t want certain things created in there.

In my case, when I’m working on Blitz scripts, I have a terrible habit of accidentally re-creating the procs in user databases. Usually whichever I’m building test data in, like Stack Overflow. This can lead to several minutes of temporary insanity, where none of my changes are working.

I wrote a DDL trigger recently to prevent me from creating Blitz procs in user databases. It looks like this.

And it works just fine, it just doesn’t survive in tempdb. Something to be aware of.

What To Do?

One workaround is to create it in model. Then it will be in tempdb on restart, but it will also be in all the databases you create.

That may or may not have the intended effect! If you don’t want people doing a certain thing in just tempdb, you have to add an additional check using the event data.

Thanks for reading!

,
Previous Post
#TSQL2sday: System-Maintained LastUpdatedDate, LastUpdatedBy Columns
Next Post
Build Me A Build: Things I’d Do Different

10 Comments. Leave new

  • Sneaky, I like!

    We could have a dbareactions-like list with such “good to have in model” inexpensive triggers… ;-P

  • Stephen A Mangiameli
    January 11, 2017 9:54 am

    You could just create a SQLAgent job and schedule it to fire on Agent startup. That way it only gets created where you want it – tempdb. You may want to wrap it in an IF EXISTS, just in case you only restart the Agent.

    • I sort of hate Agent startup jobs. What if Agent is set to Manual startup? Or someone changes it? Or it doesn’t start for some other reason that doesn’t impede SQL from starting? It’s all weird.

      • Stephen A Mangiameli
        January 11, 2017 10:47 am

        I suppose it depends on the shop. Agent is kind of integral to a whole lot of maintenance and automated processing around here. If it doesn’t start as expected the creation of a DDL trigger in tempdb (or lack thereof) is the least of our concerns.

      • That’s a problem our Nagios instance finds, which then gets reported to the appropriate admin team immediately. 🙂 If the Agent isn’t running we’re in for all sorts of other badness.

  • Two solutions:
    – you could create a DML-Trigger on server level (be selective and do not create a on-all-dml-trigger, since they have to be disabled, when you create in-memory-tables)
    – you could create a procedure in the master that creates the tempdb-database trigger and set it as autostart procedure by executing
    EXEC sys.sp_procoption ‘dbo.p_create_tempdb_trigger’, ‘startup’, ‘on’

  • Austin Harrison
    January 11, 2017 11:23 am

    Could you just create the second trigger definition on the server instead of in the model database?

    CREATE TRIGGER blitz_proc_check ON ALL SERVER

    Would prevent the trigger definition from popping up in every new database that gets created.

  • Thanks brent…

  • nice is article

Menu
{"cart_token":"","hash":"","cart_data":""}