Odd Filegroup backup behavior

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Odd Filegroup backup behavior

Odd Filegroup backup behavior

Rate Topic

Display Mode

Topic Options

Author

Message

jshurak

Posted Tuesday, September 25, 2012 7:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 11:16 AM
Points: 121, Visits: 640

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?

Post #1364021

jshurak

Posted Tuesday, September 25, 2012 2:37 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 11:16 AM
Points: 121, Visits: 640

Turns out mirroring had been suspended for a while. Broke mirroring, truncated the log, now rebuilding mirroring.

Post #1364288

Bhuvnesh

Posted Sunday, September 30, 2012 11:07 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

jshurak (9/25/2012)

Why is primary file group backup growing like this?

Did you find that why PRIMARY group increasing at sudden?

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1366273

jshurak

Posted Monday, October 01, 2012 6:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 11:16 AM
Points: 121, Visits: 640

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.

Post #1366522

Joy Smith San

Posted Tuesday, October 02, 2012 11:25 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 1,370, Visits: 2,289

jshurak (10/1/2012)

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.

Post #1367195

« Prev Topic | Next Topic »

Permissions