General discussion

Locked

SQL Server Transaction Log Removal

By tapan_maniar ·
I have seen on my server the transaction log file which is in size 8 GB on SQL Server. I want to get rid of this file. Can anybody help me on that?
Thanks.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

In your maintenance plan

by LordInfidel In reply to SQL Server Transaction Lo ...

Open up your mgr and go to your maint plans.

You should have them set up, I hope.

When you do your mainenance plan there is an option with the trans logs to remove them after x period of time.

This is how I set up my db's.

Each day they are backed up full. They are kept on the drive for 2 days. So at any given time you will have 2 days of backups.

Every 10 minutes, trans logs are written. These are then overwritten every day.

Why?

You only need to commit your trans logs after your last full backup. So if you are backing up full daily, you only need to keep the most current set of trans logs.

So if you last backup was at 2 am. And you server goes down at 10 am. You restore from the 2 am backup and then commit the trans logs from after the backup to just before it went down.

Then for securities sake, I always backup the trans logs and db backup to tape on a daily basis.

I use the incremental backup to tape so that it only grabs the latest backup.

Now you should be backing up to disk on a seperate controller on your DB, or at least to a different partition. But it should be to disk so that you can recover quickly.

Collapse -

Use DETACH function

by puppybreath In reply to SQL Server Transaction Lo ...

If you want to delete the log file and understand the ramifications, the easiest way is to use DETACH to detach the database, delete the log file, and then use ATTACH to re-attach it. You can find the correct syntax in the Books On Line, or if you are using SQL 2000, you can use the options in the GUI under "tasks".

I would strongly suggest that you perform a complete backup of your db before trying this so you can recover your work if anything goes wrong.

Back to IT Employment Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums