Log file size issue in Log shipping in SQL Server 2005

Question

Log file size issue in Log shipping in SQL Server 2005

rambilla4

SSCrazy Eights

Points: 8715
More actions
January 21, 2010 at 6:00 pm

#386157

Hi
We have SQL Server 2005 EE x64 with SP3. We are using logshipping as disaster recovery solution. Our network fron Primary to Secondary can handle only less than 200 MB file.
We have scheduled the backup,copy & restore jobs to run every 30 mins.But the log backups of more than 400 MB are getting generated within 30 mins and weekly rebuilding index is genarating 12 GB of log
Question:
1. IS there way to control the log file size (Like if the log file is 50 MB, then ship to secondary so that we do not have Network problem )?
2. I'm using the Rebuild maintenacne task to rebuilld the indexes and it's genarating morethan 12 GB log file. IS there way to run the Rebuild Indexes job with NO LOG Option? Or is there any script to rebuild Indexes with NO LOG Option like we do in ORACLE?
3.Is there any option in Rebuild maintenace Task to select NO_LOG option?
Please advice
Thank you

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

M&M SSC-Insane Points: 21700 More actions · Answer 1

Ganga,

Yours is an intersting scenario. Regarding the size of log file being more than 400 MB, is there a way by which you can reduce the time period of the transaction log backups. Like, instead of 30 mins, much lesser.

Also, for the other query regarding weekly rebuild index operation, I guess you are using the maintenance plan jobs for this. It is better to customize the rebuild index job, i.e, first find out the fragementation level of the indexes and then proceed with the ones that are heavily fragmented. In this way, you would avoid running the rebuild operation for all the indexes (i.e even for the ones where the fragmentation level is fine).

You could use DMV like sys.dm_db_index_physical_stats for checking fragmentation.

And another thing is you could make use of SORT_IN_TEMPDB = ON option.

Eg.from BOL

USE AdventureWorks;

GO

ALTER INDEX ALL ON Production.Product

REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,

STATISTICS_NORECOMPUTE = ON);

GO

Here, the sorting operation during rebuild uses the tempdb database rather than current.

This will be useful in avoiding your user db log file from growing big.

Hope you found suggestions useful. 🙂

M&M

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Ganga,

I agreed with Mohammed Sort in tempdb option will use space of tempdb your tempdb will grow 🙂 to restrict the logfile growth during maintenance.

set Tlog to perform every 10 min instead of 30 min and see if it helps.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

I would go so far as to increase the tlog backup interval to every 5 minutes during index rebuilds / reorgs

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 4

I would go so far as to increase the tlog backup interval to every 5 minutes during index rebuilds / reorgs

I have changed the log backup schedule to every 15 mins and now the log file size is OK in day time. But when I do rebuild indexes on weekly basis, how can I change the log backup schedule to change every 5 mins on the fly?

thanks

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 5

I agreed with Mohammed Sort in tempdb option will use space of tempdb

If I select the option Sort results in tempdb, the log size of 12GB of MyDB database, is going to write it in tempdb so that the log file size of 'Mydb' will not increase right?

Correct me if I misunderstood

thanks again

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

rambilla4 (1/22/2010)
I agreed with Mohammed Sort in tempdb option will use space of tempdb
If I select the option Sort results in tempdb, the log size of 12GB of MyDB database, is going to write it in tempdb so that the log file size of 'Mydb' will not increase right?
Correct me if I misunderstood
thanks again

SORT_IN_TEMPDB only results in tempdb activity if the sort operation will not fit into memory (see the index create memory, tempdb and index creation, and Setting Index Options topics in Books Online for more information).

For your t-log backups do you use native SQL backup or some other app?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 7

thanks,

log backups are happening from the Native Log shipping backup job

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

rambilla4 (1/22/2010)
thanks,
log backups are happening from the Native Log shipping backup job

I would do that as a part of a manual process - or change them to always run every 5 or every 10 minutes.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 9

So there is NO option like with NO_LOG in SQL Server while Rebuilding Indexes like we have in ORACLE?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

rambilla4 (1/22/2010)
So there is NO option like with NO_LOG in SQL Server while Rebuilding Indexes like we have in ORACLE?

No

Here is a link with a doc to download that goes into greater detail.

http://msdn.microsoft.com/en-us/library/cc966402.aspx

You may want to consider Index Reorganize in lieu of a Rebuild every time. Reorganizing indexes is minimally logged. Not all indexes must be rebuilt on the same schedule, some only need to be reorganized.

Another option to keep the impact low is to stagger index rebuilds (x number of tables on monday, y number of tables on tuesday and so on).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 11

Is there way to control the log file siz, eLike if the log file is 50 MB, then ship to secondary in Log shipping configuration in SQL Server 2005 EE as we do in Data guard in ORACLE

thanks

iBar SSCertifiable Points: 5872 More actions · Answer 12

Can you afford, changing the recovery model during the weekly index rebuild ?

In the weekly maintenance plan,

1. take full backup

2. Change the recovery model from full to simple

3. Then rebuild the index, ideally with SORT_in_Tempdb

5. Change the recovery model back to FULL

4. Shrink the log file, if required

This will help reducing your log on the primary server and cut the 12GB to minimum.

Remember, another related problem could be, if you are using secondary server as reporting server. In that case you might need a different solution because you cannot bring secondary DB online just to rebuild the indexes.

Hope it helps.

Cheers

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 13

On the weekly maintenance plan,
1. take full backup
2. Change the recovery model from full to simple
3. Then rebuild the index, ideally with SORT_in_Tempdb
5. Change the recovery model back to FULL
4. Shrink the log file, if required

If I change the Recovery model to Simple, then the log shipping chain will break and I need to send the full back of the database to secondary & restore with NORECOVERY & configure the log shipping right? If I do that the full backup of size 15 GB, I need to copy to Secondary which is NOT possible for us because of the Network bandwidth. i.e we cannot send a file more than 100 MB (thats the rule we got from our network admin)

SO is there a way to set a fixed file size for the log file (say 60 MB), and the log file size reaches 60 MB, then ship the log file to secondary? thats the way we are implementing in ORACLE using dataguard.

thanks

iBar SSCertifiable Points: 5872 More actions · Answer 14

Yes simple will create problems. I tried with Bulk=Logged but even it do not work the way you want this.

What else you are doing when you are re-building weekly indexes ?

If are just re-building indexes and there are no other bulk-loading and heavy transactions at that time ?

then

you need to identify large tables and large indexes and then you need to split the index re-build task into several intervals.

1. Full backup

2. Rebuild index of 1st largest table (Notice the time required to re-build that index)

3. log-ship after 5 min

4. Rebuild index of 2nd largest table

5. log-ship after 5 min

If you think that you can break-down more objects which can fit by size and time into that 5 mins then you can add them later.

Cheers