Integrity check fails in MP, not manually

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 2008 Forums

SQL Server Administration (2008)

Integrity check fails in MP, not manually

Author

Topic

bryan42
Starting Member

28 Posts

Posted - 2011-07-28 : 12:46:19

I have a maintenance plan that does this:
1. check integrity of system databases
2. create full backups of system databases
3. check integrity of 7 user databases
4. create backups of the 7 user databases

This plan fails at step (3) where it tries to check integrity of the last (7th) user database. The first 6 checks run. The last two integrity check output looks like this:

6th database:
Executing query "USE [database6] ".: 50% complete End Progress Progress: 2011-07-28 01:37:41.82 Source: Check Database Integrity Executing query "DBCC CHECKDB(N'database6', NOINDEX) ".: 100% complete End Progress Progress: 2011-07-28 01:37:41.85 Source: Check Database Integrity

7th database:
Executing query "USE [database7] ".: 50% complete End Progress Progress: 2011-07-28 01:38:59.83 Source: Check Database Integrity
Executing query "DBCC CHECKDB(N'database7', NOINDEX) ".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:00:10 AM Finished: 1:39:00 AM Elapsed: 2330.09 seconds. The package execution failed. The step failed.

When I run a manual integrity check of database7 with 'DBCC CHECKDB(database7) WITH all_errormsgs, no_infomsgs;', the only output is Command(s) completed successfully.

What could be wrong here?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-07-28 : 13:09:22

Can you run the package through in debug mode? This will give you more information on the error. Oh, you might want to run it under the SQLAgent context as this is what you are doing when it is failing.

On that, are you trying to output anything to a directory that SQLAgent hasn't got access to?

bryan42
Starting Member

28 Posts

Posted - 2011-07-28 : 16:03:18

Checking the logged output of the job, I'm seeing a problem found with database5 instead of database7. Running a checkdb on that database yields this.

Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:10349) in object ID 223339860, index ID 1, partition ID 72057594151763968, alloc unit ID 72057594169065472 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'UserSecurityProfile' (object ID 223339860).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'database5'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources