none
DBCC skipping one database during integrity check RRS feed

  • Question

  • Hi,

    we are running DBCC Integrity command using for user databases and sometime it's skipping one database.

    No error message.

    last few days, user databases we have 6 DB and DBCC when running every night, one DB not running DBCC integrity check but other databases running so couldn't understand why one DB not checked by DBCC command.

    There are no information or any error message in log.

    This kind of behavior happens on another server also randomly during nightly backup, one or two DBs getting skipped sometimes and it's not happening everyday but sometimes. 

    Saturday, November 30, 2019 7:50 AM

All replies

  • There is no DBCC command to run integrity checks on all database, so DBCC is most likely not skipping any integrity check. Rather it is the code you are running that decides for one reason or another not invoke DBCC for this database. Same goes for the backup.

    But since we don't know what code you use to run DBCC and these backups, it is difficult to help.

    But whatever you use now, you should consider to switch to Ola Hallengren's solution, http://ola.hallengren.com, as this is the preferred solution and the de-facto standard.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, November 30, 2019 10:55 AM
  • Thanks Erland for your response.

    I am using Ola script for whole Maintenance solution.

    Saturday, November 30, 2019 2:46 PM
  • "code you are running that decides for one reason or another not invoke DBCC for this database"

    How we can avoid this behavior?

    Saturday, November 30, 2019 3:30 PM
  • Have you tried to run the DBCC CHECKDB on that database only using the Ola Hallengren script? You can create a temporary SQL job with an output file and schedule a one time run during off business hours to see for any differences. 
    Saturday, November 30, 2019 5:59 PM
  • Tanks Israel.

    Normally we run sometimes if it's skipping the database but my question is what's the reason behind to skip the database as we also use Ola backup solution for User database backup (USER_DATABASES)and randomly this kid of behavior also happens during the backup form different server/instances for one or two databases.


    Sunday, December 1, 2019 4:51 PM
  • I have been using Ola's script for years and have not seen this before. It is possible  you changed  the cursor status?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, December 1, 2019 4:54 PM
    Answerer
  • Please post the output file contents (anonymized of course) from the execution of of the Ola job that skipped a DB.

    And the code in the job step.


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Sunday, December 1, 2019 7:51 PM
  • Thanks Uri.

    I am using exact script what he had so nothing changed except dba db.

    Monday, December 2, 2019 4:13 AM
  • Thanks Kevin.

    Please see the following output, last Database UDMain is not showing in the log.

    Date		11/30/2019 1:00:00 AM
    Log		Job History (DatabaseIntegrityCheck - USER_DATABASES)
    
    Step ID		1
    Server		SQLPX
    Job Name		DatabaseIntegrityCheck - USER_DATABASES
    Step Name		DatabaseIntegrityCheck - USER_DATABASES
    Duration		00:24:12
    Sql Severity	0
    Sql Message ID	0
    Operator Emailed	
    Operator Net sent	
    Operator Paged	
    Retries Attempted	0
    
    Message
    Executed as user: ServiceAC. DateTime: 2019-11-30 03:00:02  Server: SQLPX Version: 10.50.4339.0  Edition: Enterprise Edition (64-bit)  
    Procedure: [DBADB].[dbo].[DatabaseIntegrityCheck]  Parameters: @Databases = 'USER_DATABASES, UDDB', @CheckCommands = 'CHECKDB', @PhysicalOnly = 'N',
     @NoIndex = 'N', @ExtendedLogicalChecks = 'N', @TabLock = 'N', @FileGroups = NULL, @Objects = NULL, @LogToTable = 'Y', @Execute = 'Y'  
    Source: http://ola.hallengren.com     DateTime: 2019-11-30 03:00:02  Database: [UDDB1]  Status: ONLINE  Standby: No  Updateability: 
    READ_WRITE  User access: MULTI_USER  Is accessible: Yes  Recovery model: SIMPLE DateTime: 2019-11-30 03:00:02 
     Command: DBCC CHECKDB ([UDDB1]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITYOutcome: Succeeded  Duration: 00:00:13  DateTime: 2019-11-30 03:00:15     DateTime: 2019-11-30 03:00:15  
    Database: [UDDB12]  Status: ONLINE  Standby: No  Updateability: READ_WRITE  User access: MULTI_USER  Is accessible: Yes  Recovery model: SIMPLEDateTime: 2019-11-30 03:00:15 
     Command: DBCC CHECKDB ([UDDB12]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITYOutcome: Succeeded  Duration: 00:00:04  DateTime: 2019-11-30 03:00:19     DateTime: 2019-11-30 03:00:19  
    Database: [UDDB2]  Status: ONLINE  Standby: No  Updateability: READ_WRITE  User access: MULTI_USER  Is accessible: Yes  Recovery model: SIMPLEDateTime: 2019-11-30 03:00:19  
    Command: DBCC CHECKDB ([UDDB2]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITYOutcome: Succeeded  Duration: 00:23:14  DateTime: 2019-11-30 03:23:33     DateTime: 2019-11-30 03:23:33  
    Database: [UDDBCMS]  Status: ONLINE  Standby: No  Updateability: READ_WRITE  User access: MULTI_USER  Is accessible: Yes  Recovery model: SIMPLEDateTime: 2019-11-30 03:23:33 
     Command: DBCC CHECKDB ([UDDBCMS]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITYOutcome: Succeeded  Duration: 00:00:31  DateTime: 2019-11-30 03:24:04     DateTime: 2019-11-30 03:24:04 
     Database: [UDDBR]  Status: ONLINE  Standby: No  Updateability: READ_WRITE  User access: MULTI_USER  Is accessible: Yes  Recovery model: SIMPLEDateTime: 2019-11-30 03:24:04 
     Command: DBCC CHECKDB ([UDDBR]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITYOutcome: Succeeded  Duration: 00:00:02  DateTime: 2019-11-30 03:24:06     DateTime: 2019-11-30 03:24:06 
     Database: [DBADB]  Status: ONLINE  Standby: No  Updateability: READ_WRITE  User access: MULTI_USER  Is accessible: Yes  Recovery model: SIMPLEDateTime: 2019-11-30 03:24:06  
    Command: DBCC CHECKDB ([DBADB]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITYOutcome: Succeeded  Duration: 00:00:06  DateTime: 2019-11-30 03:24:12     DateTime: 2019-11-30 03:24:12.  Process Exit Code 0.  The step succeeded.
    

    Monday, December 2, 2019 4:22 AM
  • May be silly question but how are you reaching to a conclusion that integrity check was missed, as per above output it ran for 6 databases and in starting you said you have 6 databases, am I missing something ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, December 2, 2019 7:13 AM
    Moderator
  • Would you please directly execute the DBCC command manually on the skipped database?Can it be executed normally without any errors?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 2, 2019 7:49 AM
  • Why 'USER_DATABASES, UDDB' instead of 'USER_DATABASES'?

    What database is missing? Is it online? Any chance it was not online at the same time as the CheckDB job ran, such as being restored to this server by another process?


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Monday, December 2, 2019 1:22 PM
  • Thanks Uri.

    We didn't change anything.

    Sunday, December 15, 2019 7:10 PM
  • Thanks Shashank.

    This is the output which is not showing couple user databases, so couple DBs is missing into output log but no any error.

    Sunday, December 15, 2019 7:12 PM
  • Normally we execute the command for missing DBs and no error or any other messages and it's same for when we nightly job and let's say it's checking 8 databases then out of 8, it's skipping two databases (may be last two into the list) but it's not showing any errors or messages.
    Sunday, December 15, 2019 7:15 PM
  • Thanks Kevin.

    Whatever databases missing those are online and if it's missed today then not necessary that it will miss again today as it happens randomly.

    Sunday, December 15, 2019 7:16 PM
  • Nothing in the CommandLog table?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, December 16, 2019 5:02 AM
    Answerer
  • How long was it since you installed Ola's solution? If was some time ago, maybe you should check if he has newer version out and check his changelog, in this case is a known issue.

    Else I think you would need to alter his scripts to add some debug to investigate what is going on. Keep in mind that you are using a open-source solution provided to you for free, so you will have to some of the work yourself.

    A suitable logging would be to have a table with five columns:

    1) An IDENTITY column.
    2) A datetime2(3) column that defaults to sysdatetime.
    3) dbname column to which you log the name of the database to be processed right now.
    4) The command to run, for intance DBCC, BACKUP etc.
    5) An xml column to which you log the output of SELECT * FROM sys.databases FOR XML RAW.

    The point with the XML column is that it can give you some idea is there is some changes the database state while the job is running.

    I can't say exactly where in the scripts you need to make these changes, since I'm not using Ola's scripts myself. (I'm not working as a DBA, but I am in a developer role, that's why.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, December 29, 2019 4:00 PM
  • In addition to Erland's excellent suggestion - have you gone through these options for support?:

    https://ola.hallengren.com/contact.html


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Sunday, December 29, 2019 4:29 PM
  • Thanks Erland.
    Saturday, January 25, 2020 4:45 PM
  • Nothing into Command log.

    Thanks

    Tuesday, January 28, 2020 7:41 PM