September 21, 2009 at 9:34 am
Hi
I have been struggling to find a solution for this issue. I have a database that is about 90GB in size, I run a reindexing job on saturday around 2 AM, the first transaction log back up occurs after the job is done is so big(almost equal to the database size) which breaks the log shipping because I am transferring the data over the WAN. The reindexing job converts the databasebase mode to bulk_logged before reindexing and then switch back to full recovery mode after the reindexing is done.
What do I need to do here so that the tran log file is not so big? Am I missing any steps with the reindexing job? please please please help!
September 22, 2009 at 1:26 am
ashaaban (9/21/2009)
HiI have been struggling to find a solution for this issue. I have a database that is about 90GB in size, I run a reindexing job on saturday around 2 AM, the first transaction log back up occurs after the job is done is so big(almost equal to the database size) which breaks the log shipping because I am transferring the data over the WAN. The reindexing job converts the databasebase mode to bulk_logged before reindexing and then switch back to full recovery mode after the reindexing is done.
What do I need to do here so that the tran log file is not so big? Am I missing any steps with the reindexing job? please please please help!
Juts out of curiosity, can you break the indexing job down in separate jobs and do smaller log backups between.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 22, 2009 at 6:42 am
I dont believe this would work either. I have a couple of tables that are so large and reindexing only one takes over an hour, so even if I split the job I will still have some issues with large tables. also I dont believe that log shipping is flexible is scheduling the logs. Please if any one has any other ideas let me know. Thanks!
September 22, 2009 at 7:39 am
As silverfox said, still worth splitting the job up into tables, lynn pettis posted an article on this very recently, his script would be a starter for 10. Kick off your logshipping log backup job by using the sp_start job command, which will maintain the lsn chain and execute the job independent of the schedule
Try a reorganize rather than rebuild for the large tables, this will enable you to set up a SQLagent performance alert to back up your log by running your logshipping log backup job whenever it becomes a chosen percentage full.
Probably also worth doing the large tables at a separate time to the other tables, anything to spread the load.
---------------------------------------------------------------------
September 22, 2009 at 10:02 am
Thanks for all of your input. I will try to split the job. But I was just wondering if there are any thing wrong with the way I am reindexing, and the reason I am asking is because I thought it could be wierd for a 90GB db to produce a log file with a 90GB in size after reindexing.
This is what I am doing:
ALTER DATABASE [rkdbtest] SET RECOVERY bulk_logged WITH NO_WAIT
go
alter index all on dbo.ACAACCT rebuild with (fillfactor = 90, sort_in_tempdb = on)
.....
ALTER DATABASE [rkdbtest] SET RECOVERY FULL WITH NO_WAIT
go
September 22, 2009 at 10:22 am
Its not unusual for a reindex to produce log = to the size of the database. If you have a lot of fragmentation a lot of data pages are going to be moved around, in bulk logged mode all of those moves will be recorded so they could be played back if necessary.
---------------------------------------------------------------------
September 23, 2009 at 7:17 pm
about 5 years ago, few jobs before, one of our DBs was about 1.5 TB: So our wknd maint included the floowing: we stop Logshipping, before reindexing ( every Saturday night) we moved db into simple mode, perform reindexing in parallel ( to speed up process), but it back in full recovery, make a backup using Litespeed, copy to the Standby Server, restore and start Loshipping. Can you consider that or copy over the WAN is time conusming?
September 24, 2009 at 6:13 am
I cant do that. The copy process will probably takes forever. It is absolutely not an option. But thanks for your input. I am really not sure what to do at this point. I dont think I will get the logshipping to ever works on my DB.
September 24, 2009 at 6:22 pm
Do you think you can use compression software like LiteSpeed or RedGate
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply