August 12, 2014 at 1:09 am
SQL server 2012, SP1
DB is in SIMPLE recovery mode (total size 124 GB).
Log file Initial size is 650 MB auto growth set for 500 MB.
Below is the error I am facing.
Could not clear 'DIFFERENTIAL' bitmap in database 'DBSAPCAD' 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.
A nonrecoverable I/O error occurred on file "//SQLDIFF/SAPCAD_DBSAPCAD.bak:" 995(The I/O operation has been aborted because of either a thread exit or an application request.).
BACKUP DATABASE is terminating abnormally.
In the weekend we faced an error on the DB backup.
Drive K got full because of database log file grown and we have 4 MB free space left. I have no option to increase the drive space nor the log file size.
I am unable to perform, full or differential backup for this db.
Can anyone help me to fix this issue and understand, why it grown log file even if it is in simple recovery.
Cheers,
- Win
"Dont Judge a Book by its Cover"
August 12, 2014 at 2:48 am
The log could have grown for any number of reasons. Large transaction, replication, CDC, lack of a checkpoint, active backup, couple of others.
Can you shrink the log a little?
If not, what's the value for log_reuse_wait_desc in sys.databases for that database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2014 at 2:49 am
Try increasing the autogrowth then trying the backup again.
August 12, 2014 at 2:50 am
Beatrix Kiddo (8/12/2014)
Try increasing the autogrowth then trying the backup again.
When the drive the files are on is full? How would that help?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2014 at 2:52 am
Sorry, I missed that there was no option to increase the drive space.
August 12, 2014 at 3:02 am
Unable to shrink it, it shows 0 MB free space and utilized 58.9 GB
For for log_reuse_wait_desc - it shows NOTHING
Making offline and online DB helps ?
OR
Making DB to Full, backup, then revert to SIMPLE..
Cheers,
- Win
"Dont Judge a Book by its Cover"
August 12, 2014 at 3:08 am
- Win (8/12/2014)
Making offline and online DB helps ?
NO!
Never take a DB offline if it's showing any kind of errors like this.
Making DB to Full, backup, then revert to SIMPLE..
Huh? What would be the point of that?
Besides, I thought you said you were getting errors running backups?
Run CHECKPOINT please, post any errors, check the free space in the log afterwards.
Also, how are you taking the backup? Native or a 3rd party tool?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2014 at 3:25 am
- Win (8/12/2014)
Making offline and online DB helps ?
NO!
Never take a DB offline if it's showing any kind of errors like this.
----------------------------------------------------------------------
I was just thinking around all the options. I cannot take offline.
--------------------------------------------------------------------------------------------------------------
Making DB to Full, backup, then revert to SIMPLE..
Huh? What would be the point of that?
Besides, I thought you said you were getting errors running backups?
--------------------------------------------------------------------------------------------------------------
I tried full backup, diff via query & via SQL Safe IDERA tool
It throws error.
Could not clear 'DIFFERENTIAL' bitmap in database 'DBSAPCAD' 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.
A nonrecoverable I/O error occurred on file "//SQLDIFF/SAPCAD_DBSAPCAD.bak:" 995(The I/O operation has been aborted because of either a thread exit or an application request.).
BACKUP DATABASE is terminating abnormally.
ran CHECKPOINT, it completed without errors
Log file size did not changed, it is the same size...
Cheers,
- Win
"Dont Judge a Book by its Cover"
August 12, 2014 at 3:30 am
we have the same 4 MB free space available on drive.
what if I work on Shrinking the LOG file using second option ?
-- Reorganize pages before releasing unused space - shrink file to 20 gigs.
Cheers,
- Win
"Dont Judge a Book by its Cover"
August 12, 2014 at 4:44 am
- Win (8/12/2014)
Log file size did not changed, it is the same size...
No, it won't, because checkpoint doesn't shrink log files.
It may however have freed some space internally
Can you please check whether or not the shrink dialog indicates free space in the log?
Also, can you please check what the log_reuse is, whether it's changed
what if I work on Shrinking the LOG file using second option ?
-- Reorganize pages before releasing unused space - shrink file to 20 gigs.
Reorganise pages is only applicable to data files. The two options do exactly the same thing on a log file
Do you have any other drives on the server that are large enough to move the log file (or data file) to?
Also, can you try a native full backup
BACKUP DATABASE <db name> TO DISK = <destination>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2014 at 7:22 pm
Thanks much Gail for helping me on this issue.
-- Sorry for the late response.
-- Shrink dialog shows the same 0 M free space available.
-- Log_reuse nothing has changed
Reorganise pages is only applicable to data files. The two options do exactly the same thing on a log file
-- Thanks for this. I just tried to shrink log files by specifying the 'shrink file to XX MB'. Now I got clear picture of this on LOG file.
Also, can you try a native full backup
BACKUP DATABASE <db name> TO DISK = <destination>
-- I am trying FULL backup manually (even with copy_only option), but the same error.
Do you have any other drives on the server that are large enough to move the log file (or data file) to?
-- I have drives with free space but not enough to this LOG file.
Cheers,
- Win
"Dont Judge a Book by its Cover"
August 12, 2014 at 8:20 pm
I see this behavior as strange..
I assume the ETL process or Bulk operations would have blown this LOG file to max extent even it is in SIMPLE recovery mode.
Cheers,
- Win
"Dont Judge a Book by its Cover"
August 13, 2014 at 12:57 am
What files are on this drive? The database's data file, log file? What else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2014 at 1:11 am
What files are on this drive? The database's data file, log file? What else?
-- LOG files only
Cheers,
- Win
"Dont Judge a Book by its Cover"
August 13, 2014 at 1:20 am
You said files
Other database's log files? Can you move some other file elsewhere temporarily?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply