Tuesday 8 March 2016

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



While i was doing some testing in my server i got an interesting error while taking a full backup



System.Data.SqlClient.SqlError: Could not clear 'DIFFERENTIAL' bitmap in database 'test' 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. (Microsoft.SqlServer.Smo)

This was really an interesting error for me and had  no idea why i am getting this error while taking backup ,but i noticed one thing in the error message ' Typically, the cause of this error is insufficient resources' , Resources ehhh , whats thats??
So i started with basic check

Is there enough CPU ?? Yeah there is plenty enough 
Memory?? Enough free memory is there
Disk ?? yess.. enough to take another 30 backups
Then what else????????????????????
I missed one important thing in error message 'error 9002'
When log file is full database engine  throws error 9002
Yes there it is.... my log file is full and auto growth is disabled

  

But how come log space will make my backup fail?? anyway lets  give a try and initiated a log backup

OK there is no issues with my log backup and it completed successfully.. now  let me try for a full backup

There  goes my backup status 10 20 30 40 50 60 70 80 90 100 and completed successfully :)

So how come log space will cause backup failure??

So my understanding of this error we faced is that clearing differential bit map will be a logged operation so in our case there was no space to write to log file also we know a checkpoint will be triggered  when we take a backup and during checkpoint log records from log buffer will be written to log file so in this case since our log file was full and checkpoint was not able  to write log records to disk and once we took log backup there was enough space in log file and every thing went smooth then


4 comments:

  1. I am regular reader of your blog from long time,What a Beautiful post! This is so chock full of useful information I can’t wait to dig and start using my time on blogging and I am looking for and I love to post a comment that "The content of your post is awesome" Great work!
    Java Training in Chennai

    Java Training in Velachery

    Java Training in Tambaram

    Java Training in Porur

    Java Training in OMR

    Java Training in Annanagar

    ReplyDelete
  2. gioco digitale - stillcasino.com
    We are an independent vua nhà cái gambling site that specializes in providing online casino 메리트카지노 gambling and casino games online for gioco digitale real money. Our mission is to provide our

    ReplyDelete