Sql-server – Attach a trigger to ‘sys.dm_db_missing_index_details’

sql serversql-server-2012trigger

I am trying to attach a trigger to the sys table sys.dm_db_missing_index_details with the following query:

CREATE TRIGGER indexBackup
ON  sys.dm_db_missing_index_details 
AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
EXEC PerformanceTest.[dbo].[usp_persist_missing_index_DMV_data]

END
GO

I am getting the following error:

The object sys.dm_db_missing_index_details does not exist or is invalid for this operation.

Is it allowed to attach a trigger to a sys view or am i missing something?

Best Answer

No triggers on system DMVs, sorry.

Besides, you wouldn't really want to do this: that DMV is updated every time a query is run with a missing index request. That could be hundreds or thousands of times per second - and performance matters there.

Instead, consider running an Agent job every 5-15 minutes to capture the data you're looking for.

Related Solutions

Sql-server – sysadmin cannot drop login when ddl audit trigger enabled

After a considerable amount of testing, I finally discovered the reason behind this error. The client connection explicitly set ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL OFF. XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON. I had attempted to override these within the trigger, but I may have placed them wrong. The final code below works, even with the explicit SET options in the connections from Great Plains:

CREATE TRIGGER [ddl_log] 
ON ALL SERVER 
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS 
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;

DECLARE @data XML
SET @data = EVENTDATA() 

EXECUTE AS LOGIN='<dummy login>'
INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
VALUES (
   GETDATE(),
   CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'), 
   CONVERT(NVARCHAR(100), HOST_NAME()),
   @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
   ) ;
REVERT 
END

As an alternative, I also could have simply inserted EVENTDATA() as an XML LOB into a table, rather than parsing it out into columns. Because I would not be manipulating the XML, the SET options do not matter. Then I would just build an XML index for querying performance, and construct a view to use for audit log reporting that parses the XML in the view definition, in the same manner as I am doing above in my INSERT statement.

Thanks to Max for pointing me in a different research direction, and @AaronBertrand on #sqlhelp who helped me with correct SET options within the body of the trigger.

Sql-server – Permissions using .NET SqlDependency

After days of debugging, one little setting made all the difference.

If you get this error mentioned in OP, please check the owner of the DB-role used! It wasn't set to dbo in my case. Changing that made it work - see comments for history!

Best Answer

Related Solutions

Sql-server – sysadmin cannot drop login when ddl audit trigger enabled

Sql-server – Permissions using .NET SqlDependency

Related Question