September 25, 2012 at 7:11 am
We have a particularly large database that is partitioned across multiple filegroups. We have nightly file group backups on the read write file groups and bi weekly backups on the read only groups. We will be updating this server to 2008 in October and as a run through, the read only file groups have been made read write (just this past sunday) to allow SQL Server to perform whatever updates it needs to perform.
Our backups explicitly define which file group is to be backed up:
Backup database mydatabase FILEGROUP = 'PRIMARY'
to disk = 'F:\BackUp\Full\mydatabase\FG PRIMARY.bak' WITH INIT;
The current file size of the primary filegroup is 8579MB and 8012MB used space. Looking at the historic
archive of our backups, the backup file for the primary file group has grown substantially. on then 9th, the file size was 9.81 GB, last night it was 58.29GB. Its been growing steadily daily, until last night when it doubled in size. (9/23 was ~25.81). Why is primary file group backup growing like this?
September 25, 2012 at 2:37 pm
Turns out mirroring had been suspended for a while. Broke mirroring, truncated the log, now rebuilding mirroring.
September 30, 2012 at 11:07 am
jshurak (9/25/2012)
Why is primary file group backup growing like this?
Did you find that why PRIMARY group increasing at sudden?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 1, 2012 at 6:32 am
Yes, The database is a principal in mirroring. The mirroring session had actually been suspended for a few days (oops), transactions were being hung up in in the transaction log. They weren't able to truncate. Full backups of the filegroups must have included those transactions. I dropped the mirroring session, backed up the log, clearing the transactions. The backup sizes dropped back down to normal sizes and mirroring was re-established.
October 2, 2012 at 11:25 am
jshurak (10/1/2012)
Yes, The database is a principal in mirroring. The mirroring session had actually been suspended for a few days (oops), transactions were being hung up in in the transaction log. They weren't able to truncate. Full backups of the filegroups must have included those transactions. I dropped the mirroring session, backed up the log, clearing the transactions. The backup sizes dropped back down to normal sizes and mirroring was re-established.
So basically there were some redo errors in the mirror is it ?
Is the mirroring reconfigured ? I would suggest to setup a witness and alerts to get notifications.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply