Very big Transaction Log file

Djou2424

HI,

I'm trying to understand why the transaction log of a database is getting very big.
It is currently at almost 80 gigs.
The database itself is not that big.
mdf file is at 4 gigs and ndf file is at 15 gigs but the ldf file is at 80 gigs

They are running Nav 2009 SP1 classic on SQL
Any idea what I should be looking at?

-Julie

Luc_VanDyck

Did you create a Maintance Plan to backup your database regularly? After a backup, the logfile can be truncated.

Marije_Brummel

Or just set the recovery model to simple and it won't grow that much anymore.


http://www.youtube.com/watch?v=fiO_V0TZd-s

Djou2424

A backup is run everyday and they would like to keep the recovery model as FULL

Yashojit_Pandhare

Check for Truncate and or Shrink SQL Server transaction log file.
You may need to do this periodically if your recovery model is set to full.

Marije_Brummel

Why do you want to keep the recovery model full?

Do you have transaction log backups?

What is your disaster recovery scenario? Do you have it written on paper? What are the recovery steps? How often do you practice doing it.

If there is no one who knows how to append the t-log backups it is false security.

All I want to say is; in case of a disaster, if it takes you 4 hours to find out how to restore your transaction log it is easier to retype all the data manually.

davmac1

Hi Mark,
I respectfully disagree with you.
It will be far cheaper for them to find someone who really knows SQL Server and NAV to help them set up a decent backup plan and make sure their SQL Server is operating efficiently.
The money they spend now will be a tiny fraction of the cost of recovering from a disaster.
Otherwise they are like a person who has driven a car, suddenly driving down the road in a 18 wheeler and hoping for the best.

Alex_Chow

Djou2424 wrote:

A backup is run everyday and they would like to keep the recovery model as FULL

The transaction log backup should be done every hour.

Marije_Brummel

Hey David,

I agree with you.

All I want to say is that you should make a decision. Either make a proper recovery plan in case of a disaster or accept that in case of a recovery you only have your overnight backup.

Most customers only start thinking of recovery when a disaster strikes. This should be done way earlier and practiced every once in a while.

If you don't practice a recovery frequently you'll find out that a system that has been setup perfecly like a while ago something has changed that broke your plan.

If you don't have in-house knowledge to periodically do this you should ask your vendor to periodically do this.

This does not only apply to SQL databases. Also to file and exchange backups. Often I've experienced at customers that their vendor promised a fail safe backup system and when it comes to a disaster it either takes them days to recover the files/emails/database from backups or they cannot recover them at all because at that time they discover that "somthing" has been misconfigured.

That is why you should periodicaly practice recovery. Or let someone else do this.

DenSter

Djou2424 wrote:

A backup is run everyday and they would like to keep the recovery model as FULL

You need to understand what the different recovery models do, and you need to understand how to set up a proper backup plan.

This is NOT meant as RTFM, but to show you where you can get information like this. SQL Server has probably the best help system of all MSFT products. Even though it sits on your local computer, it's called "Books Online", or BOL. It includes offline help articles and MSDN type information, but it also grabs information from online sources.

Go to Start, All Programs, SQL Server, Documentation and Tutorials, and Books Online should be right there. If it is not, then you'll have to get the installation disk and install it.

Click 'Search' and type in "backup strategies". You should see an article called "Introduction to Backup and Restore Strategies in SQL Server". Read this carefully, it will introduce you to the concepts. If you want to drill down, search for "Recovery Model". Search for "Transaction Log".

Check out my YouTube clip about maintenance, I think there's a section about backups, although it covers just the basics.

DenSter

Oh and listen to Mark. Answer the questions he asks. Set up a proper plan. Probably most important: PRACTICE RECOVERY

bbrown

Start by defining an acceptable recovery plan for your business. Then implement a backup plan (or other things) to support it. What works for others may not be right for you. And yes, practice it. Finding out, during a real emergency, that it doesn't quite work as expected is not a good experience.

Your recovery plan should address 2 primary questions:

1. How much data are you can you afford to lose? This helps determine the type and frequency of backups.

2. How long can the recovery take? This is the question I think people sometimes forget about. You may have the backups but could stil be looking at several hours to recover.