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 performed3. Do a one time shrink of the transaction log, have the file be 25%-50% of the size of the MDF4. Buy more disks so that you can enable the reindex job in the futureTara 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 MDFTara 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 weekDO 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.aspxYou 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 - 5MBHour2 - 6MBHour3 - 4MB15minute1 - 1MB15minute2 - 2MB15minute3 - 1MB15minute4 - 1MB15minute5 - 2MB15minute6 - 2MB15minute7 - 1MB15minute8 - 1MB15minute9 - 1MB15minute10 - 1MB15minute11 - 1MB15minute12 - 1MBBoth 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 |
|
|
Next Page
|