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 |
|
ddamico
Yak Posting Veteran
76 Posts |
 Posted - 2008-11-18 : 13:43:38
|
| I am seeking some information on setting to ensure the LDF file for one of my databases doesn't continue to grow. Database A:1. Full backup once a day (backup exec)2. Transaction log backup (backup exec) twice dailyI understand the log file is truncated upon next full backup but the file is not shrunk. The log file is continuing to grow upwards of 25GB. Essentially, I need to have the log file shrink so that is doesn't continue to grow but I need some help in determining on how best to do this.The current settings of the db LDF file settings:1. Automatically grow file2. 20%3. Unrestricted growth.Any setting or approach I can follow to ensure the LDF file doesn't continue to grow? Thanks for your help in advance. |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-11-18 : 13:52:39
|
| DBCC SHRINKFILE |
 |
|
|
ddamico
Yak Posting Veteran
76 Posts |
Posted - 2008-11-18 : 14:02:31
|
| do you think i should create a job to do this every so often. |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-11-18 : 14:13:03
|
| Just do it when u need it, bcos it really effects your database performance. |
|
|
|
ddamico
Yak Posting Veteran
76 Posts |
Posted - 2008-11-18 : 14:24:25
|
| Does anyone know if there is a way to see if the transaction log content is marked for truncation.I am reading that Backup Exec may not work correctly? I want to verify if this is actually happening? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-11-18 : 14:39:23
|
| ddamico, backing up your transaction log twice a day is not enough. We backup ours every 15 minutes. Never shrink the files unless you know for sure that you don't need the space in the very near future. An example of when it is okay to do the shrink is when you just purged a bunch of data. Manage the size of the transaction log by backing it up much more frequently. At a minimum, it should be done hourly.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-18 : 17:13:08
|
quote:
Originally posted by ddamico
I am seeking some information on setting to ensure the LDF file for one of my databases doesn't continue to grow. Database A:1. Full backup once a day (backup exec)2. Transaction log backup (backup exec) twice dailyI understand the log file is truncated upon next full backup It doesn't .but the file is not shrunk. The log file is continuing to grow upwards of 25GB. Essentially, I need to have the log file shrink so that is doesn't continue to grow but I need some help in determining on how best to do this.The current settings of the db LDF file settings:1. Automatically grow file2. 20%3. Unrestricted growth.Any setting or approach I can follow to ensure the LDF file doesn't continue to grow? Thanks for your help in advance.
|
|
|
|
|
|
|
Transaction Log File Help