Check Database Integrity - Failed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Check Database Integrity - Failed

Author

Topic

Mathias
Posting Yak Master

119 Posts

Posted - 2007-02-16 : 04:51:32

I have setup a full maintenance plan on SQL2005. When I run the job, I don't see any error but by loooking at the Log file viewer it appears that Check Database Integrity step failed because :

Alter failed for Server 'LUMONT001'.

Moreover, when I run DBCC CHECKDB from the console I have no error message. Any clue?
Thanks, Paul

Kristen
Test

22859 Posts

Posted - 2007-02-16 : 08:29:43

I wonder what its trying to "Alter" ??

Mathias
Posting Yak Master

119 Posts

Posted - 2007-02-16 : 09:06:04

By using the profiler I found what was sent to the server with the words "Alter"

EXECUTE msdb..sp_maintplan_update_log 'D71A1F02-0CFD-4215-904C-1F9D160B4A9D','Check Database Integrity (LUMONT001)','Check Database integrity on Target server
connection','Databases: QuestSoftware,ReportServer,ReportServerTempDB','Include indexes','','LU40MONT0010','0','2007-02-16 14:55:56','2007-02-16 14:55:56',0,'Alter failed for
Server ''LU40MONT0010''. ',''

For all indexes :
ALTER INDEX [PK__qs_cache_times__182C9B23] ON [dbo].[qs_cache_times] REORGANIZE WITH ( LOB_COMPACTION = ON )

ALTER INDEX [PK__qs_CheckSuppress__108B795B] ON [dbo].[qs_CheckSuppressed_state] REORGANIZE WITH ( LOB_COMPACTION = ON )

ALTER INDEX [IX_SnapshotCleaning] ON [dbo].[SnapshotData] REORGANIZE WITH ( LOB_COMPACTION = ON )
all running fine with no error

Kristen
Test

22859 Posts

Posted - 2007-02-16 : 12:37:09

Looks like a "Data Reorganisation" step, rather than a "Check Integrity" step. Maybe those two tasks kinda run as a single step?

Perhaps the Login used by the scheduled task does not have permissions to run the ALTER ... REORGANIZE commands on that particular database?

Kristen

Mathias
Posting Yak Master

119 Posts

Posted - 2007-02-19 : 04:11:02

The user is "sa"
In the job step property, I have

/SQL "Maintenance Plans\MaintainSystemsDBs" /SERVER LUMONT001 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package\Subplan.Disable";false

How can I run the job manually from the query analyzer?

Kristen
Test

22859 Posts

Posted - 2007-02-19 : 07:59:48

"How can I run the job manually from the query analyzer?"

if you can get the actual SQL from Profiler then that might be the best way.

For safety I would be inclined to wrap it in a Transaction - in case running it out-of-sequence mucks anything up, so something like:

BEGIN TRANSACTION

EXEC dbo.MS_SP_DataReorganisation @PARAM1 = 'foo'

ROLLBACK

Kristen

Mathias
Posting Yak Master

119 Posts

Posted - 2007-02-26 : 04:51:57

It looks like an install problem. We uninstall SQL 2005 and reinstall it and we don't get the error message anymore.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources