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
Appreciating the persistence you put into your blog and detailed information you provide.
ReplyDeleteDevops Course Training in Chennai |Best Devops Training Institute in Chennai
Selenium Course Training in Chennai |Best Selenium Training Institute in Chennai
Java Course Training in Chennai | Best Java Training Institute in Chennai
Thanks for all your works...looking Towards for Java to enhance the knowledge...Visit here for more...
ReplyDeleteJava training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery
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!
ReplyDeleteJava Training in Chennai
Java Training in Velachery
Java Training in Tambaram
Java Training in Porur
Java Training in OMR
Java Training in Annanagar
gioco digitale - stillcasino.com
ReplyDeleteWe 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