Transaction log is full - how to fix this?

You need to shrink the log.  I have had it do this to me in LIVE when backups weren’t working.  We have used these instructions, but we also have increased the size of the log. 

TransactionLogFullError

Error: Tessitura won't load - transaction log is full.

1. Change recovery model from 'Full' to 'Simple'

2. shrink impresario database

3. change recover model from 'Simple' to 'Full'

use this script to do this:

USE [master]

GO

ALTER DATABASE [impresario] SET RECOVERY SIMPLE WITH NO_WAIT

GO

ALTER DATABASE [impresario] SET RECOVERY SIMPLE

GO

USE [impresario]

GO

DBCC SHRINKDATABASE(N'impresario' )

GO

USE [master]

GO

ALTER DATABASE [impresario] SET RECOVERY FULL WITH NO_WAIT

GO

ALTER DATABASE [impresario] SET RECOVERY FULL

GO

Trudy Guest,

ArtTix Systems Administrator

801.323.6969

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Natasha Purkiss
Sent: Wednesday, August 29, 2012 9:33 AM
To: Trudy Guest
Subject: [Tessitura Technical Forum] Transaction log is full - how to fix this?

Hi everyone,

It’s been a while since I used out Test system and upon trying to log in today I got the error “The transaction log for database ‘impresario’ is full”.

I can see allot of conversation in the forums about transaction logs but seems I know very little about this topic I don’t really know where to start. Can someone point me in the right direction or have any documentation on how to solve this? I don’t know what kind of backup our Test  database even has, or where to find this.

I downloaded the free trial of RedGate SQL Script Manager that I saw recommended by a few but not sure where to go from there.

Thanks for any help!

Natasha

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

You make sure you are on the TEST box before your proceed. 

Trudy Guest,

ArtTix Systems Administrator

801.323.6969

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Natasha Purkiss
Sent: Wednesday, August 29, 2012 10:05 AM
To: Trudy Guest
Subject: RE: [Tessitura Technical Forum] Transaction log is full - how to fix this?

Thanks Trudy!

Just one question, how do I ensure it points to Test? If I'm using the Master db and then saying 'impresario' where do I specify the test version of impresario? 

We have a couple of jobs set up that does a backup of test, one that shrinks the database, and one that deletes the oldest backup.  We keep backups for 3 days. 

Trudy Guest,

ArtTix Systems Administrator

801.323.6969

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Natasha Purkiss
Sent: Wednesday, August 29, 2012 9:54 AM
To: Trudy Guest
Subject: Re: [Tessitura Technical Forum] Transaction log is full - how to fix this?

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,

Regarding backups for Test, I do create a daily backup.  All my Dev work happens on Test, and I don’t want to lose anything in progress there.  However, I have the Recovery model set to Simple so managing the trans log isn’t an issue.

Good luck!

Lori

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?