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.
| Author |
Topic |
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
 Posted - 2010-07-21 : 03:23:02
|
| Hi,I've a database with 300Gb, and the log file is 96 Gb. as it is eating the drive space, i got an alert, and i took the log backup, and shrinked the logfile. NO USE. again i took the log backup immediatly, and shrink the log file, and all the 96 Gb was released. WOW....today also same thing happend, and i did the same. what is happening here? why it became necessary of taking Logbackup twice? please clarify meThanks in AdvanceArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-21 : 03:59:20
|
| Pointless shrinking a file that will grow back again tomorrow, you are just fragmenting the file and that will reduce performance.Increase the frequency of Tlog backup.Check which Tlog backups files are large ... if they occur at the same time each day / day-of-week then look at what tasks are running at that time (i.e. which are generating huge amounts of transactions) and review how they work / why they generate so many transaction. |
 |
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2010-07-21 : 04:53:04
|
| Till now there is no Tlog bkp plan, and i'm creating a maintenance plan for this. please tell me Kiristen the meaning of this sentence "you are just fragmenting the file and that will reduce performance"ArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-21 : 06:25:10
|
| If you repeatedly shrink a file, allow it to regrow again, shrink it again, ... then the resultant file will be fragmented and this will adversely effect performance. There is also somewhat of a performance issue that the file has to be "regrown" when it becomes full and that will take some CPU time of course which, on a busy server, may cause delays (timeouts even?) for users.If you are not making Tlog backups then use SIMPLE Recovery Model - but you will only be able to restore to the most recent FULL backup.Or use FULL recovery model and set up Tlog backups every 15 minutes, and then you have much better ability to recover after database corruption, accidental deletion of some data, to investigate fraud, and so on and such forth ....You should only ever shrink a Database or Log file if there has been some unexpected, one off, accidental growth of the file (e.g. a huge data deletion) - if you are needing to do it "regularly" then there is something wrong! |
|
|
|
|
|
|
|
|
Log backup clarification