| Author |
Topic |
|
noneil277
Starting Member
13 Posts |
 Posted - 2008-10-02 : 16:41:45
|
| Greetings,I've been trolling these forums for quite some time and finally took the time to register, so hello.My first question after searching for an answer here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=43966is how to successfully manage a database with a rapidly growing 100+GB daily LDF size without interupting the transaction log shipping?apprecaite any feedback, thank you. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-02 : 16:57:55
|
| Questions1)Are you backing up your Transaction log frequently?2)Are you rebuilding all indexes? Use Tara's script for it. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
noneil277
Starting Member
13 Posts |
Posted - 2008-10-03 : 12:02:53
|
| Hi,Thank you very much for the responses, i appreciate the support from the community.In regards to the questions asked.The size of the database is about 150GB.The transaction logs are being backed up every 30 minutes.The size of each 30 minute transaction log backup is about 30MB, and the log file (LDF) is about 100GB.In regards to rebuilding all indexes, there are no maintenance plans to rebuild indexes but the script from Tara does sound very interesting, if we could have a copy of that and a brief explanation, thanks again. |
|
|
|
lordicarus
Starting Member
3 Posts |
Posted - 2008-10-03 : 12:22:58
|
| I am actually working with noneil277 on this issue. The defrag index job is the one here correct? http://weblogs.sqlteam.com/tarad/archive/2008/09/03/Defragmenting-Indexes-in-SQL-Server-2005.aspx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lordicarus
Starting Member
3 Posts |
Posted - 2008-10-03 : 14:20:31
|
| I actually read your article here http://weblogs.sqlteam.com/tarad/archive/2008/09/11/File-Growth-Settings-for-SQL-Server-Databases.aspx and I am guessing this relates to the reason why you do not recommend shrinking the log file. Are there any other reasons against doing this?Also, in regards to the process of shrinking the log file, I was under the impression that you either have to truncate the log or switch to simple and back to full recovery before you can run the dbcc command. I thought that doing this would break the log chain and cause log shipping to fail when it tries to restore the log on the standby server. Could you or someone else please explain this in a little bit more detail?Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lordicarus
Starting Member
3 Posts |
Posted - 2008-10-03 : 16:06:20
|
| Thank you much for the assistance! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-03 : 16:21:21
|
| Also If you are using Bulk-Insert task, always use Batch_size so you don't fill up Tran log again. |
|
|
|
|
Shrinking an LDF without breaking Log Shipping