Log Backup Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Log Backup Error

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2006-12-21 : 12:53:56

Hello

I am having problem with the hourly log backups. I have scheduled the log backups from morning till evening.

Today , I have truncated the log file as it has grown too huge around 40gb , where as the original datafile is just 10gb.

Immediately the hourly log backup failed with the error , " BACKUP LOG cannot be performed because there is no current database backup. "

The database backups are performed everynight.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 13:01:00

Well you truncated the log file! That's the problem. You must perform a full backup after you truncate the log file.

Stop truncating the log file! I don't know how many more times we have to tell you this. Buy more disks if free disk space is an issue.

Tara Kizer

vaddi
Posting Yak Master

145 Posts

Posted - 2006-12-21 : 13:08:42

Thanks Tara for the suggestion.

Sorry for irriating .

I remember you told me not to truncate the files , but on one of the servers the disk space is too low and it would take another week for the disk space to be added due to some procedural issues.

That is why I am forced to truncate the log files everytime.

Thanks once again.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 13:17:40

So then stop reindexing the database. That will stop your tlog from growing out of control.

You are invalidating your backup plan by truncating the transaction log.

1. Disable the job that performs the reindex (perhaps you are using maintenance plan, so it's called optimizations)
2. Modify your backup plan so that the truncate is no longer performed
3. Do a one time shrink of the transaction log, have the file be 25%-50% of the size of the MDF
4. Buy more disks so that you can enable the reindex job in the future

Tara Kizer

vaddi
Posting Yak Master

145 Posts

Posted - 2006-12-21 : 13:45:38

Thanks for the suggestion Tara. I will make the changes accordingly and add the disk space as soon as possible.

Thanks

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-21 : 17:59:43

quote:
Originally posted by tkizer

So then stop reindexing the database. That will stop your tlog from growing out of control.

You are invalidating your backup plan by truncating the transaction log.

3. Do a one time shrink of the transaction log, have the file be 25%-50% of the size of the MDF

Tara Kizer

Tara,
How often shoudl i perform shrking the transaction log. I have a same problem. Our log file got grow too big.. even though we are doing an hourky trnasaction back up...we do perform the rebuilding index job every week

DO i have to do a full back up right after shirking the transaction log file?

Is it a good idea to set the max log size for the production DB , so it prevents from growing the transaction log file out of control...

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 18:08:11

You should never shrink the transaction log on a scheduled basis. The only time you should shrink it is if you know the database no longer needs it or if you are extremely tight on free disk space.

No to having to run backup after shrink.

We don't set the maximum size. We monitor free disk space with tools.

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-21 : 18:28:37

quote:
Originally posted by tkizer

You should never shrink the transaction log on a scheduled basis. The only time you should shrink it is if you know the database no longer needs it or if you are extremely tight on free disk space.

Tara Kizer

I can not add more hard diask spaces..

this is the production dabase server. we are almost out of the space because of the husge transaction log files... We are doing a full backup every day and hourly backup every hour...

So, what is the best way to keep the Transaction log size in control in the mimimal disk space and without hurting the database?

if i do the full backup everyday, why can't i just truncate the transaction log right after the full backup? i don;t need the transaction log from the day one database was created right? Can you explain to me wht I should not truncate the trnasaction log?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 18:32:29

The best way is to run DBCC INDEXDEFRAG instead of DBCC DBREINDEX. Or not run either. Check out my blog for details:
http://weblogs.sqlteam.com/tarad/archive/2006/04/11/9585.aspx

You are creating huge performance problems due to the shrinking.

No you can't truncate the log after you back it up! That invalidates the transaction log chain. You might as well use SIMPLE recovery model if you do this.

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-21 : 18:38:06

"You are creating huge performance problems due to the shrinking."

I know i keep asking you the same questions..I am just trying ot figure out the best way to resolve this issue ( without adding more disk spaces)..very frustrating...

So, the shrking Log file should be the one time process? what if the log grow back tothe same size tomorrow, do i have to shring the log file again?

Is it a good idea detaching the DB and delete the log file and attach it back, so the log file get created from the scrach?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 18:40:03

Just stop running the reindex. It should solve your problems. Please check out the link in my blog entry. It'll explain when you should run indexdefrag as compared to dbreindex. Most environments don't even need either running.

How big is your database?

No that's not a good idea to detach etc... You only do this when you can't shrink it down.

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-21 : 18:51:32

quote:

How big is your database?

the main database is only 7GB but log file is 10 GB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 18:56:25

Then you definitely don't need to run dbreindex. Please check out the link.

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-21 : 19:00:07

quote:
Originally posted by tkizer

Then you definitely don't need to run dbreindex. Please check out the link.

Tara Kizer

thank you tara..

I will just keep monitoring the disk space and if the trnasaction log file size > 120 % of actucal database file then i will shrkin the transaction log file manually( not the scheduled job).
If i can not shrink the transaction log file then i will do a full back up and detatch the database and delete the log and attach it back...

does it sounds ok?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 19:04:10

It doesn't sound okay to me. Just turn off the reindex, do a one time shrink, and you're done. The tlog shouldn't grow very much, most likely not even beyond 5GB.

If you don't believe me, read the Microsoft article mentioned in my blog entry! I don't know how many more times I need to say that. Well I know how many more times, zero. I'm done repeating myself.

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-21 : 20:09:16

should i do the transaction log back up every 15 min or one hour? whichone will keep the transaction log file size smaller?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 20:25:24

We do ours every 15 minutes due to the criticality of the data.

The size of the transaction log backup will be smaller if you do it more often. However, if you added them all up, they'd equal the same. Here's an example:

Hour1 - 5MB
Hour2 - 6MB
Hour3 - 4MB

15minute1 - 1MB
15minute2 - 2MB
15minute3 - 1MB
15minute4 - 1MB
15minute5 - 2MB
15minute6 - 2MB
15minute7 - 1MB
15minute8 - 1MB
15minute9 - 1MB
15minute10 - 1MB
15minute11 - 1MB
15minute12 - 1MB

Both sets cover 3 hours. Adding them up though, they each total 15MB.

Your actual transaction log might be more manageable the more often you back it up.

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-21 : 20:41:16

thanks tara..
I got one more question. If I set the max log file size, SQL server will automatically shirk the size of log file or it;s going to blow up the DB if the size of transactions are greater than the max trnasaction log size?

I would like to set the max transaction log size for the test DB, so it does not get bigger than the max size..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 20:44:23

SQL Server does not automatically shrink databases unless you have the autoshrink option turned on, which is a very bad idea. If you fill up either the data or the log file and it can't grow anymore, it'll throw an error and keep throwing the error until it's fixed.

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-21 : 21:02:00

quote:
Originally posted by tkizer

SQL Server does not automatically shrink databases unless you have the autoshrink option turned on, which is a very bad idea. If you fill up either the data or the log file and it can't grow anymore, it'll throw an error and keep throwing the error until it's fixed.

Tara Kizer

you meant it will keep throwing the error with auto shrik option?
If i have both max log file size set up and auto shrink turned on , would it still throw an error?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 23:00:55

No, once you fill it up, it will keep erroring until you free up some space.

And yes it could still throw the error.

Don't ever turn autoshrink on in a production environment. That's about the worst thing one could do for performance reasons.

Tara Kizer

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources