February 16, 2011 at 12:28 pm
Hi,
I have a rebuild indexes job of a prod database that runs every month at night . After the rebuild job completes, the ldf files grow to 80gb.( the db size is 320 gb)
My log shipping starts from 6AM-8PM...every 5 minutes for tht particular database.
So in hte morning after the rebuild night, my log shipping goes out of sync.
The first transaction log file on log shipping server at 6 am is around 80gb. I guess, it takes a long time to restore it and hence all trans log files (every 5 min) after tht are out of sync.
Also, i receive low disk memory on the log shipping server.
So i had to shrink the prod db log files and re establish the log shipping.
What is the best method?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 16, 2011 at 1:36 pm
A couple of things you could do to minimize the log size and the effect on logshipping.
Change your rebuild job so that it only rebuilds specific tables on certain nights and then have the rebuilds run over several nights.
Leave logshipping enabled and running during the rebuild(s). Backup the logs every x minutes during your rebuilds.
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
February 17, 2011 at 7:36 am
CirquedeSQLeil (2/16/2011)
A couple of things you could do to minimize the log size and the effect on logshipping.Change your rebuild job so that it only rebuilds specific tables on certain nights and then have the rebuilds run over several nights.
Leave logshipping enabled and running during the rebuild(s). Backup the logs every x minutes during your rebuilds.
I cant leave log shipping enabled and running during rebuilds..
If i shrink my log file (ldf)after the rebuild, then will the first trn log file ( log shipping) at 6 am next morning be same 79 gb or less..
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 17, 2011 at 9:07 am
Unless you do one or both of those things I mentioned, your log file will continue to be 70+ GB for that first tran log that needs to be shipped.
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
February 17, 2011 at 1:35 pm
If i do the rebuild of only some indexes and that too some specific nights and then also the log file is around 25 gb, then will it go again out of sync in the morning?...
It shouldnt be the case.. is there any specific cut off in gb.... whr log shipping would be done without any problem....
Is that the later logs cant be copied or not restored in time?
They should be arranged in a queue and it should be in sync after some time....
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 17, 2011 at 1:38 pm
The constraints with logshipping is the network and the latency that will be imposed in copying a file of that size. 25Gb will copy faster but it is still gonna cause a delay in logshipping.
Why can't you run logshipping while rebuilding the indexes?
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
February 17, 2011 at 1:51 pm
We run log shipping everyday from 6AM-8PM , with 5 minutes interval on prod database.
We do rebuilding indexes every 2 weeks on a weekend, so i cant change the course of log shipping just for 1 day when indexes are rebuilded.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 17, 2011 at 1:59 pm
Is there something that runs overnight preventing you from having logshipping run through the night every night?
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
February 17, 2011 at 2:13 pm
v run daily full db backup at around 8:30 pm of tht particular db. The db is 320 gb and it takes around 1.5 hrs to backup.
What would happen if the db is backing up and same time log shipping is also backing up every 5 min?
Wouldnt there be any discrepancy with the LSN numbers involving log shipping,
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 17, 2011 at 2:53 pm
No discrepancy with the LSNs. The log chain would not be broken
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
February 18, 2011 at 12:51 am
February 18, 2011 at 12:53 am
February 18, 2011 at 2:38 am
When you say your log shipping is going out of sync, how long are you leaving it for? We have log shipping set up over an ADSL link (e.g. really slow!) and a big log backup will knock that out of sync, but it always gets back into sync by itself--I've never yet had to rebuild the log shipping just to fix this issue.
February 18, 2011 at 3:35 am
In what state you leave database after restoring log - standby or no recovery?
If you have standby, change to no recovery,that will speed up process of restoring logs, no undo process occurs
I've the same situation rebuild large indexes generates large trn files.
February 18, 2011 at 6:04 am
BillAvalos06 (2/18/2011)
ok:-)
Wats this ??
Regards
Sushant Kumar
MCTS,MCP
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply