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 |
|
Jake Shelton
Yak Posting Veteran
74 Posts |
 Posted - 2012-06-24 : 06:14:54
|
| Hi all,During defragmention of a DB's indexes last night its log blew up from 2 to 80gb in size, and although we have sufficient space, the db is the primary for log shipping and this problem has caused the secondary to drift out of synch by several hours, whereas shipping should occur every 15 mins).All the while, the log still hasn't reduced. Is there any way I can force the log to commit any uncommitted transactions as necessary and then flush out ? I have done a full backup of the datafile, to no effect. Would performing a tran log dump break the log shipping chain?Thanks,JakeEdit - the DB recovery model is 'Bulk-logged' |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-24 : 07:32:45
|
| If you can afford to break your current log shipping: break it, delete the secondaries, make new full and log backups of the primary, reinitialize your secondaries from them and re-establish log shipping. If you can't then don't make any new log backups, unless you're comfortable doing manual log restores on the existing log shipping chain.If you're going to do regular defragmentation it's better to query the current level to determine if a rebuild is more appropriate. I recommend Ola Hallengren's maintenace procedures:http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.htmlThere are parameters to choose reorganize vs. rebuild based on fragmentation level. If the fragmentation is over 30% then a rebuild is typically recommended, however it's not an online operation unless you have Enterprise Edition. |
 |
|
|
|
|
|
|
|
Tran log enormous after defrag