April 24, 2008 at 5:16 pm
Hello all,
I am having a strange problem with a Transaction log backup which runs on a 2005 database every two hours. Due to a high volume of change in the database the TLog grows at very fast rate and will fill a its disk in no time. I created a maintence job which backs up the TLog every 2 hours. It is also supposed to delete any file older than a day, which does not seem to work either. The backup runs every two hours for a few days then stops with the following error:
Executing the query "BACKUP DATABASE [SSP_Search_DB] TO DISK = N'F:\\Backup\\Special_TLog_Backup\\SSP_Search_DB\\SSP_Search_DB_backup_200804240600.bak' WITH NOFORMAT, NOINIT, NAME = N'SSP_Search_DB_backup_20080424060001', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "Could not clear 'DIFFERENTIAL' bitmap in database 'SSP_Search_DB' 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.
Not sure what they mean by insufficient resources as the destination drive has plenty of space for the backups. Naturally when the TLog backup stops the TLog grows until the disk is full. Also, the cleanup job does not seem to be working. Has anyone run into this problem before? Any help you can give will be greatly appreciated. Thanks in advance.
Ken
April 25, 2008 at 4:47 am
Hi,
The error description shows that the problem is not with the transactional log backup, the error says
BACKUP DATABASE and not BACKUP LOG.
Please check wether transactioanl log backup is coinsding with the full database backup.If possigble try reducing the transactional log backup schedule to 1 hour.
April 28, 2008 at 11:53 am
Ok thanks for you reply, I think I got it fixed, I had to recreate the job and it seems to be running fine now.
Ken
May 7, 2008 at 9:48 am
I have exactly the same error but my transaction log backup was already an hourly backup. I have removed log backup but database is still growing at a significant raete.
May 13, 2008 at 2:18 pm
We're having the same problem. And, we're finding that the database is growing by 3% per day (or about 20% a week), and it is two tables ... the ones that store Index Server content. We've only been trending it for the last 30 days with Idera Diagnostic Manager, but it really looks like a MOSS setting or something under the Sharepoint hood. Any ideas?
May 13, 2008 at 3:17 pm
My system is also a sharepoint system that was growing the tlog so fast it filled the disk in about 2 days! A 20 gig tlog file was not unusual! I ended up creating a 2005 maintenance plan to back up the transaction log every hour and added to that job a cleanup task to delete files older than a day (as far as I can tell 24 hours is the earliest a file can be deleted using the maintenance plan) as a result I have 24 tlog backups. I then created another maintenance plan which backs up the database itself once in a 24 hour period (I have the database backup run between the time the tlog backup runs). What this has done has kept the space used constant with very little growth. If your problem is wild tlog file growth this approach has thus far worked for me. Hope this helps!
Ken
May 13, 2008 at 3:18 pm
re_dean_illumina (5/13/2008)
We're having the same problem. And, we're finding that the database is growing by 3% per day (or about 20% a week), and it is two tables ... the ones that store Index Server content. We've only been trending it for the last 30 days with Idera Diagnostic Manager, but it really looks like a MOSS setting or something under the Sharepoint hood. Any ideas?
Are you using MOSS or WSS? We have WSS set up in development using the Microsoft Internal Database (aka SQL Server Embedded Edition). The only backups we could do through WSS are full and differential backups. Unfortunately, the content database was using the FULL recovery model so the transaction log was not getting cleaned up. Finally figured out how to connect SSMS to MID, and set the database to use the SIMPLE recovery model, then shrunk the transaction log. Freed up 4 GB of disk space, and we haven't had space issues since.
I'd look at your backup processes and what recover model the MOSS/WSS databases are using.
😎
February 9, 2012 at 11:36 am
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.
Hope this works for all
February 9, 2012 at 11:57 am
Nataraja Sidgal (2/9/2012)
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.
Hope this works for all
FYI, this is a 3+ year old thread.
February 9, 2012 at 12:07 pm
Nataraja Sidgal (2/9/2012)
Change the recovery model from simple to full or full to simple - this will reset all the bitmaps.
Changing recovery models won't affect the differential bitmaps, the mechanism of full and differential backup is independent of recovery models.
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply