Solved

MS Sql Server 2000 Log Shipping - taking too long

Posted on 2011-09-14
3
279 Views
Last Modified: 2012-05-12
I Have Sql server which log ships the transaction log to a standby server used for reporting. I am presented with the following issue the transaction log is not consistent in size its output in between min 5mb to just over 1gb.
When the file is applied by the standby server from Saturday to Weds it extremely slow, but then it happens to speed reducing the out of sync time and by Thursday / Friday is fully operational
This issue is happening on weekly basis.

So far set a trace on DBCC TRACEON (3004, 3605, -1) to determine any errors, but none have been captured.
DBCC Checkdb (DB) takes too long to process in query analyser processed for 30 mins no results returned
DBCC loginfo (db) returns  561 rows which would indicate Vlf may not be an issue
The domain accounts on the server are permissions on the shared drive are correctly assigned, no hardware bottlenecks
The problem has started over the past month .
Any advice is appreciated
0
Comment
Question by:bestway_it
3 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 36537526
Am I to understand that you are only applying the logs twice per week?  If so, can you not speed that up to once per day at least?
0
 

Author Comment

by:bestway_it
ID: 36537771
The transactionlog is shipped on interval of 15 mins every day, the procedure is on a Sat the recovery model on the production server is set to simple then reindex all tables then change the recovery model back to full back up all databases with symantec veritas then start the log shipping on sunday morning at 04:00 from this point onwards the standby server is slow in appying the transaction logs whilst the log shipping is to intervals of 15 mins

 Hope that helps to understand the situation tanks you in advance
 
0
 
LVL 5

Accepted Solution

by:
VENKAT KOKULLA earned 500 total points
ID: 36542719
The transaction log file size gets varied depending on the transactions that are running on DB.
Have you checked the file size which are generating b\w Satuday n Wednesday, i guess the file size will be more when compared with Thursday / Friday.
Could you please check the memory usage of SQL @ peak load and the transactions that are taking time b\w Sat n Wed.
the attached snap will help out regarding the SQL Memory settings.
Try it and let us know if still having issue

SQL-RAM-USAGE.jpg
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question