Log Shipping Restore seems to Time out after 10 minutes

While playing with log shipping it appears that the restore is timing out, without errors. The shipper is successfully backing up to disk, xcopying the backup to the receiver, calling the restore SP on the receiver. The restore runs for exactly 10 minutes
and 1 second then ends with a success. Any ideas on what I missed?
Denny,
I'm not sure about this. How do you know it has timed out - do any of the
transactions get applied on the standby server at all?
Regards,
Paul Ibison
|||I am not sure it is timing out, it was only a guess. When it tries to apply the first log it says that database was only partially restored.
If I manually run the retore SP on the receiver, rather than having the shipper call it, it successfully restores the entire backup file then I can ship the logs with no errors.
|||I ran the restore SP via Query Analyzer on the shipping server and I was correct. The error was (OLE/DB provider returned message: Timeout Expired)..
Did I do my linked server incorrectly?
|||Denny,
your setup is sound, as there is no timeout property in log shipping. I
suspect this is something peripheral - it could be network lag, or
alternatively (and possibly more likely) your database is expanding. I have
a KB article that explains this as a potential cause of this issue:
http://support.microsoft.com/default...;EN-US;Q305635
HTH,
Paul Ibison
|||I was able to narrow it down to a timeout setting with the Linked Servers. Both the connection timeout and query timeout had a value of zero entered, which is supposed to be no timeout (assuming I understood the documentation correctly). As soon as I set
the query timeout to 1200 seconds everything works fine. I will probably set it to 1800 seconds, I'd imagine 30 minutes would be more than enough time to restore from a file.
|||Denny,
but log-shipping doesn't use linked servers. Is this perhaps a custom
inplementation?
Regards,
Paul Ibison
|||Yes it is. I followed the directions from a doc on SQL-Server-Performance. The scheduled task on the production box dumps to disk, copies it to the warm standby server using xcopy, then calls an SP on the warm standby server that restores that file.
|||Denny this makes sense now. If the query timeout is zero, it uses the value
in sp_configure. I thing that you'll find that that is the problem as it is
set too low. Setting the value at the lower level as you have done overrides
the sp_configure value, so that explains why it works now.
Regards,
Paul Ibison

Log Shipping Monitor

