SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Log Shipping failing - large trn files
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2076 Posts

Posted - 03/04/2008 :  06:29:35  Show Profile  Visit jackv's Homepage  Reply with Quote
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

Edited by - tkizer on 03/11/2008 16:27:06

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 03/04/2008 :  13:17:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 03/04/2008 :  21:51:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2076 Posts

Posted - 03/11/2008 :  08:34:41  Show Profile  Visit jackv's Homepage  Reply with Quote
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

USA
37316 Posts

Posted - 03/11/2008 :  14:09:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
221 Posts

Posted - 03/11/2008 :  14:23:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 03/11/2008 :  15:05:02  Show Profile  Reply with Quote
"SQL Server 2000 Enterprise Edition"

Its already mentioned on top.
Go to Top of Page

Haywood
Posting Yak Master

USA
221 Posts

Posted - 03/11/2008 :  15:11:26  Show Profile  Reply with Quote
Feh, someone move the post to the appropriate platform forum....
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000