29 January,2019 by Tom Collins
Question : An error message has started appearing in the SQL Server Error Logs during a nightly full backup.
Could not clear 'DIFFERENTIAL' bitmap in database 'Database1' 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.
As suggested in the error message - I checked for insufficient resources and the first thing I checked was disk resources. In fact - there was only 2 MB left for the drive associated with the transaction log file. I attempted a LOG BAKBUP but this failed , so I attempted a Shrink file on the transaction log file and a new error appeared related to :
Reading that post outlined a few potential ideas . How should I progress?
Answer: The most important point about the ideas suggested in
, is to pick the most effective method for the particular details.Based on information you've supplied - and the safest, is to opt for make some extra disk space available - either through manipulating another database file on the same drive . Once you've confirmed some extra space , than issue the BACKIP LOG command.
I like to be ultra - cautious on fixing these type of issues i.e do I have a recovery point and proper review of underlying causes and understanding the root cause .
Apply the FORDEC decision making process
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |