Transaction log full

Question

Post reply

Transaction log full

Jayanthi-243119

SSC Rookie

Points: 25
More actions
November 13, 2005 at 10:47 pm

#169482

I am a newbie. Matter most urgent. Can anyone tell me how to backup a transcation log. I am getting the error maessage log full, backup the transaction log. Any help would be appreciated very much.

Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ajay_k73 SSC Veteran Points: 217 More actions · Answer 1

DBCC SHRINKDATABASE('database',10,TRUNCATEONLY)

BACKUP LOG database WITH TRUNCATE_ONLY

Sarang Bobde Mr or Mrs. 500 Points: 534 More actions · Answer 2

This is very interesting solution , but works only if you have rights for Enterprise manager.

Follow these steps:

1) Right click on desired Database

2) Go to All Task -- Backup

3) Select Transaction log from the options

4) Back up the database as desired location.

Once the backup has been done you will see that the transaction log has been backed up and the space is freed up.

This is simple and trusted technique.

fhanlon SSChampion Points: 13131 More actions · Answer 3

If you don't backup you transaction log on a periodic basis you probably should set your database to a recovery model of 'simple' rather than 'full' so that the transaction log does need backups

Francis

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

It's not quite as simple as just changing the recovery model to SIMPLE to avoid doing backups. "FULL" recovery allows you to recover lost or corrupted data from a point in time between your full backups. "SIMPLE" does not give you that ability, you're limited to the last full backup. Consider the importance of each particular database to determine the best backup strategy for each DB. There are thousands of posts on this topic for more detailed info .... as well as Books Online (BOL).

fhanlon SSChampion Points: 13131 More actions · Answer 5

To clarify. I agree that you shouldn't change the recovery model to simple in order to avoid backups. What I said was if you are not going to backup the transaction log then change the recovery model. Of course the decision to not back up the transaction log should be based on the business need for recovery. I suspect that in this case full backups may not be happening on a regular basis either. But this was never said. So perhaps full backups are being taken every day and there is just a problem with transaction logs filling up. If this is the case then changing the recovery model is a good idea. If this is not the case then the recovery model should not be changed and periodic transaction log backups should occur. But BACKUP LOG database TRUNCATE_ONLY is not the command to use. The transaction log should actually be saved somewhere for possible use. See BOL for the actual syntax or use a database maintenance plan. There are lots a great articles on this site about creating an appropriate strategy for backups.

Francis

Jayanthi-243119 SSC Rookie Points: 25 More actions · Answer 6

Thanks for the response. We used the sp_dboption checkpoint - not sure if that was essential. Later we backed up the transaction log & executed DBCC shrinkdatabase also.

Everything going fine now. Will try your solution next time we encounter a situation like this.

Regards

Jayanthi

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 7

Sarang Bobde,

This is very interesting solution , but works only if you have rights for Enterprise manager.

your statement quoted above is misleading ,

what is rights for Enterprise manager ?

Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com