Hello,
I have configured a SQL Server for Log Shipping using the Database Maintenan
ce Planner Wizard. Now a Log Shipping Monitor object appears below the Manag
ement folder. How do I reverse this process, and make the Log Shipping Monit
or disappear? In other word
s I want to revert back to the state SQL Server was in before I ran the Data
base Maintenance Planner.
Thanks,
Jerome SmithHello all,
I'll answer my own question for everybody's benefit. Eliminating the Log Shi
pping Monitor is equivalent to eliminating all Log Shipping pairs from the L
og Shipping system database. This is done with the stored procedure:
sp_delete_log_shipping_primary
located in the msdb system database.
Following is the help article.
Cheers,
Jerome
sp_delete_log_shipping_primary
Deletes the primary server from the log_shipping_primaries table.
Syntax
sp_delete_log_shipping_primary
[ @.primary_server_name = ] 'primary_server_name' ,
[ @.primary_database_name = ] 'primary_database_name' ,
{ [ @.delete_secondaries = ] delete_secondaries }
Arguments
[@.primary_server_name =] 'primary_server_name'
Is the name of the primary server. primary_server_name is sysname.
[@.primary_database_name =] 'primary_database_name'
Is the name of the secondary server. primary_database_name is sysname.
[@.delete_secondaries =] delete_secondaries
Specifies that the delete action is also applied to log_shipping_secondaries
table. delete_secondaries is bit, with a default of zero (0).
Return Code Values
0 (success) or 1 (failure)
Remarks
This stored procedure only removes the primary and secondary server from the
monitor. Log shipping still has to be removed from the primary and secondar
y servers.
sp_delete_log_shipping_primary deletes a log shipping primary table. If ther
e are corresponding rows in the log_shipping_databases table, delete_seconda
ries must be set to one (1) or the stored procedure will fail.
Permissions
Only members of the sysadmin fixed server role can execute sp_delete_log_shi
pping_primaries.
Examples
This example deletes the source database "pubs" from the server "source". Th
ere are no corresponding rows in log_shipping_secondaries table.
EXEC sp_delete_log_shipping_primary @.primary_server_name = N'source', @.pri
mary_database_name = N'pubs'
?1988-2000 Microsoft Corporation. All Rights Reserved.|||Actually, when nothing has gone wrong, the easiest way is in Enterprise
Manager:
Management->Database Maintenance Plans->Right-Click->Properties->Log
Shipping->Remove Log Shipping.
Then delete Plan.
Jerome
"Jerome Smith" <jerosmith@.hotmail.com> escribi en el mensaje
news:3B6447C3-7819-4F31-983B-7BDD84D2311F@.microsoft.com...
> Hello all,
> I'll answer my own question for everybody's benefit. Eliminating the Log
Shipping Monitor is equivalent to eliminating all Log Shipping pairs from
the Log Shipping system database. This is done with the stored procedure:
> sp_delete_log_shipping_primary
> located in the msdb system database.
> Following is the help article.
> Cheers,
> Jerome
> sp_delete_log_shipping_primary
> Deletes the primary server from the log_shipping_primaries table.
> Syntax
> sp_delete_log_shipping_primary
> [ @.primary_server_name = ] 'primary_server_name' ,
> [ @.primary_database_name = ] 'primary_database_name' ,
> { [ @.delete_secondaries = ] delete_secondaries }
> Arguments
> [@.primary_server_name =] 'primary_server_name'
> Is the name of the primary server. primary_server_name is sysname.
> [@.primary_database_name =] 'primary_database_name'
> Is the name of the secondary server. primary_database_name is sysname.
> [@.delete_secondaries =] delete_secondaries
> Specifies that the delete action is also applied to
log_shipping_secondaries table. delete_secondaries is bit, with a default of
zero (0).
> Return Code Values
> 0 (success) or 1 (failure)
> Remarks
> This stored procedure only removes the primary and secondary server from
the monitor. Log shipping still has to be removed from the primary and
secondary servers.
> sp_delete_log_shipping_primary deletes a log shipping primary table. If
there are corresponding rows in the log_shipping_databases table,
delete_secondaries must be set to one (1) or the stored procedure will fail.
> Permissions
> Only members of the sysadmin fixed server role can execute
sp_delete_log_shipping_primaries.
> Examples
> This example deletes the source database "pubs" from the server "source".
There are no corresponding rows in log_shipping_secondaries table.
> EXEC sp_delete_log_shipping_primary @.primary_server_name = N'source',
@.primary_database_name = N'pubs'
> 1988-2000 Microsoft Corporation. All Rights Reserved.
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.629 / Virus Database: 403 - Release Date: 17-03-2004

Log Shipping Error

