I am doing a fullbackup once a day and transaction log backup every 15 mins.
The jobs are running fine on both primary server and secondary server (restores always with norecovery).
But unexpectedly I am receiving the below error on the secondary server from yesterday.
Eg:
The transaction log backup taken at xyz_13_00.trn fails and I cannot go further.
Msg 4330, Sev 16: The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE LOG is terminating abnormally. [SQLSTATE 42000]
If I try restoring from the transaction log backup taken at xyz_12_45.trn, it restores fine.
The statement that I use,
restore log xyz from disk='E:MSSQLsqlbackupTranlogBackupxyzxyz_13_ 00.trn'
with replace,norecovery,
move 'xyz_log' to 'f:mssqldataxyz_Log.LDF'
Im planning to setup sqlserver 2005 log shipping between two servers.The size of the database is around 50GB and we are taking transaction log backup and shipping at 30mins interval.
Suppose if the backup job couldnt take the transaction log backup with in the specified interval ie 30mins,then how does the copy and restore job works. Will it give an error saying tht the transaction log could nt be copied and restored ??? How to deal with this kind of situations where the backup is taking more time than the threshold time limit and ur copy,restore fails. Is there a way to handle this kind of situation in logshipping Sqlserver 2005. and how does it affect the Secondary database. ??
I have log shipping set up between 2 SQL 2000 SP1 Servers on Win 2000. The db is small 10 meg, and when the restore job on the backup server fails I am getting "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." as the message, the maint paln on the primary server show no error. Anyone seen this before? The restore has worked 3 out of 5 times
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
I am using ODBC to connect SQL Server 2000 + SP4 server runnig on Windows 2003 standard edition Server +SP1. But, sporadically my application server connectivity to DB fails and i receive the following error messages
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (WrapperWrite()). [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.) In D:de.cpp 702 Apr 12 2006 20:34:47 [Microsoft][ODBC SQL Server Driver]Communication link failure) In D:de.cpp 702 Apr 12 2006 20:34:47 20071017 08:23:10 TID (00000ff0) Sev (3) Err (0) Msg (Read failure. General SQL error.
Strange thing is that i receive this error only sporadically, Please Please advice !
I need to create a RO copy of a production DB owned by an outside company. We are connectd via a WAN link, but cannot use replication. They are proposing using an initial load via tape, and sending us a text file nightly with the days changes to the DB. We will then need to load that data using BCP, DTS or some other method. Does any one have any ideas on using log shipping instead of the text file. It would only be practical to get a fresh load of the entire DB once a quarter or once a month at most. It is a 40+ GB database and we are expecting 100 to 200 MB of logs per night. For business reasons, we are limited to some type of file transfer mechanism for the data transfer, and cannot really change their backup schedule which is nightly fullbackups and tlogs every 30 minutes.
I am using SQL 2k EP Editions with SP2 on Win 2k Advance servers. Since more than week or so I am trying to establish log shipping between two servers. But its not working.
I am using database maintainence plan wizard to set up log shipping. Every thing works fine as far as wizard is concern, it creats plan for log shipping. But my log shipping is not working. The plan to back up log on source database is working fine. I can see the job history and the log files in the backup folder. But I have found that the job on the standby server to copy log file on network folder is failing and so the job to restore log on stand by server. I get the following message
"sqlmaint.exe failed with error state....."
Little reaserch on the standby server found that sql server is using maintainence plan to copy and restore log files, but i do not see any database maintainence plans on standby server as well as I have checked that there is no plan id in sysjobs table on either server.
I have sa rights. The account used by sql service and sql agent have admin rights and they do have rights to access the network folder for both the servers. So there is no rights problem.
I have followed all steps published in white paper for setting up log shipping on microsoft web site.
I have searched microsoft KB but it is of no use for sqlmaint.exe.
This might end up being fairly lengthy...I'm in the midst of implementing log shipping as a "warm stand-by" solution at my company. All the components appear to be in place: I'm using cmd shell to copy the backup device file to a remote server and then execute a RESTORE stored procedure on the remote server. The copy and restore work just fine. The problem I'm having is with the transaction log dumps and restores. We normally dump transaction logs (and then truncate) every hour. With the log shipping being implemented, we're going to want to do separate log dumps every ten or fifteen minutes, copy that dump over to the remote server, and then apply that log to the database. Here's the question: for the log ship portion, I don't truncate the log. But after the "normal" log dump occurs, things get tossed out of whack. When you try to apply a log, I get the message "database has not been rolled forward enough....". Has anyone encountered this type of issue and if so, how did you work around it? I'm assuming it's a simple of issue of certain options you set on your dumps and scheduling.... I'd appreciate any help.... Thanks!!!
We are considering implementing log shipping. Do the sql server logs keep track of the logs that are shipped and applied through log shipping? Or is there some other way to make sure that all logs have been shipped and applied?
I have been successful in getting log shipping working but still have some nagging questions that I cannot find answers to.
1. I had a situation where the copy for one TranLog took much longer than the 15 minute interval I have it setup for. It seemed to get stuck on that copy. Is that how it is supposed to behave.
2. Related to the question above, weekly, I have jobs that reorganize, check integity, recalc statistics. Would these jobs create very large log files? If so, how do others deal with this?
3. Is there any documents available that discuss testing converting your secondary server/database to your primary and back again?
4. Is there a way to setup Email notification to report out-of-sync conditions?
While configuring log shipping, if i choose the "allow database to assume primary role" then the "ceate and initialise new database " option is selected by default..Does this happen all the time or am i missing something.What if i have already initialised the destination database.
I am testing my log - shipping strategy. I have tried with northwind database and it was successfully created and is operating. However in order to test I have created a new Test Table in the primary database to see whether it is working. From now on database shows that it has been loading and I cannot see any tables it is grayed and it says loading. What would be the problem? When I checked the logs it has been copying to the secondary database and it doesn't show any error in the log-shipping monitor. It seem everything is cool accept this loading part. If some one help me I really appreciate it.
I am using SQL 2k EP Editions with SP2 on Win 2k Advance servers. Since more than week or so I am trying to establish log shipping between two servers. But its not working.
I am using database maintainence plan wizard to set up log shipping. Every thing works fine as far as wizard is concern, it creats plan for log shipping. But my log shipping is not working. The plan to back up log on source database is working fine. I can see the job history and the log files in the backup folder. But I have found that the job on the standby server to copy log file on network folder is failing and so the job to restore log on stand by server. I get the following message
"sqlmaint.exe failed with error state....."
Little reaserch on the standby server found that sql server is using maintainence plan to copy and restore log files, but i do not see any database maintainence plans on standby server as well as I have checked that there is no plan id in sysjobs table on either server.
I have sa rights. The account used by sql service and sql agent have admin rights and they do have rights to access the network folder for both the servers. So there is no rights problem.
I have followed all steps published in white paper for setting up log shipping on microsoft web site.
I have searched microsoft KB but it is of no use for sqlmaint.exe.
I failed to configure the Logshipping in SQLServer2000, through database maintenance plan wizard, at last step I got the following error.
Microsoft SQL-DMO(ODBC SQLState:42000) Error 50007:xp_repl_encrypt:Error executing srv_paramsetoutput
I am running SQLServer2000 Enterprise Edition with Servicepack2, and Windows2000AdvancedServer with Servicepack2 on both machines and bothe machines are in the domain, both SQLServerservices are running under Domain account.
Is there any effect on taking full backups on Primary Server while log Shipping is in place? If we take a full backup is that applied to the secondary server?
We would like to use log shipping for a Near Real Time (5 min delay)reporting server. SQL 2000 requires exclusive use of the database when the logs are applied. This presents a problem since there may be a couple of hundred users on the NRT server. Even if they only make connections when running the reports there could be some contention issues. Has anybody used log shipping in this type of scenario?
Is it possible to log ship a database from server A (source) to server B (read only) when the database on server A is partly replicated (transactional repl) to several other servers ? All hints and advices are highly appreciated.
Does anyone know why some databases in SQL 2K Enterprise are not available for log shipping? In other words when you select a database Log Shipping this option is not available (grayed out). Other DB's on the same server log ship without problem.
Any ideas?
I think Microsoft has an answer to this but their site is not working so I can't get to the Q270006 which supposed to discuss that. If you do a search in KB for "Log Shipping is disabled" you will see the WebCast link which references Q270006 but then if you click on the link to go to the Q link does not work and search Q270006 is not found in KB..
to use Log Shipping solution, Entreprise Edition is required for both Primary and Secondry Server (you agree ?).
Do you have an idea about licence regarding the secondry Server (suppose that you have to switch 2 or 3 per year. The second server will be used 2 or three times per year).
In some plateform (xeon CPU), the logical number of CPU is 2 times the physical number (SQL Server See 2 CPUs. You have 1 physical CPU). Do you have an idea about licence in this case ?
Hi, I have following question ( same was asked to me by my collegue)
I have Log shipping between two servers ( say Server A - Primary & Server B - Secondary). One user is logged in to B Server and fired a long query on the same database / object. it is taking a very long time to execute.
Q : How this will impact the log shipping process ? Ans : Log shipping will fail as the secondary db will show as " database is in use" for log shipping process.
Q : How to recover the log shipping process? And : Notify the user to disconnect the session / or kill the user session. But i don't know how to recover the log shipping.
All, I am new to log shipping and I am troubleshooting an issue with it. Basically somebody restored a backup from a day ago on the primary server, now the load is failing on the destination (obviously). I read that I should disable the jobs and backup and restore to the destination server to get it going again. I have some questions on this though, since the destination is a standby I read that I need to use the WITH STANDBY clause and specify a standby file name to use. Where do I find out the standby file name to use here and also is this the only way to get it back in synch?
I am attempting to set up log shipping from my primary dell clustered 2node sql server to a backup quad xeon server in case of a failure. When Ifollow the information given from Microsoft regarding this and I get tothe point where I am at "Add Destination Database" I am being promptedfor my server name, which shows the primary one ONLY. I dont seem to findany documentation on where I add in the secondary server name...Searching online has yielded no responses for me and MS only says toregister the servers... but where? and do I need to restart? I am onlytrying to be extra cautious as this is a live database.Thank you in advance for your help...
Hello:I'm using MS SQL Server 2000 developer editionand trying to implement log shipping to the secondary server.I copied a database MDF and LDF files from primary to secondary serverand all data is there, but this database still in recovery mode so I can notapply log shipping to it.My question is:how to put the database on secondary server in standby or non-recovery modeto apply log shipping?Thanks,GB
Hi all,i was wondering if for the use of logshipping two Enterprise Editionversions of SQL Server are necessay or logshipping can also workbetween a Enterprise Edition and a Standard Edition.Does anyone know an url for me where i can read more about this. I'vefound quite a lot of articles but not one telling me explicit thatlogshipping does not work between an EE and a SE version.Greetings,Sjaak
I am trying to set up a dual log shipping configuration in SQL 2005 where I have one primary server to two secondary servers.
All of the following is being done from the primary server.
If I fill out the entire log shipping configuration window with both servers and then press OK, and let it all get set up, and then run the log shipping report, I only get status information for one secondary database - the other one is there but has no information available, yet the setups on both secondarys are OK and the jobs are succeeding on them.
If I then go back into the log shipping screen for my database and remove the server that I'm getting no status information for and press OK to actually remove it, then go back in and re-add it and THEN run the log shipping report I now get information for the server I just deleted and re-added, but no status information for the other server. If I look in the secondary database table, I only see a record for whatever secondary server is currently correctly showing status information.
When looking at the scripts generated by the log shippping screen for my setup, I noticed the stored procedure call below gets executed from the proc sp_delete_log_shipping_secondary_database that the script calls. This SP is only getting passed the database name, which is the same on both secondary servers and would therefore be the same for both servers' entries in the secondary database table on the primary server. Therefore, I think it is deleting ALL records in the secondary database table that have that name before re-adding the record for the one server and that's what's causing my issue, i.e. if my database is called "userdb" it is deleting all entries for "userdb" regardless of whether they are on the primary or secondary servers and then only adding one server back. Am I wrong?
Can some one help in understanding Log Shipping. My production database is over 50 GB and we want to implement Log shipping to ensure a standy by server. I know how to implement the Log shippinng and what it does but I am not sure how to perform Log shipping with daily FULL database backup on production server. Consider this scenrio.
1. 1:30 AM Monday Full database backup on production server 2. At 1 hour interval transaction backup on production server and applied on Standby server. 3.1:30 AM Tuesday Full database backup on production server..... Here I am getting confused. DOo I have to apply this full back up again on standy servers, because if I don't then my LSN for next transaction log will be different. So how can i take a full database backup of the production server while implementing Log Shipping. Thanks, Punu
I'm fairly certain that this posting should not be in replication, but here goes. I'm not a SQL expert, and I know just enough to keep things running. I'm looking to add some recovery options on top of my current backup to tape rotation for 2 core SQL servers. I setup log shipping and that appears to be working without an issue. I left it in standby mode instead of recovery and I also did a run through on switching the server where the primary db resides. That worked great and on the secondary data was up to date and all looked great.
Now I would like to test this process by taking my primary (test)SQL server down and force the secondary server to have the db available for read/write. Can anyone explain to me the best practies for performing the role change in this scenario?
SQL Server 2000 Enterprise running on Windows 2000 Server
We have configured log shipping on our SQLServer 2005 (SP2) servers and this has been working fine for the last three weeks. The primary, secondary and monitor are all on different machines within in a group. A service account exists on each of the machines with the same username / password, and this is used for the folder shares etc.
On Saturday afternoon the monitor job started reporting that one of the backups was out of date and I then recieved over 900 failure email messages (one every 3 minutes!)
It seems that although all the jobs are running correctly on both the primary and secondary servers, the log_shipping_monitor_primary table on the primary server is not being updated.
Is there anything else I need to check for errors?
Also, is it normal to receive so many emails, ideally i would just get a mail the first time it noticed it had failed.
Can stand by server in log shipping host production database used by other applications? if yes, what is the effect of log shipping on other databases?
