Transaction Log Fills During Backup

[gharabed]

I hav a fairly large (~2 TB) database that I am trying to backup. After about 5 minutes running the backup, it fails and the database goes into emergency mode because the transaction log is full. Why would the transaction log fill during a backup? I know there is no other activity going on other than the backup so I'm sure it is because of the backup. Is the only way around this to backup the transaction log every couple of minutes?

 

[gharabed]

I should also say that I am doing a filegroup based backup. It is my understanding that when I file/filegroup based backup is done, the transaction log must also be backed up. How is this transaction log backed up in conjunction with the filegroup so that I don't receive this transaction log full error?

 

[Catadmin]

Are you doing the backup through the EM/SSMS console or via T-SQL?

If T-SQL, you can use the Backup Log statement (see Books Online) to backup the log before you back anything up. Otherwise, you have to choose a Log backup before you do your filegroup backup.

BTW, You also want to check the logical drives where the Transaction Log and the TempDB are located. If you don't have free space about half the size of your DB on those logical drives, this might also be the problem. Is there anything you can move to another logical drive or delete?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"