Solved

MS Sql Server 2000 Log Shipping - taking too long

Posted on 2011-09-14
3
275 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now