| Author |
Topic |
|
inbs
Aged Yak Warrior
860 Posts |
 Posted - 2010-01-11 : 02:44:36
|
| i have a DB, the data file size is 4 Giga and the log files is 15 Giga.the recovery model is full.do i need to shrink the log? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-11 : 04:45:10
|
| Let me guess, full recovery and no log backups?Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-11 : 05:16:23
|
| Ensure you have an appropriate backup plan in place . This will be dependant on what recoverability is required. For example , if you have a full backup once a week and daily log backups , then you could include within the process a truncation processJack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-11 : 07:20:20
|
| If you have daily log backups, the log backups themselves will truncate the log and allow the space to be reused. Explicit log truncations are a bad idea, they break the recovery chain of the database. Repeated log shrinks are also a bad idea, just means the log will grow again.--Gail ShawSQL Server MVP |
|
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2010-01-11 : 07:22:46
|
| i have a full backup daily, and log backups every 3 hoursthanks |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-11 : 08:14:50
|
| inbs, what is the initial size you have set for the db?Jack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2010-01-11 : 08:24:53
|
| jackv, i think that you right.i choose in autogrowth by 10 precent. (when my log get 10 Giga,it growth by 1 giga) |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-11 : 08:47:39
|
| I would recommend , you change that to a fixed growth rate in MB - such as 500 MB. Your ideal is to try and forecast the size and set this as your initial size , although this can be difficult , if the usage can't be predicted accuratelyJack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2010-01-11 : 08:57:50
|
| yes that what i do. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-01-11 : 13:37:49
|
quote:
Originally posted by jackv
Ensure you have an appropriate backup plan in place . This will be dependant on what recoverability is required. For example , if you have a full backup once a week and daily log backups , then you could include within the process a truncation process
This is not correct. This invalidates your recovery plan. You should never include a truncate process in your recovery plan. Only emergency truncations should be performed and never, ever scheduled.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-01-11 : 13:38:36
|
quote:
Originally posted by inbs
i have a full backup daily, and log backups every 3 hoursthanks
Since your tlog is much bigger than your MDF file, you should increase the frequency of your tlog backups. We perform our tlog backups every 15 minutes and are able to keep our tlog sizes at a reasonable size.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-12 : 01:42:44
|
| Tara , I think you may have misinterpreted what I've written , I actually meant that the truncation process will be included as part of the log backup process , as opposed to issuing a truncating command when you do a log backup. Jack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-01-12 : 03:26:33
|
| inbs: how big are your log backup files? If they are "small" (compared to the 15GB log file) it means that the majority of the log file is basically a placeholder for larger logging operations. Unless you need the disk space for something else there is no need to truncate the log file...- Lumbagohttp://xkcd.com/327/ |
|
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2010-01-12 : 12:41:24
|
| inbs, if you don't have any disk space issue, just follow your backup scheudle. No need to truncate tlog manually. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-12 : 13:15:33
|
Here's my two pennyworth:At some point your LOG file grew to 15 GB. Perhaps someone did a one-time huge deletion, perhaps your TLog backups failed for a while.Our LOG files tend to be around 150% of our database files, sometimes 200%. So I think yours is bigWe only ever use fixed filesize extension, never percentage. We set a value where an extension will occur once a week, at the most. So we watch how the file grows over time, and set the extension size accordingly.I see NO POINT in having a TLog backup every 3 hours. Change it to 10 minutes (Tara knows way more then me, I'll allow you 15 minutes if you like  )If you backup the Tlog every 3 hours you risk losing 3 hours of data. If you change that to backup every 10 minutes you risk losing 10 minutes of data. I cannot see any reason to risk losing data for a longer period of time. (The total filesize of your backups will be the same, but you will have a lot more files)You need to SHRINK your TLog file as a ONE TIME event. It will then grow back to its working size. Do this AFTER changing the frequency of TLog backups. Take a TLOG backup immediately before the Shrink, and then a FULL BACKUP immediately after the shrink. The Shrink will break your backup chain (I think??) so you won't be able to recover across this point.Keep an eye on the size of the TLog file after you shrink it. If possible record its size hourly for a few days / weeks. See if it jumps up in size at any specific time. Perhaps you have a housekeeping routine that runs at night, or once a week, that creates a huge increase in TLog size? We have a daily deletion that removes 10% of the database; we change the TLOG backup frequency to be every 2 minutes during that interval, otherwise our TLog file grows unnecessarily big.Once your filesizes have stabilised keep an eye on them. Sooner or later someone will do a massive deletion without thinking about the consequences, and your TLog file will grow again  Get them to buy you lunch when that happens |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-12 : 13:39:01
|
quote:
Originally posted by Peter99inbs, if you don't have any disk space issue, just follow your backup scheudle. No need to truncate tlog manually.
I disagree, with the following caveat:If the TLog file is much MUCH larger than it needs to be it is a threat.Restoring a backup to a different database will pre-create the database to the size of the original.Thus restoring to a temporary database (just to check how the data was at some time in the past), or in an emergency trying to squeeze this database onto another server, may mean that the database physically cannot be restored if disk space cannot be made available. I realise that 15 GB is not a lot in this day and age, but for me the principle would still hold. SQL 2005 is better than SQL 2000 in this regard, but there is also a time cost to pre-allocating 15GB of disk space.IMHO it would be better to shrink the database and run it at a more sustainable size.Having said that, if there is a housekeeping routine that will push this database to that size, anyway, then there is nothing that can be done except to tool-up for hosting a database that big. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-13 : 02:22:23
|
quote:
Originally posted by Kristen
Take a TLOG backup immediately before the Shrink, and then a FULL BACKUP immediately after the shrink. The Shrink will break your backup chain (I think??) so you won't be able to recover across this point.
Nah, shrink doesn't invalidate log chains. Only things that do that are explicit log truncation, switch to simple recovery, deleting the log file.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-13 : 02:56:04
|
| "shrink doesn't invalidate log chains"Its obvious now I read you say that! Shrink just shuffles the existing data to the front of the file and/or chops the unused end of the file off; nothing is actually "lost".Thanks for clarifying. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-13 : 07:37:11
|
| Don't worry, lots of people seem to get confused. I suspect it's because of the name of one of the options to Shrinkfile - Truncate_only. Despite it's name, nothing to do with BACKUP LOG ... WITH TRUNCATE ONLY--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-13 : 08:15:45
|
| I've got past the stage/age of worrying, but have arrived at the stage/age of confusion!! |
|
|
|
|
what is ratio between data and log files?