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 |
|
gentis
Starting Member
1 Post |
 Posted - 2008-05-20 : 01:10:53
|
| Hello FolksWe have configured log shipping from prod to standby, and thats working great, but we have a nightly maintenance plan the makes a full backup of the database and rebuilds indexes in the prod DB. This maintenance plan increases log file tremendously. You guys have any ideas how we can truncate the log file without disturbing log shipping ? I have read rebuilding indexes increases log file a great deal. Any ideas how we can get around this?Thanks,G |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-05-20 : 02:12:35
|
| You can try using SORT_IN_TEMPDB, although this is not guaranteed to work. How big exactly is your database and what size does the logfile grow to when the maint plan runs? |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-20 : 09:42:29
|
| While rebuilding index ,You use SORT_IN_TEMPDB option so that it does operation in TempDb. If you log file is growing huge while rebuilding index1)you need to figure out which index needs to be rebuilt by checking fragmentation level and kicking the job.Remember that when you rebuild non-unique clustered index ,all non-clustered index pointing to it gets rebuild as well.2)you can also shrink log file after log backup.3)Try taking log backup frequently like every 15-20 mins. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-05-20 : 11:06:42
|
quote:
Originally posted by sodeep
While rebuilding index ,You use SORT_IN_TEMPDB option so that it does operation in TempDb. If you log file is growing huge while rebuilding index1)you need to figure out which index needs to be rebuilt by checking fragmentation level and kicking the job.Remember that when you rebuild non-unique clustered index ,all non-clustered index pointing to it gets rebuild as well.2)you can also shrink log file after log backup.3)Try taking log backup frequently like every 15-20 mins.
As I said, SORT_IN_TEMPDB is not guaranteed to work as if a sort is not required or the operation can take place in memory, it is ignored. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-20 : 22:14:18
|
| Even you shrink log file on primary, sql still ships transactions generated by reindex to standby. |
|
|
|
|
|
|
Log Shipping and Index Rebuilding