Integrity checks job for DB Maintenance plan ''DB maintenance plan1''

  • Under my sql server agent: Jobs, I have got this job "Integrity checks job for DB Maintenance plan 'DB maintenance plan1'". I think it's something to do with checking the integrity of maintenance plan but couldn't figure out exactly what it is. It is failing and I just wondering whether it will affect the database in any way.

  • Hi Ashik,

    Integrity check are used for determining corrupt data, reallocation of indexes, repairing index pages etc. Integrity and optimization tasks are time-consuming and if it fails will effect the full backup and transaction backup plans.

    In my environment, I have 3 separate plans instead of one plan doing all 3 jobs..i.e. I have separate plan for full backup, second plan for transaction log backup and 3rd plan for "optimizations and integrity" which I run on Sundays only before my full backup schedule.

    If you want to delve into subject of "Optimizations and Integrity" in Backup Plans, I would recommend you to read BOL for a more elaborative explanation on that subject..

    Good luck

    Regards,

    Dilip

  • Hi dilip,

    Thanx for the reply. My integrity check is failing but full backup and transaction backup is running fine. Do I need to be worried?

  • As long as your full and tlog backups are okay and u can recover them in any emergency, then it's alrite..

    But saying so, u always wud like to have a second look why the integrity checks are failing. As i said possiblited would be like corruption of data, indexes problem, y not try issuing DBCC CHECKDB in QA..This will take time but gives u the entire picture of what exactly is failing etc. Try it!

    Reg backups, it's always recommended u test it - by restoring it on a standby/test server and run some base scripts to check if it's in sync with prod database.

    HTH

    Regards,

    Dilip

  • thanx dilip.

    i have got two databases. i checked them using command "dbcc checkdb 'dbname' " and i got the following message at the end "CHECKDB found 0 allocation errors and 0 consistency errors in database 'dbname'." So i believe my database is not corrupted and indexes are fine as well.

    i am new to this field (managing database) and yesterday i was playing around with indexes and i issued "DBCC SHOWCONTIG 'tablename'" and i don't get scan density 100% for all tables. and also logical scan fragmentation and extent scan fragmentation. is that normal? i read that they need to be 100% and 0% respectively else means index defragmentation. how correct is that?

  • If some fragmentation exists, either you can use DBCC DEINDEXFRAG or DBCC DBREINDEX which will build ur indexes with an appropriate fill factor which you can provide..

    You are right logical scan/extent fragmentation should be near to 0 and scan density should be 100.

    regards,

    dilip

  • thanx dilip.

    i did use the BBCC DEINDEXFRAG and DBCC DBREINDEX and also tried deleting and recreating index manually but still extent scan fragmentation doesn't improve. fill factor is 90%.

    DBCC SHOWCONTIG scanning 'FeesPaid' table...

    Table: 'FeesPaid' (853578079); index ID: 1, database ID: 7

    TABLE level scan performed.

    - Pages Scanned................................: 115

    - Extents Scanned..............................: 15

    - Extent Switches..............................: 14

    - Avg. Pages per Extent........................: 7.7

    - Scan Density [Best Count:Actual Count].......: 100.00% [15:15]

    - Logical Scan Fragmentation ..................: 0.00%

    - Extent Scan Fragmentation ...................: 66.67%

    - Avg. Bytes Free per Page.....................: 737.1

    - Avg. Page Density (full).....................: 90.89%

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  • thanx dilip.

    i did use the BBCC DEINDEXFRAG and DBCC DBREINDEX and also tried deleting and recreating index manually but still extent scan fragmentation doesn't improve. fill factor is 90%.

    DBCC SHOWCONTIG scanning 'FeesPaid' table...

    Table: 'FeesPaid' (853578079); index ID: 1, database ID: 7

    TABLE level scan performed.

    - Pages Scanned................................: 115

    - Extents Scanned..............................: 15

    - Extent Switches..............................: 14

    - Avg. Pages per Extent........................: 7.7

    - Scan Density [Best Count:Actual Count].......: 100.00% [15:15]

    - Logical Scan Fragmentation ..................: 0.00%

    - Extent Scan Fragmentation ...................: 66.67%

    - Avg. Bytes Free per Page.....................: 737.1

    - Avg. Page Density (full).....................: 90.89%

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  • By using DBCC INDEXDEFRAG / REINDEX should have the done the job for you..Extent Scan Fragmentation shows a higher percentage if your indexes are spanning multiple pages and not in contiguous order..If urs is a highly transaction table just try reducing the fill factor around 50-60% and again rebuilding the indexes..

    regards,

    dilip

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply