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 |
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-03-04 : 06:29:35
|
SQL Server 2000 Enterprise EditionAt certain times of the day the transaction log files created are to large to be handled by the Log Shipping process. Due to large batch jobs.As a consequence I have to reinitialize the Log Shipping process-Primary Server - the Transaction Log Back Up job runs every 5 minutes from 00:00:00 to 23:59:59-Secondary Server : The Transaction Log Copy Job - runs every 5 minutes from 00:01:00 to 23:59:59-Secondary Server : The Transaction Log Restore Jobs - run every 5 minutes from 00:02:00 to 23:59:59 It is necessary to keep the Log Shipping process 24 hrs a day , does any one have any strategies which may assist in dealing with very large .TRN files in the log shipping context ?Jack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-04 : 13:17:19
|
Do you have an optimizations job that runs around the time the trn files become large? If so, you'll need to consider not running DBCC DBREINDEX across the entire database each night. Break it up to only those indexes that need to be rebuilt or consider using DBCC INDEXDEFRAG instead.Even if you don't have log shipping jobs runnings 24 hrs a day, the backup files will be large if you don't correct the situation. And why do the large trn files break log shipping? It should just cause the copy and restore jobs to take longer than usual. Log shipping will be out of sync during that time, but it will catch up if you just let it keep going.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-04 : 21:51:37
|
Actually i had similar problem with Log-shipping.I had setup same schedule to run every 5 minutes and transaction bak files were huge taking spaces on disk.But i didn't setup to delete transaction bak files greater than --- days as LSN numbers gets sync out. Next thing i had a problem was Unless there is no users connecting in secondary server, tran bak files will get restored to sync up. Actually thats really sucks as we are using Secondary servers for reporting purposes ? |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-03-11 : 08:34:41
|
Thanks for th responses , Tkizer : yes there are a number of jobs , reports etc which run at scheduled times during the night , which as you've outlined probbably causes the problem . I was considering the possibility of chnaging the Recovery Model from Full to Simple during the intense reporting ,in your opinion, can you see any problems (in terms of logshipping ) that this would causeJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-11 : 14:09:05
|
You will break log shipping if you switch to simple as the transaction log chain will be broken, so no you can't do that.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2008-03-11 : 14:23:08
|
Since you're on a 2005 platform, you can take a snapshot of the mirror database for your reporting purposes. You'll need Enterprise edition on the mirror server though for Snapshot.SourceDB ---> MirrorDB ---> SnapShot. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-11 : 15:05:02
|
"SQL Server 2000 Enterprise Edition"Its already mentioned on top. |
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2008-03-11 : 15:11:26
|
Feh, someone move the post to the appropriate platform forum.... |
|
|
|
|
|
|
|