Large Transactions Logs 1

[GShort]

I am very novice when it comes to transaction logs so any help would be great.

Right now I have a Customer DB that is roughly 260 MB and the transaction log is 31GB. The DB is being backup nightly with a full backup just using SQL Server Management Studio.

I can't imagine that this is normal but like I said I am not very good when it comes to understanding the transaction logs.

 

[ptheriault]

the problem is your database is set to full recovery mode and you are not backing up your transaction log. You backup your transaction log seperate from your database. Because you log is so large I would first shrink it.

1. Right click on database and select properties
2. Set recovery model of database to simple. (this will cause the log to truncate on check point)
3. from query analyzer run DBCC SHRINKFILE(log_name, truncateonly)

If you don't know the name of the log run select * from sysfiles.

After you shrink the log file set the database back to full reovery.

Create a backup job for your transaction log.
If you do not need to recover this database to a point in time you can leave it in simple recovery mode. This means that the log truncates when the database checkpoint. I don't really recomemd this because you can't recover to a point in time.

- Paul

- If at first you don't succeed, find out if the loser gets anything.