Database log size a problem

Question

Database log size a problem

jollyguy4all

SSC Veteran

Points: 270
More actions
September 21, 2003 at 11:52 pm

#159885

Hi,
I have a database which has the total size of around 4 GB. When i inspected the data file it was only 655 MB, while the log file was around 3.5GB. How important is the log file? Can it be truncated to save hard disk space and if yes how can we do that?
I have a subscriptions database which holds a users table and a subscriptions table. I do some batch updates sometimes when bulk changes are required.
Please advise.
Thanks and regards
Hitendra

Viewing 11 posts - 1 through 10 (of 10 total)

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

mccork SSC-Insane Points: 22288 More actions · Answer 1

Hitendra,

When you have databases in full recovery mode (which it appears you do), it's important to schedule regular transaction log backups. This backs up and then truncates the inactive portion of the log. Easiest way to accomplish this is through a maintenance plan.

Cheers,

- Mark

Cheers,
- Mark

jollyguy4all SSC Veteran Points: 270 More actions · Answer 2

What is a full recovery mode?

And how does the transaction log help?

Sorry for my ignorance but i really think there this is something important that i am missing.

We have a users master table and a subscriptions table referencing to the users in the master, different labels file we have printed in the past months. We take database backups on a daily basis and what kind of recovery do you forsee in such an environment.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 3

Wondering if there maybe an open transaction hanging around. When last did you rebooted the server

jollyguy4all SSC Veteran Points: 270 More actions · Answer 4

the server boots up everyday atleast once as it has wingate running on it and it keeps on hanging.

SQLBill SSC Guru Points: 51440 More actions · Answer 5

The transaction log is only important if you want to be able to do 'complete' restores or 'point-in-time' restores.

First, you need to be doing regular backups. You should have at least one full backup. Then you do transaction log backups. The TL backups apply to the last full backup.

Let's say you only do a Full backup each day. It's done at midnight. Then your database crashes at noon and you need to restore it. All you have is a full backup of the data upto midnight the night before. Everything from midnight to noon is lost.

Now lets say you are doing transaction log backups every 6 hours along with the midnight fullbackup. Same scenerio...database fails at noon. You restore the full backup using the WITH NORECOVERY clause and then restore the 0600 transaction log backup (also WITH NORECOVERY) and then the 1200 log backup (if it was done). At the most you will lose the data from 0600 to noon, if a log backup hadn't been done.

Third scenerio...you lose the database at 1201. You know it's a bad command that did it and the command started running at 1150. You restore the fullbackup (WITH NORECOVERY), the 0600 log backup (WITH NORECOVERY), and the noon log backup (WITH STOPAT 11:49). That recovers you up to the time the bad command was entered.

How often should you do full backups? Depends on how important your data is, how large the databases are, how quickly you want to be able to restore (the less backups to restore the quicker it will be).

I do a full backup each day, and a log backup every 4 hours.

-SQLBill

jollyguy4all SSC Veteran Points: 270 More actions · Answer 6

That was really a good explanation. I also have scheduled a daily backup at midnight. When you say taking log backups every 4 hrs. Do you overwrite the same log backup evertime the log backup runs or do you manually save the log backups seperately for each day before the whole database backup is done?

My Network administrator complains that the backup size is huge as he takes a backup on a tape drive and it takes lot of time to complete around 4GB of backup. But my actual database is only 655MB and the rest is the log file.

I understand that if i backup the log seperately then it will reduce the size of the log file. But then wont the backup file of the logs which i have to preseve or store on the tape disk as well will have the same size? Sorry but i am bit confused here.

Also why do we use the option (NO Recovery) while resotring?

What i understand is that log backups can come to use when you issue a bad command or if the hard disk crashes (and provided the backup is on some other drive on the network)

Am i right?

Thanks and regards

Hitendra

SQLBill SSC Guru Points: 51440 More actions · Answer 7

I created two log backup jobs. The first one of the day (4 hours after the Full Backup) runs using the WITH INIT command. That initializes (overwrites) the backup file. Then the second job is scheduled to run every 4 hours after the previous job. It uses the WITH NOINIT command and appends to the backup file.

That way at the end of a day, I have a FULL Backup and one LOG backup file. The log backup file contains three backups.

I then use Veritas Backup Exec to copy the files to tape.

How long is 'long'? It doesn't take me long to backup my data to tape. When my database was 150+GB it only took 4 hours.

The thing with the log files is that you aren't backing them up now. So they are growing quite large. Once you back them up the first time, after that they will be smaller backups.

NO RECOVERY:

Once you tell SQL Server that the database is recovered (WITH RECOVERY, which is also the default), no other backup files can be added (Restored). WITH RECOVERY tell SQL Server "I am done recovering/restoring this database and I don't have any more backups to restore."

Yes you are right about when log backups become useful.

Should you backup the log? That depends, here's some questions to ask yourself:

1. Are there a lot of transactions on the database? (UPDATES/INSERTS/DELETES)

If yes, then backing up the Log will be a benefit.

2. Is your database critical?

BACKUP the Log files!

3. Might you need to restore to a point-in-time?

Using log backups is the only way you can do that.

If you decide you don't care about or need to back up the logs, then change the Recovery Mode to SIMPLE.

Hopefully I answered all of your questions.

-SQLBill

jollyguy4all SSC Veteran Points: 270 More actions · Answer 8

Thanks mate, that was a great help. I am pretty clear with the backup concepts now. Is there a way to read the log files. I mean if i just issued a bad command and if there is a way that i could read the log files and identify which records were affected instead of restoring the backup and applying the log files.

Sorry to keep firing new queries in every other post.

Thanks

Hitendra

happycat59 One Orange Chip Points: 29389 More actions · Answer 9

MS does not provide any easy way to do this.

Have a look at "Log Explorer" from Lumigent (http://www.lumigent.com) - it is able to do this and create "undo" scripts if you need.

Cheers

Stephen

rameshts999 SSC Journeyman Points: 98 More actions · Answer 10

Hi

You just take the BAckup of the log File. The log File will get truncated automatically. You just see to it that the AutoShrink option is ON. Otherwise you need to Shrink the log. You can do it in Enterprise Manager itself.

quote:
Hi,
I have a database which has the total size of around 4 GB. When i inspected the data file it was only 655 MB, while the log file was around 3.5GB. How important is the log file? Can it be truncated to save hard disk space and if yes how can we do that?
I have a subscriptions database which holds a users table and a subscriptions table. I do some batch updates sometimes when bulk changes are required.
Please advise.
Thanks and regards
Hitendra