Transaction log is full - how to fix this?

Hi Natasha,

We do not back up our test database. We refresh it every other week from live (soon to be every week), so our focus on backups is on the live database. Likewise, I set the recovery mode on our test database to simple during each refresh. My opinion is there is no reason for transaction logging on the test side – we have no reason to ever need to recover at a point in time on the test side. (That may not be true of every organization, but is the case for us.) Doing that keeps it simple (no pun intended) and eliminates issues, like transaction logs filling up.

The key thing is you really only need Full recovery model on databases that you need to have the ability to restore to a specific point in time and/or where database backups are not adequate.  For example, we do transaction backups every 30 minutes on our live database as it is unacceptable to us to have to lose a partial day of transactions if there is a major failure. For databases, like test databases, it’s often best to use Simple recovery model. Likewise, if you use the Full recovery model, you need to implement transaction backups of some sort. It is not really a best practice to use methods, like shifting to simple then back to full, on a routine basis to truncate the transaction log.

I hope this helps.

Thanks,

David

Just to add to this: 

Under Properties > Options the Recovery model is set to Full

Under General the last database backup is 12/2/2011 (last time we refreshed). Under Transaction Log shipping everything is greyed out so I guess that means it’s not actually doing anything.

On a side note, how do others manage their Test database in terms of backups, if at all?

Hi Natasha,

We do not back up our test database. We refresh it every other week from live (soon to be every week), so our focus on backups is on the live database. Likewise, I set the recovery mode on our test database to simple during each refresh. My opinion is there is no reason for transaction logging on the test side – we have no reason to ever need to recover at a point in time on the test side. (That may not be true of every organization, but is the case for us.) Doing that keeps it simple (no pun intended) and eliminates issues, like transaction logs filling up.

The key thing is you really only need Full recovery model on databases that you need to have the ability to restore to a specific point in time and/or where database backups are not adequate.  For example, we do transaction backups every 30 minutes on our live database as it is unacceptable to us to have to lose a partial day of transactions if there is a major failure. For databases, like test databases, it’s often best to use Simple recovery model. Likewise, if you use the Full recovery model, you need to implement transaction backups of some sort. It is not really a best practice to use methods, like shifting to simple then back to full, on a routine basis to truncate the transaction log.

I hope this helps.

Thanks,

David

Just to add to this: 

Under Properties > Options the Recovery model is set to Full

Under General the last database backup is 12/2/2011 (last time we refreshed). Under Transaction Log shipping everything is greyed out so I guess that means it’s not actually doing anything.

On a side note, how do others manage their Test database in terms of backups, if at all?