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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Log Shipping failing - large trn files

Author  Topic 

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-03-04 : 06:29:35
SQL Server 2000 Enterprise Edition
At 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 ?
Go to Top of Page

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 cause


Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com
Go to Top of Page

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

Haywood
Posting Yak Master

221 Posts

Posted - 2008-03-11 : 15:11:26
Feh, someone move the post to the appropriate platform forum....
Go to Top of Page
   

- Advertisement -