Could not clear 'DIFFERENTIAL' bitmap in database 'database name' because of error 9002

Today when I receive error: “Could not clear 'DIFFERENTIAL' bitmap in database 'database name' because of error 9002” I though this might be the opportunity to work with DCM pages but it was a big no……………….. This error is some thing else a big disappointment but still learning is learning. So I got exactly below error

 

ERROR: Could not clear 'DIFFERENTIAL' bitmap in database 'database name' because of error 9002. As a result, the differential or bulk-logged bitmap overstates the amount of change that will occur with the next differential or log backup. This discrepancy might slow down later differential or log backup operations and cause the backup sets to be larger than necessary. Typically, the cause of this error is insufficient resources. Investigate the failure and resolve the cause. If the error occurred on a data backup, consider taking a data backup to create a new base for future differential backups.

 

BACKUP DATABASE is terminating abnormally.” Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

 

This all start with one of alert I receive about failure of maintenance job and as first step error log says above error. A bit of investigation and I found t-log is almost full and increasing log file size resolved the issue, but still root cause is unknown.

So I ran a basic query check:

 /******************************************************/

Use database
GO
SELECT
    name AS FileName,
    size*1.0/128 AS FileSizeinMB,
    CASE max_size
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' =
        CASE
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'
            WHEN growth > 0 AND is_percent_growth = 0
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM sys.database_files;
GO
Master..xp_fixeddrives
GO

 /******************************************************/

-> Auto grow is off

-> Drive doesn’t have much space

-> Log file is almost full

So I understood if auto grow is disable increase log file size by at least 10 % and if auto grow is enabled then don’t keep auto grow to very low size

Possible Solutions:

 If this error is observed:

• Check the database properties and if the log appears to be too small in size, adjust the Auto growth setting to allow it to grow larger. (Else it may cause the famous VLF’s issue, which impacts startup and recovery of the DB).
• If there are a large number of logs, perform a log backup so that committed data can be removed and the log will be smaller.
• Check the disk space available for the drive hosting the logs, and free up space if the drive is near capacity.
• Perform a new full backup if a differential was being performed
• Change the recovery model from simple to full or full to simple - this will reset all the bitmaps. Then perform a full backup. Set the database to the recovery model that you want and again run a full backup.