SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Log Backup Error
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

vaddi
Posting Yak Master

145 Posts

Posted - 12/21/2006 :  12:53:56  Show Profile  Reply with Quote
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

USA
37291 Posts

Posted - 12/21/2006 :  13:01:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 12/21/2006 :  13:08:42  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37291 Posts

Posted - 12/21/2006 :  13:17:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 12/21/2006 :  13:45:38  Show Profile  Reply with Quote
Thanks for the suggestion Tara. I will make the changes accordingly and add the disk space as soon as possible.

Thanks
Go to Top of Page

jung1975
Aged Yak Warrior

USA
503 Posts

Posted - 12/21/2006 :  17:59:43  Show Profile  Reply with Quote
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





Edited by - jung1975 on 12/21/2006 18:06:34
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37291 Posts

Posted - 12/21/2006 :  18:08:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

jung1975
Aged Yak Warrior

USA
503 Posts

Posted - 12/21/2006 :  18:28:37  Show Profile  Reply with Quote
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?







Edited by - jung1975 on 12/21/2006 18:30:26
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37291 Posts

Posted - 12/21/2006 :  18:32:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

jung1975
Aged Yak Warrior

USA
503 Posts

Posted - 12/21/2006 :  18:38:06  Show Profile  Reply with Quote
"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?







Edited by - jung1975 on 12/21/2006 18:39:03
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37291 Posts

Posted - 12/21/2006 :  18:40:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 12/21/2006 18:41:32
Go to Top of Page

jung1975
Aged Yak Warrior

USA
503 Posts

Posted - 12/21/2006 :  18:51:32  Show Profile  Reply with Quote
quote:


How big is your database?




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


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37291 Posts

Posted - 12/21/2006 :  18:56:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
Then you definitely don't need to run dbreindex. Please check out the link.

Tara Kizer
Go to Top of Page

jung1975
Aged Yak Warrior

USA
503 Posts

Posted - 12/21/2006 :  19:00:07  Show Profile  Reply with Quote
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?


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37291 Posts

Posted - 12/21/2006 :  19:04:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 12/21/2006 19:04:40
Go to Top of Page

jung1975
Aged Yak Warrior

USA
503 Posts

Posted - 12/21/2006 :  20:09:16  Show Profile  Reply with Quote
should i do the transaction log back up every 15 min or one hour? whichone will keep the transaction log file size smaller?


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37291 Posts

Posted - 12/21/2006 :  20:25:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 12/21/2006 20:44:51
Go to Top of Page

jung1975
Aged Yak Warrior

USA
503 Posts

Posted - 12/21/2006 :  20:41:16  Show Profile  Reply with Quote
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..


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37291 Posts

Posted - 12/21/2006 :  20:44:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

jung1975
Aged Yak Warrior

USA
503 Posts

Posted - 12/21/2006 :  21:02:00  Show Profile  Reply with Quote
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?


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37291 Posts

Posted - 12/21/2006 :  23:00:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000