September 21, 2015 at 10:02 am
We have a script running everyday for rebuild and re-organisation of indexes. But, somehow its getting failed. Attached script for your consideration. There is no database name with amoperations. There is table called DatabaseObjectAudit but it exist on master db.
Message:
-->Start Index Maint
-> Gathering fragmentation information (can take a while!)
-> Gathering COMPLETE : Total of 43 databases were found.
-> Gathering COMPLETE : Total of 1622 indexes were found.
-> Gathering COMPLETE : Total of 9 indexes were found for optimization.
-> Starting the index maintenance
Fragmented index (91%) found : [PK_DVW208] ON azApps.[dbo].[DVW208]
ALTER INDEX [PK_DVW208] ON [azApps].[dbo].[DVW208] REBUILD WITH( FILLFACTOR = 80, ONLINE = ON, SORT_IN_TEMPDB = ON)
ALTER INDEX [PK_DVW208] ON [azApps].[dbo].[DVW208] REBUILD WITH( FILLFACTOR = 80, ONLINE = ON, SORT_IN_TEMPDB = ON)
Msg 208, Level 16, State 1, Procedure AHCAuditAllObjects, Line 17
Invalid object name 'amOperations.dbo.DatabaseObjectAudit'.
Can anyone please help?
September 21, 2015 at 10:17 am
What's this object: AHCAuditAllObjects
do you have a trigger based audit in place or something like that?
September 21, 2015 at 10:21 am
I tried with msforeachdb but not able to find out that object.
Is there any way to find that object from the database?
September 21, 2015 at 11:28 am
I'm with NJ-DBA. What is AHCAuditAllObjects?
That procedure seems to be giving you issues. I can't find it in a search, so it looks like it's something you've created locally.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 21, 2015 at 12:14 pm
Actually, i haven't created this SP. I just moved with this company recently and there are no other DBA's over here. So, i really dont have any idea about this.
September 21, 2015 at 12:31 pm
select * from sys.objects where name='AHCAuditAllObjects'
run that across all your databases... did you find this proc? Use the GUI to "find dependencies"... did you find a trigger?
September 21, 2015 at 1:00 pm
Yes. I did run against each dB using msforeachdb as well as using the script.
But i couldnt find anything.
There are few triggers but it doesnt seems to be related to this at all.
September 21, 2015 at 1:04 pm
Do you have sysadmin level permissions on the server? somewhere a proc named AHCAuditAllObjects exists, or you would not be getting that error.
September 21, 2015 at 1:35 pm
We are having a similar kind of issue for update statistics as well.
This job is running under NT AUTHORITY\NETWORK SERVICE.
Message
Executed as user: NT AUTHORITY\NETWORK SERVICE. Invalid object name 'amOperations.dbo.DatabaseObjectAudit'. [SQLSTATE 42S02] (Error 208). The step failed.
There is no database called amoperations or no table with DatabaseObjectAudit.
I have attached query and result.
Update stats query works well till --select * from @StatisticsReport. I have attached excel sheet that pull the result from the query select * from @StatisticsReport.
But, somehow it gets failed at last step.
September 21, 2015 at 1:44 pm
Do you have sysadmin permissions?
Look at this error:
Msg 208, Level 16, State 1, Procedure AHCAuditAllObjects, Line 17
Invalid object name 'amOperations.dbo.DatabaseObjectAudit'.
the only way you are getting that error is if a proc called AHCAuditAllObjects is trying to call that invalid object. That proc exists, it's probably being called by some trigger that's auditing (or trying to audit) object changes. The key to solving your problem is finding AHCAuditAllObjects. If you can't find it, one possibility is that you dont have permissions to see it.
September 21, 2015 at 1:45 pm
This really looks like a trigger. Check for db-level (DDL) triggers. If not, check for server-based triggers (that seems unlikely in this case, though). For example:
SELECT t.*
FROM sys.triggers t
WHERE
t.parent_id = 0 AND
(OBJECT_DEFINITION(t.object_id) LIKE '%amOperations%' OR
OBJECT_DEFINITION(t.object_id) LIKE '%DatabaseObjectAudit%')
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 21, 2015 at 1:45 pm
It seems like something wrong with the below statement.
EXEC sp_executesql @SQLStmt
I have already set the @SQLStmt to nvarchar(max).
Am i missing something?
September 21, 2015 at 2:04 pm
I have run the query below:
use azADT
go
SELECT t.*
FROM sys.triggers t
WHERE
t.parent_id = 0 AND
(OBJECT_DEFINITION(t.object_id) LIKE '%amOperations%' OR
OBJECT_DEFINITION(t.object_id) LIKE '%DatabaseObjectAudit%' or
OBJECT_DEFINITION(t.object_id) LIKE '%AHCAuditAllObjects%')
and find AHCAuditAllObjects in azadt db.
Name Object_ID
AHCAuditAllObjects 1275151588
Here is the code;
USE [azADT]
GO
/****** Object: DdlTrigger [AHCAuditAllObjects] Script Date: 09/21/2015 16:38:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [AHCAuditAllObjects] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/aObjectName)[1]', 'sysname')
INSERT [amOperations].[dbo].[DatabaseObjectAudit]
(DatabaseName,JobId ,ObjectName, Operation, CommandText,DoneBy)
SELECT db_name(),1,CONVERT(sysname, @object),@eventType,@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)'),SUSER_NAME()
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [AHCAuditAllObjects] ON DATABASE
GO
ENABLE TRIGGER [AHCAuditAllObjects] ON DATABASE
GO
September 21, 2015 at 2:37 pm
Is this in a DDL trigger by chance?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 21, 2015 at 2:54 pm
EasyBoy (9/21/2015)
I have run the query below:use azADT
go
SELECT t.*
FROM sys.triggers t
WHERE
t.parent_id = 0 AND
(OBJECT_DEFINITION(t.object_id) LIKE '%amOperations%' OR
OBJECT_DEFINITION(t.object_id) LIKE '%DatabaseObjectAudit%' or
OBJECT_DEFINITION(t.object_id) LIKE '%AHCAuditAllObjects%')
and find AHCAuditAllObjects in azadt db.
Name Object_ID
AHCAuditAllObjects 1275151588
Here is the code;
USE [azADT]
GO
/****** Object: DdlTrigger [AHCAuditAllObjects] Script Date: 09/21/2015 16:38:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [AHCAuditAllObjects] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/aObjectName)[1]', 'sysname')
INSERT [amOperations].[dbo].[DatabaseObjectAudit]
(DatabaseName,JobId ,ObjectName, Operation, CommandText,DoneBy)
SELECT db_name(),1,CONVERT(sysname, @object),@eventType,@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)'),SUSER_NAME()
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [AHCAuditAllObjects] ON DATABASE
GO
ENABLE TRIGGER [AHCAuditAllObjects] ON DATABASE
GO
Does the database amOperations exist?
Does the table DatabaseObjectAudit exist in this database if the database exists?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply