Maint. Plan on SQL 2005 - Integrity Check problem

[Catadmin]

I have two jobs / plans that do integrity checks. Both failing, but only one telling me it failed. I've edited the one job down to checking just one DB at a time and it still fails.

The error I'm getting is "Alter failed for Server 'MyServer\\MyInstance'." and in the ServerName part of the log, it lists my connection as MyServer\MyInstance (only one \ character). This looks odd to me. Has anyone else seen this before?

Also, does anyone know what the heck the integrity check task is doing that it requires an Alter command?

Thanks in advance...



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Do you have DDL Triggers enabled? I had the same problem with update stats failing because of a DDL trigger. The error it gave didn't say anything about the trigger.


- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

I know of no DDL Triggers on the system, but I'll check with the other DBAs to see if they might have done something and not told me. Thanks for the idea, Paul.

In the meantime, I'm open to any other thoughts too.

Thanks,



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

DDL Triggers will show up in the following path,
yourDatabase ->Programmability->Database Triggers.

My dbMaint plan was failing because of this trigger.

CREATE TRIGGER RestrictDDL
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS 

EXECUTE AS USER = 'DBO'

DECLARE @errordata XML
SET @errordata = EVENTDATA()
RAISERROR ('You can not perform this action on a production database. Please contact the DBA department for change procedures.', 16, -1)
ROLLBACK
INSERT DDLAudit
		(LoginName, 
		 UserName,
		 PostDateTime,
		 EventType,
		 DDLOp)
VALUES   (SYSTEM_USER, ORIGINAL_LOGIN(), GETDATE(), 
   @errordata.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)'), 
   @errordata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(2500)') ) 
RETURN;
GO

There was nothing in the error message about it. It will prevent any alter command to be run on the database.

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Ah. Thank you for the location. Unfortunately, this doesn't seem to be my problem. I have no DDL triggers at all on any of my databases...

Drat... Do you think it's trying to set the databases to single user or Restricted user??



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

No I don't think it will do that. I found this quote from BOL.

DBCC CHECKDB uses an internal database snapshot to provide the transactional consistency that it must have to perform these checks. For more information, see For more information, see Understanding Sparse File Sizes and the "DBCC Internal Database Snapshot Usage" section in DBCC (Transact-SQL). If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKDB acquires locks to obtain the required consistency. In this case, an exclusive database lock is required to perform the allocation checks, and shared table locks are required to perform the table checks

I believe it uses the alter command to create the internal db snapshot.

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

can you run DBCC CHECKDB manually?


- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

I just tested that on one DB (can't test the others until after hours) and the answer is Yes. The manual DBCC CHECKDB works. It's just the maintenance plan that won't work.

And I double-checked the maintenance plan against that DB just before running the CHECKDB and the MP failed, but the CHECKDB ran without errors....

Hrm. Something weird is going on in the state of Denmark. I wonder if it's a permissions issue on the job's owner.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

It could be permissions. Are you using a job step proxy account? But it definelty sounds like something is up with the Maint plan. Can you create a new maint plan that just does an integrity check?

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

I just though of something. Did you create the maint plan to run on all datatbases? It could have to do with the tempdb or resourcedb. Here is another quote from BOL.

Because the Resource database is accessible only in single-user mode, the DBCC CHECKDB command cannot be run on it directly. However, when DBCC CHECKDB is executed against the master database, a second CHECKDB is also run internally on the Resource database. This means that DBCC CHECKDB can return extra results. The command returns extra result sets when no options are set, or when either the PHYSICAL ONLY or ESTIMATE ONLY option is set. For more information about the Resource database, see Resource Database

In SQL Server 2005, running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.

Could it be failing because you can not obtain the locks on either one of those databases?

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

No on the job step proxy account, but I am using a domain account as the job owner that doesn't reside in the same domain as my server.

And my integrity plan is just an integrity check step and a notify operator task for failure. I don't see how the notify operator task could be causing issues, but I'll try removing that and see.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

What happens if you change the owner to sa?

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Now here's an interesting take on the problem (possibly):

http://sqljunkies.com/WebLog/acalvett/archive/2006/02/13/17999.aspx

So, how do I see if my Server is configured to Allow Updates? I can't find it on the Server properties.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Nevermind. I found it using sp_configure 'Show Advanced Options', 1 and then running sp_configure by itself showed me the options.

Unfortunately, my Allow Updates is set to 0 already, so this still isn't my problem. But it has caused people problems in the past.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

that is an intersting little feature.

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Okay, the best I've been able to determine, while running profiler, is that my integrity checks fail on the following command:

EXEC sys.sp_configure N'user options', 32 RECONFIGURE

Now, I used to have 0 where the 32 was, but I went into Server Properties and added ANSI NULLS to the list to see if there was a difference between having 0 User Connection Options set and having one set. Nope. It keeps failing. The command after the sp_configure is:

EXECUTE msdb..sp_maintplan_update_log 'E13FA8EA-4478-4F9F-9FC8-6B1F9FD8CF67','Check Database Integrity Task (MyServer, MyPort)','Check
Database integrity on Local server connection','Databases: MYDB','','','MyServer, MyPort','0','2006-11-30 08:35:44','2006-11-30
08:35:44',0,'Alter failed for Server ''MyServer, MyPort''. ',''

"Check Database Integrity" it says. What the heck does it think I'm trying to do??? Bleargh. I guess I'm going to have to contact Microsoft and see what they think on this.

Anybody else have any thoughts?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"