I've been playing with the SQL 2005 log shipping and have been running into a
few problems.
My system is configured on a virtual server with two instances of SQL 2005.
The OS is Windows 2003. I'm shipping logs from the default instance to the
named instance with the default having the primary database and the named,
the secondary.
When I use the log shipping wizard, and have it do the initial restore to
the secondary, all appears to work well but the database remains in
"(Restoring ...) in the Management Studio (MS) GUI. So after starting over
and manually restoring the database to the secondary, I used the wizard again
to set this up and now the Agent jobs indicate success in applying the logs
but again, the database continues to show as (Restoring ...) in the MS GUI.
It is indeed unavailable as you can't query it in a Query window. I tried
this with upgraded SQL 2000 databases and newly created SQL 2005 databases,
both with the same result.
Has anyone run into this and can you tell me what might be happening?
Hi
By default, you can not access the destination DB in a log shipping
partnership. It was the same in SQL Server 2000.
If you want to have read only access, subject to having your connections
dropped when the next log restore occurs,
you need to execute a restore with the additional parameter of "STANDBY =
{standby_file_name}".
This can not be done in the GUI.
What are you trying to achieve?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dlorbecki" <Dlorbecki@.discussions.microsoft.com> wrote in message
news:4262DE8E-343E-4C2C-B2D6-43A5D6A425A3@.microsoft.com...
> I've been playing with the SQL 2005 log shipping and have been running
> into a
> few problems.
> My system is configured on a virtual server with two instances of SQL
> 2005.
> The OS is Windows 2003. I'm shipping logs from the default instance to the
> named instance with the default having the primary database and the named,
> the secondary.
> When I use the log shipping wizard, and have it do the initial restore to
> the secondary, all appears to work well but the database remains in
> "(Restoring ...) in the Management Studio (MS) GUI. So after starting over
> and manually restoring the database to the secondary, I used the wizard
> again
> to set this up and now the Agent jobs indicate success in applying the
> logs
> but again, the database continues to show as (Restoring ...) in the MS
> GUI.
> It is indeed unavailable as you can't query it in a Query window. I tried
> this with upgraded SQL 2000 databases and newly created SQL 2005
> databases,
> both with the same result.
> Has anyone run into this and can you tell me what might be happening?
|||My goal was to have a readable database on the destination side that could be
accessible in case of failure of the primary database. I guess I'm ignorant
of a basic tenent of log shipping and for that I apologize. Apparently, the
process is working as it should be and I thank you for pointing that out. (I
never used log shipping in 2000 since we only have standard edition). I will
"read up" on the subject a bit more. Thanks much!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> By default, you can not access the destination DB in a log shipping
> partnership. It was the same in SQL Server 2000.
> If you want to have read only access, subject to having your connections
> dropped when the next log restore occurs,
> you need to execute a restore with the additional parameter of "STANDBY =
> {standby_file_name}".
> This can not be done in the GUI.
> What are you trying to achieve?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dlorbecki" <Dlorbecki@.discussions.microsoft.com> wrote in message
> news:4262DE8E-343E-4C2C-B2D6-43A5D6A425A3@.microsoft.com...
>
>
Showing posts with label playing. Show all posts
Showing posts with label playing. Show all posts
Wednesday, March 28, 2012
log shipping sql 2005 database remains in (restoring...) mode
I've been playing with the SQL 2005 log shipping and have been running into
a
few problems.
My system is configured on a virtual server with two instances of SQL 2005.
The OS is Windows 2003. I'm shipping logs from the default instance to the
named instance with the default having the primary database and the named,
the secondary.
When I use the log shipping wizard, and have it do the initial restore to
the secondary, all appears to work well but the database remains in
"(Restoring ...) in the Management Studio (MS) GUI. So after starting over
and manually restoring the database to the secondary, I used the wizard agai
n
to set this up and now the Agent jobs indicate success in applying the logs
but again, the database continues to show as (Restoring ...) in the MS GUI.
It is indeed unavailable as you can't query it in a Query window. I tried
this with upgraded SQL 2000 databases and newly created SQL 2005 databases,
both with the same result.
Has anyone run into this and can you tell me what might be happening?Hi
By default, you can not access the destination DB in a log shipping
partnership. It was the same in SQL Server 2000.
If you want to have read only access, subject to having your connections
dropped when the next log restore occurs,
you need to execute a restore with the additional parameter of "STANDBY =
{standby_file_name}".
This can not be done in the GUI.
What are you trying to achieve?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dlorbecki" <Dlorbecki@.discussions.microsoft.com> wrote in message
news:4262DE8E-343E-4C2C-B2D6-43A5D6A425A3@.microsoft.com...
> I've been playing with the SQL 2005 log shipping and have been running
> into a
> few problems.
> My system is configured on a virtual server with two instances of SQL
> 2005.
> The OS is Windows 2003. I'm shipping logs from the default instance to the
> named instance with the default having the primary database and the named,
> the secondary.
> When I use the log shipping wizard, and have it do the initial restore to
> the secondary, all appears to work well but the database remains in
> "(Restoring ...) in the Management Studio (MS) GUI. So after starting over
> and manually restoring the database to the secondary, I used the wizard
> again
> to set this up and now the Agent jobs indicate success in applying the
> logs
> but again, the database continues to show as (Restoring ...) in the MS
> GUI.
> It is indeed unavailable as you can't query it in a Query window. I tried
> this with upgraded SQL 2000 databases and newly created SQL 2005
> databases,
> both with the same result.
> Has anyone run into this and can you tell me what might be happening?|||My goal was to have a readable database on the destination side that could b
e
accessible in case of failure of the primary database. I guess I'm ignorant
of a basic tenent of log shipping and for that I apologize. Apparently, the
process is working as it should be and I thank you for pointing that out. (
I
never used log shipping in 2000 since we only have standard edition). I wil
l
"read up" on the subject a bit more. Thanks much!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> By default, you can not access the destination DB in a log shipping
> partnership. It was the same in SQL Server 2000.
> If you want to have read only access, subject to having your connections
> dropped when the next log restore occurs,
> you need to execute a restore with the additional parameter of "STANDBY =
> {standby_file_name}".
> This can not be done in the GUI.
> What are you trying to achieve?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dlorbecki" <Dlorbecki@.discussions.microsoft.com> wrote in message
> news:4262DE8E-343E-4C2C-B2D6-43A5D6A425A3@.microsoft.com...
>
>
a
few problems.
My system is configured on a virtual server with two instances of SQL 2005.
The OS is Windows 2003. I'm shipping logs from the default instance to the
named instance with the default having the primary database and the named,
the secondary.
When I use the log shipping wizard, and have it do the initial restore to
the secondary, all appears to work well but the database remains in
"(Restoring ...) in the Management Studio (MS) GUI. So after starting over
and manually restoring the database to the secondary, I used the wizard agai
n
to set this up and now the Agent jobs indicate success in applying the logs
but again, the database continues to show as (Restoring ...) in the MS GUI.
It is indeed unavailable as you can't query it in a Query window. I tried
this with upgraded SQL 2000 databases and newly created SQL 2005 databases,
both with the same result.
Has anyone run into this and can you tell me what might be happening?Hi
By default, you can not access the destination DB in a log shipping
partnership. It was the same in SQL Server 2000.
If you want to have read only access, subject to having your connections
dropped when the next log restore occurs,
you need to execute a restore with the additional parameter of "STANDBY =
{standby_file_name}".
This can not be done in the GUI.
What are you trying to achieve?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dlorbecki" <Dlorbecki@.discussions.microsoft.com> wrote in message
news:4262DE8E-343E-4C2C-B2D6-43A5D6A425A3@.microsoft.com...
> I've been playing with the SQL 2005 log shipping and have been running
> into a
> few problems.
> My system is configured on a virtual server with two instances of SQL
> 2005.
> The OS is Windows 2003. I'm shipping logs from the default instance to the
> named instance with the default having the primary database and the named,
> the secondary.
> When I use the log shipping wizard, and have it do the initial restore to
> the secondary, all appears to work well but the database remains in
> "(Restoring ...) in the Management Studio (MS) GUI. So after starting over
> and manually restoring the database to the secondary, I used the wizard
> again
> to set this up and now the Agent jobs indicate success in applying the
> logs
> but again, the database continues to show as (Restoring ...) in the MS
> GUI.
> It is indeed unavailable as you can't query it in a Query window. I tried
> this with upgraded SQL 2000 databases and newly created SQL 2005
> databases,
> both with the same result.
> Has anyone run into this and can you tell me what might be happening?|||My goal was to have a readable database on the destination side that could b
e
accessible in case of failure of the primary database. I guess I'm ignorant
of a basic tenent of log shipping and for that I apologize. Apparently, the
process is working as it should be and I thank you for pointing that out. (
I
never used log shipping in 2000 since we only have standard edition). I wil
l
"read up" on the subject a bit more. Thanks much!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> By default, you can not access the destination DB in a log shipping
> partnership. It was the same in SQL Server 2000.
> If you want to have read only access, subject to having your connections
> dropped when the next log restore occurs,
> you need to execute a restore with the additional parameter of "STANDBY =
> {standby_file_name}".
> This can not be done in the GUI.
> What are you trying to achieve?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dlorbecki" <Dlorbecki@.discussions.microsoft.com> wrote in message
> news:4262DE8E-343E-4C2C-B2D6-43A5D6A425A3@.microsoft.com...
>
>
log shipping sql 2005 database remains in (restoring...) mode
I've been playing with the SQL 2005 log shipping and have been running into a
few problems.
My system is configured on a virtual server with two instances of SQL 2005.
The OS is Windows 2003. I'm shipping logs from the default instance to the
named instance with the default having the primary database and the named,
the secondary.
When I use the log shipping wizard, and have it do the initial restore to
the secondary, all appears to work well but the database remains in
"(Restoring ...) in the Management Studio (MS) GUI. So after starting over
and manually restoring the database to the secondary, I used the wizard again
to set this up and now the Agent jobs indicate success in applying the logs
but again, the database continues to show as (Restoring ...) in the MS GUI.
It is indeed unavailable as you can't query it in a Query window. I tried
this with upgraded SQL 2000 databases and newly created SQL 2005 databases,
both with the same result.
Has anyone run into this and can you tell me what might be happening?Hi
By default, you can not access the destination DB in a log shipping
partnership. It was the same in SQL Server 2000.
If you want to have read only access, subject to having your connections
dropped when the next log restore occurs,
you need to execute a restore with the additional parameter of "STANDBY ={standby_file_name}".
This can not be done in the GUI.
What are you trying to achieve?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dlorbecki" <Dlorbecki@.discussions.microsoft.com> wrote in message
news:4262DE8E-343E-4C2C-B2D6-43A5D6A425A3@.microsoft.com...
> I've been playing with the SQL 2005 log shipping and have been running
> into a
> few problems.
> My system is configured on a virtual server with two instances of SQL
> 2005.
> The OS is Windows 2003. I'm shipping logs from the default instance to the
> named instance with the default having the primary database and the named,
> the secondary.
> When I use the log shipping wizard, and have it do the initial restore to
> the secondary, all appears to work well but the database remains in
> "(Restoring ...) in the Management Studio (MS) GUI. So after starting over
> and manually restoring the database to the secondary, I used the wizard
> again
> to set this up and now the Agent jobs indicate success in applying the
> logs
> but again, the database continues to show as (Restoring ...) in the MS
> GUI.
> It is indeed unavailable as you can't query it in a Query window. I tried
> this with upgraded SQL 2000 databases and newly created SQL 2005
> databases,
> both with the same result.
> Has anyone run into this and can you tell me what might be happening?|||My goal was to have a readable database on the destination side that could be
accessible in case of failure of the primary database. I guess I'm ignorant
of a basic tenent of log shipping and for that I apologize. Apparently, the
process is working as it should be and I thank you for pointing that out. (I
never used log shipping in 2000 since we only have standard edition). I will
"read up" on the subject a bit more. Thanks much!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> By default, you can not access the destination DB in a log shipping
> partnership. It was the same in SQL Server 2000.
> If you want to have read only access, subject to having your connections
> dropped when the next log restore occurs,
> you need to execute a restore with the additional parameter of "STANDBY => {standby_file_name}".
> This can not be done in the GUI.
> What are you trying to achieve?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dlorbecki" <Dlorbecki@.discussions.microsoft.com> wrote in message
> news:4262DE8E-343E-4C2C-B2D6-43A5D6A425A3@.microsoft.com...
> > I've been playing with the SQL 2005 log shipping and have been running
> > into a
> > few problems.
> >
> > My system is configured on a virtual server with two instances of SQL
> > 2005.
> > The OS is Windows 2003. I'm shipping logs from the default instance to the
> > named instance with the default having the primary database and the named,
> > the secondary.
> >
> > When I use the log shipping wizard, and have it do the initial restore to
> > the secondary, all appears to work well but the database remains in
> > "(Restoring ...) in the Management Studio (MS) GUI. So after starting over
> > and manually restoring the database to the secondary, I used the wizard
> > again
> > to set this up and now the Agent jobs indicate success in applying the
> > logs
> > but again, the database continues to show as (Restoring ...) in the MS
> > GUI.
> > It is indeed unavailable as you can't query it in a Query window. I tried
> > this with upgraded SQL 2000 databases and newly created SQL 2005
> > databases,
> > both with the same result.
> >
> > Has anyone run into this and can you tell me what might be happening?
>
>
few problems.
My system is configured on a virtual server with two instances of SQL 2005.
The OS is Windows 2003. I'm shipping logs from the default instance to the
named instance with the default having the primary database and the named,
the secondary.
When I use the log shipping wizard, and have it do the initial restore to
the secondary, all appears to work well but the database remains in
"(Restoring ...) in the Management Studio (MS) GUI. So after starting over
and manually restoring the database to the secondary, I used the wizard again
to set this up and now the Agent jobs indicate success in applying the logs
but again, the database continues to show as (Restoring ...) in the MS GUI.
It is indeed unavailable as you can't query it in a Query window. I tried
this with upgraded SQL 2000 databases and newly created SQL 2005 databases,
both with the same result.
Has anyone run into this and can you tell me what might be happening?Hi
By default, you can not access the destination DB in a log shipping
partnership. It was the same in SQL Server 2000.
If you want to have read only access, subject to having your connections
dropped when the next log restore occurs,
you need to execute a restore with the additional parameter of "STANDBY ={standby_file_name}".
This can not be done in the GUI.
What are you trying to achieve?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dlorbecki" <Dlorbecki@.discussions.microsoft.com> wrote in message
news:4262DE8E-343E-4C2C-B2D6-43A5D6A425A3@.microsoft.com...
> I've been playing with the SQL 2005 log shipping and have been running
> into a
> few problems.
> My system is configured on a virtual server with two instances of SQL
> 2005.
> The OS is Windows 2003. I'm shipping logs from the default instance to the
> named instance with the default having the primary database and the named,
> the secondary.
> When I use the log shipping wizard, and have it do the initial restore to
> the secondary, all appears to work well but the database remains in
> "(Restoring ...) in the Management Studio (MS) GUI. So after starting over
> and manually restoring the database to the secondary, I used the wizard
> again
> to set this up and now the Agent jobs indicate success in applying the
> logs
> but again, the database continues to show as (Restoring ...) in the MS
> GUI.
> It is indeed unavailable as you can't query it in a Query window. I tried
> this with upgraded SQL 2000 databases and newly created SQL 2005
> databases,
> both with the same result.
> Has anyone run into this and can you tell me what might be happening?|||My goal was to have a readable database on the destination side that could be
accessible in case of failure of the primary database. I guess I'm ignorant
of a basic tenent of log shipping and for that I apologize. Apparently, the
process is working as it should be and I thank you for pointing that out. (I
never used log shipping in 2000 since we only have standard edition). I will
"read up" on the subject a bit more. Thanks much!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> By default, you can not access the destination DB in a log shipping
> partnership. It was the same in SQL Server 2000.
> If you want to have read only access, subject to having your connections
> dropped when the next log restore occurs,
> you need to execute a restore with the additional parameter of "STANDBY => {standby_file_name}".
> This can not be done in the GUI.
> What are you trying to achieve?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dlorbecki" <Dlorbecki@.discussions.microsoft.com> wrote in message
> news:4262DE8E-343E-4C2C-B2D6-43A5D6A425A3@.microsoft.com...
> > I've been playing with the SQL 2005 log shipping and have been running
> > into a
> > few problems.
> >
> > My system is configured on a virtual server with two instances of SQL
> > 2005.
> > The OS is Windows 2003. I'm shipping logs from the default instance to the
> > named instance with the default having the primary database and the named,
> > the secondary.
> >
> > When I use the log shipping wizard, and have it do the initial restore to
> > the secondary, all appears to work well but the database remains in
> > "(Restoring ...) in the Management Studio (MS) GUI. So after starting over
> > and manually restoring the database to the secondary, I used the wizard
> > again
> > to set this up and now the Agent jobs indicate success in applying the
> > logs
> > but again, the database continues to show as (Restoring ...) in the MS
> > GUI.
> > It is indeed unavailable as you can't query it in a Query window. I tried
> > this with upgraded SQL 2000 databases and newly created SQL 2005
> > databases,
> > both with the same result.
> >
> > Has anyone run into this and can you tell me what might be happening?
>
>
Monday, March 26, 2012
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
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
Subscribe to:
Comments (Atom)