After studying Log Shipping last week and reading up on it, it appears to be
the best solution for us. I am using to test servers to get it up and
running before releasing it to production.
I upgraded both servers from Standard to Enterprise edition. This allows me
the capabilities of using the DB Maint. Wizard to setup Log Shipping.
However, in setting it up, I am now encountering errors. I have researched
the errors on MS website but to no avail. Here is what I am doing and
getting:
1) I step through the Wizard as discussed in
http://msdn.microsoft.com/library/de...erver_8elj.asp
2) Upon finishing the wizrd I receive the error: "Unable to copy the
initialization file to \\SOURCE\data\file.bak"
Does anyone have any ideas on this? I have provided detailed steps below as
to my wizard selections.
Thanks,
Marty
Wizard:
1) Right clicked on the database on the SOURCE server.
2) Chose DB Maint. Plan
3) Clicked Next
4) Chose my DB then checked the Log Shipping option
5) Clicked Next
6) Clicked Next until the Specify the Transaction Log Share screen came up.
In this field I chose \\SOURCE\data
7) Clicked Next
8) Clicked ADD to add log shipping destination.
9) Chose the DESTINATION server.
10) Clicked OK
11) Clicked Next
12) Chose User Most Recent Backup File \\SOURCE\data\file.bak clicked Next
13) Clicked Next to the end
14) Clicked Finish
15) Error "Unable to Copy...\\SOURCE\data\file.bak" as mentioned above.
I tried with different login options and nothing works. When I tried it with
PERFORM A FULL DATABASE BACKUP NOW on step 12, I receive this error at the
end: "ERROR 3201: Cannot open Backup device '\\SOURCE\data\file.bak'. Device
error or device off-line. See the SQL Server error log for more details.
BACKUP DATABASE is terminating abnormally."
have a look at kb article 811890.
You also don't need the Enterprise Edition to do log shipping. SQL Server EE
has a log shipping wizard but you can roll your own. Here is a link to an
article which talks about how to do this:
http://www.sql-server-performance.co...g_shipping.asp
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"M.Smith" <martys@.bennyhinn.org> wrote in message
news:ez1%23RkKKFHA.724@.TK2MSFTNGP10.phx.gbl...
> After studying Log Shipping last week and reading up on it, it appears to
be
> the best solution for us. I am using to test servers to get it up and
> running before releasing it to production.
> I upgraded both servers from Standard to Enterprise edition. This allows
me
> the capabilities of using the DB Maint. Wizard to setup Log Shipping.
> However, in setting it up, I am now encountering errors. I have researched
> the errors on MS website but to no avail. Here is what I am doing and
> getting:
> 1) I step through the Wizard as discussed in
>
http://msdn.microsoft.com/library/de...erver_8elj.asp
> 2) Upon finishing the wizrd I receive the error: "Unable to copy the
> initialization file to \\SOURCE\data\file.bak"
> Does anyone have any ideas on this? I have provided detailed steps below
as
> to my wizard selections.
> Thanks,
> Marty
> Wizard:
> 1) Right clicked on the database on the SOURCE server.
> 2) Chose DB Maint. Plan
> 3) Clicked Next
> 4) Chose my DB then checked the Log Shipping option
> 5) Clicked Next
> 6) Clicked Next until the Specify the Transaction Log Share screen came
up.
> In this field I chose \\SOURCE\data
> 7) Clicked Next
> 8) Clicked ADD to add log shipping destination.
> 9) Chose the DESTINATION server.
> 10) Clicked OK
> 11) Clicked Next
> 12) Chose User Most Recent Backup File \\SOURCE\data\file.bak clicked Next
> 13) Clicked Next to the end
> 14) Clicked Finish
> 15) Error "Unable to Copy...\\SOURCE\data\file.bak" as mentioned above.
> I tried with different login options and nothing works. When I tried it
with
> PERFORM A FULL DATABASE BACKUP NOW on step 12, I receive this error at the
> end: "ERROR 3201: Cannot open Backup device '\\SOURCE\data\file.bak'.
Device
> error or device off-line. See the SQL Server error log for more details.
> BACKUP DATABASE is terminating abnormally."
>
|||Thanks for the response Hilary...Acutally I have already read both of those
articles :-) I started my research with the one at sql-server-performance
and after researching thought that the wizard in EE would be easier.
So, I will step back and try to do it manually and see how that turns out.
Thanks again,
Marty
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ecNEp4KKFHA.1172@.TK2MSFTNGP12.phx.gbl...
> have a look at kb article 811890.
> You also don't need the Enterprise Edition to do log shipping. SQL Server
> EE
> has a log shipping wizard but you can roll your own. Here is a link to an
> article which talks about how to do this:
> http://www.sql-server-performance.co...g_shipping.asp
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "M.Smith" <martys@.bennyhinn.org> wrote in message
> news:ez1%23RkKKFHA.724@.TK2MSFTNGP10.phx.gbl...
> be
> me
> http://msdn.microsoft.com/library/de...erver_8elj.asp
> as
> up.
> with
> Device
>