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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE tempdb CREATE TRIGGER blitz_proc_check ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE AS BEGIN SET NOCOUNT ON; DECLARE @event_data XML; SET @event_data = EVENTDATA() IF ( SELECT @event_data.value ('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)') ) LIKE '%Blitz%' BEGIN RAISERROR('Stop creating Blitz procs in user databases.', 0, 1) WITH NOWAIT; ROLLBACK; END END; GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TRIGGER blitz_proc_check ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE AS BEGIN SET NOCOUNT ON; DECLARE @event_data XML; SET @event_data = EVENTDATA() IF (@event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(255)')) = 'tempdb' BEGIN IF ( SELECT @event_data.value ('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)') ) LIKE '%Blitz%' BEGIN RAISERROR('Stop creating Blitz procs in user databases.', 0, 1) WITH NOWAIT; ROLLBACK; END END END; GO |
Thanks for reading!
10 Comments. Leave new
Sneaky, I like!
We could have a dbareactions-like list with such “good to have in model” inexpensive triggers… ;-P
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.
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’
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…
No problemo!
nice is article