Hi,
Is it possible to sepup log shipping for two databases on one server?
I have already setup logshipping for one database and it is working
fine. I need to setup logshipping for the second database that is on
the
same primary server. But while doing this is using the same schedule
for both databases but i want different schedules.
Can I logship it to same secondary server?
Thanks.
ParveenYes. Can you describe what you are doing when you set this up?
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
<Parveen.Beniwal.Daffodil@.gmail.com> wrote in message
news:1146027423.468492.140520@.j33g2000cwa.googlegroups.com...
> Hi,
> Is it possible to sepup log shipping for two databases on one server?
> I have already setup logshipping for one database and it is working
> fine. I need to setup logshipping for the second database that is on
> the
> same primary server. But while doing this is using the same schedule
> for both databases but i want different schedules.
> Can I logship it to same secondary server?
> Thanks.
> Parveen
>|||Hi mike
yes sure,I am having a database Test on SERVER1 and i want to
transfer Log backups of Test to a secondry Server SERVER2 on Databases
Test and Test1 both are read-only restored databases. Jobs according to
that configuration are as follows:
SERVER1:
LSAlert_SERVER1
LSBackup_Test
SERVER2:
LSAlert_SERVER1
LSCopy_SERVER1_Test
LSRestore_SERVER1_Test
As above SERVER2 is having jobs only for one database not for the
second one that is Test1 to which i want to restore at some different
schedule. But i want log shpping for the both databases on a different
schedule.
How it is possible ?
Parveen|||I'm not sure what isn't working. In Management Studio, right click on the
Test database, select properties, and set it up for log shipping. Then rigt
click the test1 database, select properties, and set it up for log shipping.
A separate backup job is createdfor each database. You also get a separate
copy and restore job for each database. So, there are no issues with having
differet schedules.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Parv" <Parveen.Beniwal.Daffodil@.gmail.com> wrote in message
news:1146197628.535252.55870@.u72g2000cwu.googlegroups.com...
> Hi mike
> yes sure,I am having a database Test on SERVER1 and i want to
> transfer Log backups of Test to a secondry Server SERVER2 on Databases
> Test and Test1 both are read-only restored databases. Jobs according to
> that configuration are as follows:
> SERVER1:
> LSAlert_SERVER1
> LSBackup_Test
> SERVER2:
> LSAlert_SERVER1
> LSCopy_SERVER1_Test
> LSRestore_SERVER1_Test
> As above SERVER2 is having jobs only for one database not for the
> second one that is Test1 to which i want to restore at some different
> schedule. But i want log shpping for the both databases on a different
> schedule.
> How it is possible ?
> Parveen
>|||Hi mike
I think you are not getting my problem i want to transport
backup of Test on SERVER1 to two databases Test and Test1 on the
SERVER2 on a different schedule. I mean
SERVER1:
Test publisher databases whose Backup is to take.
SERVER2:
Test restore backup here after every 45 mins
Test1 restore backup here after every 75 mins
But jobs on the SERVER2 are created for the restoration of the
Test on Test only not for the Test1 Database. How it is possible ?
Parveen|||Right click the database you are backing up. Select properties. Select the
Transaction Log Shipping tab. Look in the middle of the screen. You can
add as many targets to this as you choose to add. So, you just have to add
another entry to this for the other database and it can be configured with
its own schedule.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Parv" <Parveen.Beniwal.Daffodil@.gmail.com> wrote in message
news:1146206287.127394.183270@.i39g2000cwa.googlegroups.com...
> Hi mike
> I think you are not getting my problem i want to transport
> backup of Test on SERVER1 to two databases Test and Test1 on the
> SERVER2 on a different schedule. I mean
> SERVER1:
> Test publisher databases whose Backup is to take.
> SERVER2:
> Test restore backup here after every 45 mins
> Test1 restore backup here after every 75 mins
> But jobs on the SERVER2 are created for the restoration of the
> Test on Test only not for the Test1 Database. How it is possible ?
> Parveen
>
Showing posts with label working. Show all posts
Showing posts with label working. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Log shipping seems to fail because of database size
Hi,
I am having a problem setting up log shipping for a large, 45GB,
database. I had log shipping working for this database. Then one
evening it failed because it could not apply a log file. I cleaned up
by removing log shipping from the maintenance plan, deleting the plans,
jobs, suspect destination database, log files and entries in the msdb
log shipping tables. When I try and re-establish log shipping for the
same database I get the error "unable to copy the initialization file
to the secondary server". I can see the initial backup file being
created on the source server and being copied to the destination server
but at some point it fails. What is strange is that I can still get log
shipping working for a small test database in exactly the same
environment and using the same steps.
Most of the posts on this error I have read suggest that permissions
are at the root. However, I cannot see how permissions can be causing
my problem as I can get it to work for a small database. It seems as
though size is the issue! Has anyone else experienced such a problem?
Any help would be very gratefully received as I've been struggling with
this one for a few days now.
Thanks in advance,
Gavin
have you thought about integrating backup compression software with log
shipping - like LiteSpeed, or RedGate's SQL Backup? These should be able to
get you around this size problem. I find also I have more control when I
roll my own log shipping solution as opposed to using the log shipping
wizard.
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
<geetastic@.hotmail.com> wrote in message
news:1169489699.082941.198830@.l53g2000cwa.googlegr oups.com...
> Hi,
> I am having a problem setting up log shipping for a large, 45GB,
> database. I had log shipping working for this database. Then one
> evening it failed because it could not apply a log file. I cleaned up
> by removing log shipping from the maintenance plan, deleting the plans,
> jobs, suspect destination database, log files and entries in the msdb
> log shipping tables. When I try and re-establish log shipping for the
> same database I get the error "unable to copy the initialization file
> to the secondary server". I can see the initial backup file being
> created on the source server and being copied to the destination server
> but at some point it fails. What is strange is that I can still get log
> shipping working for a small test database in exactly the same
> environment and using the same steps.
> Most of the posts on this error I have read suggest that permissions
> are at the root. However, I cannot see how permissions can be causing
> my problem as I can get it to work for a small database. It seems as
> though size is the issue! Has anyone else experienced such a problem?
> Any help would be very gratefully received as I've been struggling with
> this one for a few days now.
> Thanks in advance,
> Gavin
>
|||Hi Hilary,
Thank you for this. Yes I have thought of using these tools but have
been put off by having already spent a fortune on 2 * 4CPU licences for
SQL Server 2000 Enterprise and having already had this database log
shipping happily for over 2 years in another hosting environment. If we
have to use some 3rd party software like this then we have to but it
just seems wrong.
Which of these 2 have you used? Have you found them to be reliable?
Cheers,
Gavin
Hilary Cotter wrote:
> have you thought about integrating backup compression software with log
> shipping - like LiteSpeed, or RedGate's SQL Backup? These should be able to
> get you around this size problem. I find also I have more control when I
> roll my own log shipping solution as opposed to using the log shipping
> wizard.
> --
|||I've used lite speed and it has hooks built into it that will work with the
log shipping wizard.
SQL Backup from RedGate is a highly competitive product - cheaper too.
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
"Geetastic" <geetastic@.hotmail.com> wrote in message
news:1169541651.197139.116160@.s48g2000cws.googlegr oups.com...
> Hi Hilary,
> Thank you for this. Yes I have thought of using these tools but have
> been put off by having already spent a fortune on 2 * 4CPU licences for
> SQL Server 2000 Enterprise and having already had this database log
> shipping happily for over 2 years in another hosting environment. If we
> have to use some 3rd party software like this then we have to but it
> just seems wrong.
> Which of these 2 have you used? Have you found them to be reliable?
> Cheers,
> Gavin
> Hilary Cotter wrote:
>
|||Fixed the problem by rebooting both servers! I didn't want to do this
straight away because this is not something I want to have to do in
production. However, it works.
Thanks for your help.
On Jan 23, 1:11 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> I've used lite speed and it has hooks built into it that will work with the
> log shipping wizard.
> SQL Backup from RedGate is a highly competitive product - cheaper too.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Geetastic" <geetas...@.hotmail.com> wrote in messagenews:1169541651.197139.116160@.s48g2000cws.g ooglegroups.com...
>
>
>
>
I am having a problem setting up log shipping for a large, 45GB,
database. I had log shipping working for this database. Then one
evening it failed because it could not apply a log file. I cleaned up
by removing log shipping from the maintenance plan, deleting the plans,
jobs, suspect destination database, log files and entries in the msdb
log shipping tables. When I try and re-establish log shipping for the
same database I get the error "unable to copy the initialization file
to the secondary server". I can see the initial backup file being
created on the source server and being copied to the destination server
but at some point it fails. What is strange is that I can still get log
shipping working for a small test database in exactly the same
environment and using the same steps.
Most of the posts on this error I have read suggest that permissions
are at the root. However, I cannot see how permissions can be causing
my problem as I can get it to work for a small database. It seems as
though size is the issue! Has anyone else experienced such a problem?
Any help would be very gratefully received as I've been struggling with
this one for a few days now.
Thanks in advance,
Gavin
have you thought about integrating backup compression software with log
shipping - like LiteSpeed, or RedGate's SQL Backup? These should be able to
get you around this size problem. I find also I have more control when I
roll my own log shipping solution as opposed to using the log shipping
wizard.
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
<geetastic@.hotmail.com> wrote in message
news:1169489699.082941.198830@.l53g2000cwa.googlegr oups.com...
> Hi,
> I am having a problem setting up log shipping for a large, 45GB,
> database. I had log shipping working for this database. Then one
> evening it failed because it could not apply a log file. I cleaned up
> by removing log shipping from the maintenance plan, deleting the plans,
> jobs, suspect destination database, log files and entries in the msdb
> log shipping tables. When I try and re-establish log shipping for the
> same database I get the error "unable to copy the initialization file
> to the secondary server". I can see the initial backup file being
> created on the source server and being copied to the destination server
> but at some point it fails. What is strange is that I can still get log
> shipping working for a small test database in exactly the same
> environment and using the same steps.
> Most of the posts on this error I have read suggest that permissions
> are at the root. However, I cannot see how permissions can be causing
> my problem as I can get it to work for a small database. It seems as
> though size is the issue! Has anyone else experienced such a problem?
> Any help would be very gratefully received as I've been struggling with
> this one for a few days now.
> Thanks in advance,
> Gavin
>
|||Hi Hilary,
Thank you for this. Yes I have thought of using these tools but have
been put off by having already spent a fortune on 2 * 4CPU licences for
SQL Server 2000 Enterprise and having already had this database log
shipping happily for over 2 years in another hosting environment. If we
have to use some 3rd party software like this then we have to but it
just seems wrong.
Which of these 2 have you used? Have you found them to be reliable?
Cheers,
Gavin
Hilary Cotter wrote:
> have you thought about integrating backup compression software with log
> shipping - like LiteSpeed, or RedGate's SQL Backup? These should be able to
> get you around this size problem. I find also I have more control when I
> roll my own log shipping solution as opposed to using the log shipping
> wizard.
> --
|||I've used lite speed and it has hooks built into it that will work with the
log shipping wizard.
SQL Backup from RedGate is a highly competitive product - cheaper too.
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
"Geetastic" <geetastic@.hotmail.com> wrote in message
news:1169541651.197139.116160@.s48g2000cws.googlegr oups.com...
> Hi Hilary,
> Thank you for this. Yes I have thought of using these tools but have
> been put off by having already spent a fortune on 2 * 4CPU licences for
> SQL Server 2000 Enterprise and having already had this database log
> shipping happily for over 2 years in another hosting environment. If we
> have to use some 3rd party software like this then we have to but it
> just seems wrong.
> Which of these 2 have you used? Have you found them to be reliable?
> Cheers,
> Gavin
> Hilary Cotter wrote:
>
|||Fixed the problem by rebooting both servers! I didn't want to do this
straight away because this is not something I want to have to do in
production. However, it works.
Thanks for your help.
On Jan 23, 1:11 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> I've used lite speed and it has hooks built into it that will work with the
> log shipping wizard.
> SQL Backup from RedGate is a highly competitive product - cheaper too.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Geetastic" <geetas...@.hotmail.com> wrote in messagenews:1169541651.197139.116160@.s48g2000cws.g ooglegroups.com...
>
>
>
>
Log Shipping Role Reversal
I have log shipping configured on a pair of test SQL 2000 servers, both are
at SP3. The log shipping is working fine, but the problem occurs when I try
to reverse roles. I run this SQL statement on the primary server:
USE master
GO
EXEC msdb.dbo.sp_change_primary_role
@.db_name = 'ModelCopy',
@.backup_log = 1,
@.terminate = 1,
@.final_state = 3,
@.access_level = 1
This places the primary database into read-only mode. I then run this SQL
statement on the secondary server.
USE master
GO
EXEC msdb.dbo.sp_change_secondary_role
@.db_name = 'ModelCopy',
@.do_load = 1,
@.force_load = 1,
@.final_state = 1,
@.access_level = 1,
@.terminate = 1,
@.keep_replication = 0,
@.stopat = null
At which time I get this error:
Server: Msg 927, Level 14, State 2, Line 1
Database 'ModelCopy' cannot be opened. It is in the middle of a restore.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role,
Line 49
Could not set single user mode.
In each case, I am runnig the statements in Query Analyzer with the
Enterprise Manager window closed. I understand that these statements cause
the primary to make a final copy and the secondary to do a final restore, but
the database I'm using is very small (a copy of the Model databae) and
shouldn't take but a few seconds, which is all the time it took to create and
initialize the database on the secondary to begin with. Is this process hung,
or am I just being impatient? It's been over 20 minutes.
TIA,
Ken
Ken,
See the KB article 294397 BUG: sp_change_secondary_role Fails with Error
3101 if There Are Outstanding Transaction Log Backups
http://support.microsoft.com/default...&Product=sql2k
This is similar, though not identical, to the error you are getting. The
upshot is that you need to make sure that all the transaction logs have been
restored if you set the @.terminate parameter to 1.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
news:733456B1-BC99-43B2-B6BD-4030A08DB6EC@.microsoft.com...
> I have log shipping configured on a pair of test SQL 2000 servers, both
are
> at SP3. The log shipping is working fine, but the problem occurs when I
try
> to reverse roles. I run this SQL statement on the primary server:
> USE master
> GO
> EXEC msdb.dbo.sp_change_primary_role
> @.db_name = 'ModelCopy',
> @.backup_log = 1,
> @.terminate = 1,
> @.final_state = 3,
> @.access_level = 1
> This places the primary database into read-only mode. I then run this SQL
> statement on the secondary server.
> USE master
> GO
> EXEC msdb.dbo.sp_change_secondary_role
> @.db_name = 'ModelCopy',
> @.do_load = 1,
> @.force_load = 1,
> @.final_state = 1,
> @.access_level = 1,
> @.terminate = 1,
> @.keep_replication = 0,
> @.stopat = null
> At which time I get this error:
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'ModelCopy' cannot be opened. It is in the middle of a restore.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role,
> Line 49
> Could not set single user mode.
> In each case, I am runnig the statements in Query Analyzer with the
> Enterprise Manager window closed. I understand that these statements cause
> the primary to make a final copy and the secondary to do a final restore,
but
> the database I'm using is very small (a copy of the Model databae) and
> shouldn't take but a few seconds, which is all the time it took to create
and
> initialize the database on the secondary to begin with. Is this process
hung,
> or am I just being impatient? It's been over 20 minutes.
> TIA,
> Ken
|||Thanks for the info Ron. It pointed me in the right direction. I still needed
to to a detach/attach sequesnce on the database before running the secondary
stored procedure, but at least it is working in a predictable manner now.
"Ron Talmage" wrote:
> Ken,
> See the KB article 294397 BUG: sp_change_secondary_role Fails with Error
> 3101 if There Are Outstanding Transaction Log Backups
> http://support.microsoft.com/default...&Product=sql2k
> This is similar, though not identical, to the error you are getting. The
> upshot is that you need to make sure that all the transaction logs have been
> restored if you set the @.terminate parameter to 1.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
> news:733456B1-BC99-43B2-B6BD-4030A08DB6EC@.microsoft.com...
> are
> try
> but
> and
> hung,
>
>
|||OK, I spoke too soon. Occasionally, the sp_change_secondary_role procedure
will end with a "sqlmaint.exe failed. [SQLSTATE 42000][Error 22029]. The step
failed." message. I haven't been able to find any helpful information on this
message yet. Can you shed some more light on what is happening during this
procedure? Same scripts as posted previously in this thread, and include the
manual running of the restore job and a database detach/attach sequence in
Enterprise Manager immediately prior to running the secondary role change
stpred procedure.
Thanks Again,
Ken
"kmkrause2" wrote:
[vbcol=seagreen]
> Thanks for the info Ron. It pointed me in the right direction. I still needed
> to to a detach/attach sequesnce on the database before running the secondary
> stored procedure, but at least it is working in a predictable manner now.
> "Ron Talmage" wrote:
at SP3. The log shipping is working fine, but the problem occurs when I try
to reverse roles. I run this SQL statement on the primary server:
USE master
GO
EXEC msdb.dbo.sp_change_primary_role
@.db_name = 'ModelCopy',
@.backup_log = 1,
@.terminate = 1,
@.final_state = 3,
@.access_level = 1
This places the primary database into read-only mode. I then run this SQL
statement on the secondary server.
USE master
GO
EXEC msdb.dbo.sp_change_secondary_role
@.db_name = 'ModelCopy',
@.do_load = 1,
@.force_load = 1,
@.final_state = 1,
@.access_level = 1,
@.terminate = 1,
@.keep_replication = 0,
@.stopat = null
At which time I get this error:
Server: Msg 927, Level 14, State 2, Line 1
Database 'ModelCopy' cannot be opened. It is in the middle of a restore.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role,
Line 49
Could not set single user mode.
In each case, I am runnig the statements in Query Analyzer with the
Enterprise Manager window closed. I understand that these statements cause
the primary to make a final copy and the secondary to do a final restore, but
the database I'm using is very small (a copy of the Model databae) and
shouldn't take but a few seconds, which is all the time it took to create and
initialize the database on the secondary to begin with. Is this process hung,
or am I just being impatient? It's been over 20 minutes.
TIA,
Ken
Ken,
See the KB article 294397 BUG: sp_change_secondary_role Fails with Error
3101 if There Are Outstanding Transaction Log Backups
http://support.microsoft.com/default...&Product=sql2k
This is similar, though not identical, to the error you are getting. The
upshot is that you need to make sure that all the transaction logs have been
restored if you set the @.terminate parameter to 1.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
news:733456B1-BC99-43B2-B6BD-4030A08DB6EC@.microsoft.com...
> I have log shipping configured on a pair of test SQL 2000 servers, both
are
> at SP3. The log shipping is working fine, but the problem occurs when I
try
> to reverse roles. I run this SQL statement on the primary server:
> USE master
> GO
> EXEC msdb.dbo.sp_change_primary_role
> @.db_name = 'ModelCopy',
> @.backup_log = 1,
> @.terminate = 1,
> @.final_state = 3,
> @.access_level = 1
> This places the primary database into read-only mode. I then run this SQL
> statement on the secondary server.
> USE master
> GO
> EXEC msdb.dbo.sp_change_secondary_role
> @.db_name = 'ModelCopy',
> @.do_load = 1,
> @.force_load = 1,
> @.final_state = 1,
> @.access_level = 1,
> @.terminate = 1,
> @.keep_replication = 0,
> @.stopat = null
> At which time I get this error:
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'ModelCopy' cannot be opened. It is in the middle of a restore.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role,
> Line 49
> Could not set single user mode.
> In each case, I am runnig the statements in Query Analyzer with the
> Enterprise Manager window closed. I understand that these statements cause
> the primary to make a final copy and the secondary to do a final restore,
but
> the database I'm using is very small (a copy of the Model databae) and
> shouldn't take but a few seconds, which is all the time it took to create
and
> initialize the database on the secondary to begin with. Is this process
hung,
> or am I just being impatient? It's been over 20 minutes.
> TIA,
> Ken
|||Thanks for the info Ron. It pointed me in the right direction. I still needed
to to a detach/attach sequesnce on the database before running the secondary
stored procedure, but at least it is working in a predictable manner now.
"Ron Talmage" wrote:
> Ken,
> See the KB article 294397 BUG: sp_change_secondary_role Fails with Error
> 3101 if There Are Outstanding Transaction Log Backups
> http://support.microsoft.com/default...&Product=sql2k
> This is similar, though not identical, to the error you are getting. The
> upshot is that you need to make sure that all the transaction logs have been
> restored if you set the @.terminate parameter to 1.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
> news:733456B1-BC99-43B2-B6BD-4030A08DB6EC@.microsoft.com...
> are
> try
> but
> and
> hung,
>
>
|||OK, I spoke too soon. Occasionally, the sp_change_secondary_role procedure
will end with a "sqlmaint.exe failed. [SQLSTATE 42000][Error 22029]. The step
failed." message. I haven't been able to find any helpful information on this
message yet. Can you shed some more light on what is happening during this
procedure? Same scripts as posted previously in this thread, and include the
manual running of the restore job and a database detach/attach sequence in
Enterprise Manager immediately prior to running the secondary role change
stpred procedure.
Thanks Again,
Ken
"kmkrause2" wrote:
[vbcol=seagreen]
> Thanks for the info Ron. It pointed me in the right direction. I still needed
> to to a detach/attach sequesnce on the database before running the secondary
> stored procedure, but at least it is working in a predictable manner now.
> "Ron Talmage" wrote:
Monday, March 26, 2012
Log Shipping restore failing
I have a log shipping configuration set up that has been working for several weeks now. Last night starting at about 12:30 am the restore job has started failing periodically. It has gotten worse as the day has gone on, and the job history shows the same
error message each time it fails.
Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Can anyone tell me what this means? I am aware of the problem of nameing a directory with a -S in the name, and this is not the case here.
TIA,
Ken
Right-click on the maintenance plan name (not the SQL Agent Job) and select
Maintenance Plan History... for a more detailed error message.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:E8A88DA1-CAAA-45DB-A063-A4D5315D20F0@.microsoft.com...
> I have a log shipping configuration set up that has been working for
several weeks now. Last night starting at about 12:30 am the restore job has
started failing periodically. It has gotten worse as the day has gone on,
and the job history shows the same error message each time it fails.
> Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
> Can anyone tell me what this means? I am aware of the problem of nameing
a directory with a -S in the name, and this is not the case here.
> TIA,
> Ken
|||I have since found out that running indexing operations on the database while the log shipping file is being created can cause this problem, and that's exactly what happened. My next question is how do I get past a corrupted transaction log file without s
crewing up the whole chain of transaction logs? The rest are stacking up behind it every 15 minutes because this one log file won't restore.
Thanks,
Ken
|||Since the log sequence is broken, you will likely have to re-initialize log
shipping with a full backup-restore.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:DC38D04C-0745-400C-8F83-290F3EF46369@.microsoft.com...
> I have since found out that running indexing operations on the database
while the log shipping file is being created can cause this problem, and
that's exactly what happened. My next question is how do I get past a
corrupted transaction log file without screwing up the whole chain of
transaction logs? The rest are stacking up behind it every 15 minutes
because this one log file won't restore.
> Thanks,
> Ken
error message each time it fails.
Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Can anyone tell me what this means? I am aware of the problem of nameing a directory with a -S in the name, and this is not the case here.
TIA,
Ken
Right-click on the maintenance plan name (not the SQL Agent Job) and select
Maintenance Plan History... for a more detailed error message.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:E8A88DA1-CAAA-45DB-A063-A4D5315D20F0@.microsoft.com...
> I have a log shipping configuration set up that has been working for
several weeks now. Last night starting at about 12:30 am the restore job has
started failing periodically. It has gotten worse as the day has gone on,
and the job history shows the same error message each time it fails.
> Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
> Can anyone tell me what this means? I am aware of the problem of nameing
a directory with a -S in the name, and this is not the case here.
> TIA,
> Ken
|||I have since found out that running indexing operations on the database while the log shipping file is being created can cause this problem, and that's exactly what happened. My next question is how do I get past a corrupted transaction log file without s
crewing up the whole chain of transaction logs? The rest are stacking up behind it every 15 minutes because this one log file won't restore.
Thanks,
Ken
|||Since the log sequence is broken, you will likely have to re-initialize log
shipping with a full backup-restore.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:DC38D04C-0745-400C-8F83-290F3EF46369@.microsoft.com...
> I have since found out that running indexing operations on the database
while the log shipping file is being created can cause this problem, and
that's exactly what happened. My next question is how do I get past a
corrupted transaction log file without screwing up the whole chain of
transaction logs? The rest are stacking up behind it every 15 minutes
because this one log file won't restore.
> Thanks,
> Ken
Log Shipping restore failing
I have a log shipping configuration set up that has been working for several weeks now. Last night starting at about 12:30 am the restore job has started failing periodically. It has gotten worse as the day has gone on, and the job history shows the same error message each time it fails
Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed
Can anyone tell me what this means? I am aware of the problem of nameing a directory with a -S in the name, and this is not the case here
TIA
KenRight-click on the maintenance plan name (not the SQL Agent Job) and select
Maintenance Plan History... for a more detailed error message.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:E8A88DA1-CAAA-45DB-A063-A4D5315D20F0@.microsoft.com...
> I have a log shipping configuration set up that has been working for
several weeks now. Last night starting at about 12:30 am the restore job has
started failing periodically. It has gotten worse as the day has gone on,
and the job history shows the same error message each time it fails.
> Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
> Can anyone tell me what this means? I am aware of the problem of nameing
a directory with a -S in the name, and this is not the case here.
> TIA,
> Ken|||Since the log sequence is broken, you will likely have to re-initialize log
shipping with a full backup-restore.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:DC38D04C-0745-400C-8F83-290F3EF46369@.microsoft.com...
> I have since found out that running indexing operations on the database
while the log shipping file is being created can cause this problem, and
that's exactly what happened. My next question is how do I get past a
corrupted transaction log file without screwing up the whole chain of
transaction logs? The rest are stacking up behind it every 15 minutes
because this one log file won't restore.
> Thanks,
> Ken
Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed
Can anyone tell me what this means? I am aware of the problem of nameing a directory with a -S in the name, and this is not the case here
TIA
KenRight-click on the maintenance plan name (not the SQL Agent Job) and select
Maintenance Plan History... for a more detailed error message.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:E8A88DA1-CAAA-45DB-A063-A4D5315D20F0@.microsoft.com...
> I have a log shipping configuration set up that has been working for
several weeks now. Last night starting at about 12:30 am the restore job has
started failing periodically. It has gotten worse as the day has gone on,
and the job history shows the same error message each time it fails.
> Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
> Can anyone tell me what this means? I am aware of the problem of nameing
a directory with a -S in the name, and this is not the case here.
> TIA,
> Ken|||Since the log sequence is broken, you will likely have to re-initialize log
shipping with a full backup-restore.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:DC38D04C-0745-400C-8F83-290F3EF46369@.microsoft.com...
> I have since found out that running indexing operations on the database
while the log shipping file is being created can cause this problem, and
that's exactly what happened. My next question is how do I get past a
corrupted transaction log file without screwing up the whole chain of
transaction logs? The rest are stacking up behind it every 15 minutes
because this one log file won't restore.
> Thanks,
> Ken
Log Shipping restore failing
I have a log shipping configuration set up that has been working for several
weeks now. Last night starting at about 12:30 am the restore job has starte
d failing periodically. It has gotten worse as the day has gone on, and the
job history shows the same
error message each time it fails.
Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE 42
000] (Error 22029). The step failed.
Can anyone tell me what this means? I am aware of the problem of nameing a
directory with a -S in the name, and this is not the case here.
TIA,
KenRight-click on the maintenance plan name (not the SQL Agent Job) and select
Maintenance Plan History... for a more detailed error message.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:E8A88DA1-CAAA-45DB-A063-A4D5315D20F0@.microsoft.com...
> I have a log shipping configuration set up that has been working for
several weeks now. Last night starting at about 12:30 am the restore job has
started failing periodically. It has gotten worse as the day has gone on,
and the job history shows the same error message each time it fails.
> Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
> Can anyone tell me what this means? I am aware of the problem of nameing
a directory with a -S in the name, and this is not the case here.
> TIA,
> Ken|||I have since found out that running indexing operations on the database whil
e the log shipping file is being created can cause this problem, and that's
exactly what happened. My next question is how do I get past a corrupted tra
nsaction log file without s
crewing up the whole chain of transaction logs? The rest are stacking up beh
ind it every 15 minutes because this one log file won't restore.
Thanks,
Ken|||Since the log sequence is broken, you will likely have to re-initialize log
shipping with a full backup-restore.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:DC38D04C-0745-400C-8F83-290F3EF46369@.microsoft.com...
> I have since found out that running indexing operations on the database
while the log shipping file is being created can cause this problem, and
that's exactly what happened. My next question is how do I get past a
corrupted transaction log file without screwing up the whole chain of
transaction logs? The rest are stacking up behind it every 15 minutes
because this one log file won't restore.
> Thanks,
> Kensql
weeks now. Last night starting at about 12:30 am the restore job has starte
d failing periodically. It has gotten worse as the day has gone on, and the
job history shows the same
error message each time it fails.
Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE 42
000] (Error 22029). The step failed.
Can anyone tell me what this means? I am aware of the problem of nameing a
directory with a -S in the name, and this is not the case here.
TIA,
KenRight-click on the maintenance plan name (not the SQL Agent Job) and select
Maintenance Plan History... for a more detailed error message.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:E8A88DA1-CAAA-45DB-A063-A4D5315D20F0@.microsoft.com...
> I have a log shipping configuration set up that has been working for
several weeks now. Last night starting at about 12:30 am the restore job has
started failing periodically. It has gotten worse as the day has gone on,
and the job history shows the same error message each time it fails.
> Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
> Can anyone tell me what this means? I am aware of the problem of nameing
a directory with a -S in the name, and this is not the case here.
> TIA,
> Ken|||I have since found out that running indexing operations on the database whil
e the log shipping file is being created can cause this problem, and that's
exactly what happened. My next question is how do I get past a corrupted tra
nsaction log file without s
crewing up the whole chain of transaction logs? The rest are stacking up beh
ind it every 15 minutes because this one log file won't restore.
Thanks,
Ken|||Since the log sequence is broken, you will likely have to re-initialize log
shipping with a full backup-restore.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:DC38D04C-0745-400C-8F83-290F3EF46369@.microsoft.com...
> I have since found out that running indexing operations on the database
while the log shipping file is being created can cause this problem, and
that's exactly what happened. My next question is how do I get past a
corrupted transaction log file without screwing up the whole chain of
transaction logs? The rest are stacking up behind it every 15 minutes
because this one log file won't restore.
> Thanks,
> Kensql
Friday, March 23, 2012
Log Shipping Problem
I've installed log shipping, it was working very fine but since last 2 days
it is givving this type error as given below. Anyone plz help me it's urgent
and in this scenario what should I do
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft]&#
91;ODBC SQL Server Driver][SQL Server]The log in this backup set begins
at LSN 589000000013600001, which is too late to apply to the database. An ea
rlier log backup that includes LSN 58900000
0011700001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is te
rminating abnormally.
Thanks in advance and Regards,
SunilSunil
How do you implement the log shipping?
Do you perform BACKUP LOG withinit OPTION and then RESTORE it.
It seems you have lost a consistency (brocken a chain) of the database.
drop database test
go
create database test
GO
Alter database set recovery full
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH INIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH recovery
GO
After running tthis script you supposed to get the same error.
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:61F86E7E-5F98-4582-8740-AF5DE5F31000@.microsoft.com...
> I've installed log shipping, it was working very fine but since last 2
days it is givving this type error as given below. Anyone plz help me it's
urgent and in this scenario what should I do
>
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODB
C
SQL Server Driver][SQL Server]The log in this backup set begins at LSN
589000000013600001, which is too late to apply to the database. An earlier
log backup that includes LSN 589000000011700001 can be restored.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminat
ing
abnormally.
>
> Thanks in advance and Regards,
>
> Sunil
it is givving this type error as given below. Anyone plz help me it's urgent
and in this scenario what should I do
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft]&#
91;ODBC SQL Server Driver][SQL Server]The log in this backup set begins
at LSN 589000000013600001, which is too late to apply to the database. An ea
rlier log backup that includes LSN 58900000
0011700001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is te
rminating abnormally.
Thanks in advance and Regards,
SunilSunil
How do you implement the log shipping?
Do you perform BACKUP LOG withinit OPTION and then RESTORE it.
It seems you have lost a consistency (brocken a chain) of the database.
drop database test
go
create database test
GO
Alter database set recovery full
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH INIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH recovery
GO
After running tthis script you supposed to get the same error.
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:61F86E7E-5F98-4582-8740-AF5DE5F31000@.microsoft.com...
> I've installed log shipping, it was working very fine but since last 2
days it is givving this type error as given below. Anyone plz help me it's
urgent and in this scenario what should I do
>
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODB
C
SQL Server Driver][SQL Server]The log in this backup set begins at LSN
589000000013600001, which is too late to apply to the database. An earlier
log backup that includes LSN 589000000011700001 can be restored.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminat
ing
abnormally.
>
> Thanks in advance and Regards,
>
> Sunil
Log Shipping Problem
I have a warm standby 2003 Server and am trying to set up log shipping. I am
working with SQL Server 2005 and have created the log shipping jobs using the
wizard. Initially as suggested in the documentation, I backed up the primary
database using norecover options. I resored the database on the secondary to
overwrite the existing database with recovery and standby options which has
left the secondary database in Standby/Read-Only mode. All the the log
shipping job history on the primary and secondary servers show success (even
the restore on the secondary). When I insert records into tables on the
primary I do not see them appear in the secondary database after the restore
job executes. I don't have much experience with log shipping or partial
restores from log files for that matter, I have always done full restores
with no recovery. Why don't I see the new records in the secondary database
after a successful restore (according to job history). I can see the .trn
files in the shipped log file directory, how does this process work, does it
take a restore with norecovery before you can see them in the secondary
database? There is nothing in the documentation that indicates this, I'm
missing something here ... I guess it has something to do with partial
restores. What am I doing wrong.Fran Morabito wrote:
> I have a warm standby 2003 Server and am trying to set up log shipping. I am
> working with SQL Server 2005 and have created the log shipping jobs using the
> wizard. Initially as suggested in the documentation, I backed up the primary
> database using norecover options. I resored the database on the secondary to
> overwrite the existing database with recovery and standby options which has
> left the secondary database in Standby/Read-Only mode. All the the log
> shipping job history on the primary and secondary servers show success (even
> the restore on the secondary). When I insert records into tables on the
> primary I do not see them appear in the secondary database after the restore
> job executes. I don't have much experience with log shipping or partial
> restores from log files for that matter, I have always done full restores
> with no recovery. Why don't I see the new records in the secondary database
> after a successful restore (according to job history). I can see the .trn
> files in the shipped log file directory, how does this process work, does it
> take a restore with norecovery before you can see them in the secondary
> database? There is nothing in the documentation that indicates this, I'm
> missing something here ... I guess it has something to do with partial
> restores. What am I doing wrong.
>
I don't know anything about the log shipping wizard, I've always rolled
my own log shipping routines. It's a pretty simple process:
1. Start with a full backup of the live database
2. Restore full backup to standby using WITH NORECOVERY option
3. Schedule frequent transaction log backups of the live database,
preferrably writing each backup to a seperate file
4. Monitor for new transaction log backups, when found, restore to
standby using WITH NORECOVERY option.
I would start by making sure your transaction log backups are occurring
on the live server, and that they are being restored on the standby server.
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql
working with SQL Server 2005 and have created the log shipping jobs using the
wizard. Initially as suggested in the documentation, I backed up the primary
database using norecover options. I resored the database on the secondary to
overwrite the existing database with recovery and standby options which has
left the secondary database in Standby/Read-Only mode. All the the log
shipping job history on the primary and secondary servers show success (even
the restore on the secondary). When I insert records into tables on the
primary I do not see them appear in the secondary database after the restore
job executes. I don't have much experience with log shipping or partial
restores from log files for that matter, I have always done full restores
with no recovery. Why don't I see the new records in the secondary database
after a successful restore (according to job history). I can see the .trn
files in the shipped log file directory, how does this process work, does it
take a restore with norecovery before you can see them in the secondary
database? There is nothing in the documentation that indicates this, I'm
missing something here ... I guess it has something to do with partial
restores. What am I doing wrong.Fran Morabito wrote:
> I have a warm standby 2003 Server and am trying to set up log shipping. I am
> working with SQL Server 2005 and have created the log shipping jobs using the
> wizard. Initially as suggested in the documentation, I backed up the primary
> database using norecover options. I resored the database on the secondary to
> overwrite the existing database with recovery and standby options which has
> left the secondary database in Standby/Read-Only mode. All the the log
> shipping job history on the primary and secondary servers show success (even
> the restore on the secondary). When I insert records into tables on the
> primary I do not see them appear in the secondary database after the restore
> job executes. I don't have much experience with log shipping or partial
> restores from log files for that matter, I have always done full restores
> with no recovery. Why don't I see the new records in the secondary database
> after a successful restore (according to job history). I can see the .trn
> files in the shipped log file directory, how does this process work, does it
> take a restore with norecovery before you can see them in the secondary
> database? There is nothing in the documentation that indicates this, I'm
> missing something here ... I guess it has something to do with partial
> restores. What am I doing wrong.
>
I don't know anything about the log shipping wizard, I've always rolled
my own log shipping routines. It's a pretty simple process:
1. Start with a full backup of the live database
2. Restore full backup to standby using WITH NORECOVERY option
3. Schedule frequent transaction log backups of the live database,
preferrably writing each backup to a seperate file
4. Monitor for new transaction log backups, when found, restore to
standby using WITH NORECOVERY option.
I would start by making sure your transaction log backups are occurring
on the live server, and that they are being restored on the standby server.
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql
Wednesday, March 21, 2012
log shipping not working
hello, on the destination server he says the restore job has failed
and my log shipping databases are then out of sync. where do i begin
to find out why my log shipping is no longer working? my two servers
are in differnet locations but i can ping and returns are under 50ms.
help?
thanks
APAP
Is it one log it has failed to apply or several? Sometimes
one log may fail to apply for a number of reasons. Next
time the target server tries to apply the logs it should
try to apply all logs since it last applied one. Quite
often it can catch up.
One common reason for log shipping failure is having the
restore on the target run too soon after the backup on the
source. Occasionally the restore tries to run before the
backup finishes. This may be because a big work load on
the source has caused a larger than normal log. It may be
the servers have got out of sync time wise.
Hope this helps
John|||thanks you john, it looks to me that the log shipping has never worked
since the backup was restored in the destincation server. these is a
small database like 63 mb so backup times is quick. each site has a
t1 or faster.
how do i get them back to sync?
AP
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message news:<0ec801c3aa02$82d32cc0$a401280a@.phx.gbl>...
> AP
> Is it one log it has failed to apply or several? Sometimes
> one log may fail to apply for a number of reasons. Next
> time the target server tries to apply the logs it should
> try to apply all logs since it last applied one. Quite
> often it can catch up.
> One common reason for log shipping failure is having the
> restore on the target run too soon after the backup on the
> source. Occasionally the restore tries to run before the
> backup finishes. This may be because a big work load on
> the source has caused a larger than normal log. It may be
> the servers have got out of sync time wise.
> Hope this helps
> John|||Antonio
You will need to copy a recent full backup to your standby
server, to re-establish the log shipping.
I don't have a copy of Enterprise edition that I can look
at at the moment to see exactly how you do it. There
should be an option somewhere to re-establish it.
The fact that it has never worked suggests there is a
problem anyway. Permissions on the share on the target
server is quite a common problem. Use 'xp_cmdshell' on
your source database to check the permissions work. (Do
a 'dir' on it).
Also worth having a look at this link.
http://support.microsoft.com/default.aspx?scid=kb;en-
us;323135
Hope this helps
John|||thank you again. i did check permissions. i see on teh source and
destination server where the transaction logs are that all are on both
servers so i see this to mean that the logs are baccking up on the
source server good and they are being transmisssion to the destination
server good. it looks to me that the logs are shipping ok but not
being replayed on the destination server.
this probably changes your recommentations.
what do you think about it now?
thanks
AP
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message news:<0adc01c3aac4$4e192ad0$a501280a@.phx.gbl>...
> Antonio
> You will need to copy a recent full backup to your standby
> server, to re-establish the log shipping.
> I don't have a copy of Enterprise edition that I can look
> at at the moment to see exactly how you do it. There
> should be an option somewhere to re-establish it.
> The fact that it has never worked suggests there is a
> problem anyway. Permissions on the share on the target
> server is quite a common problem. Use 'xp_cmdshell' on
> your source database to check the permissions work. (Do
> a 'dir' on it).
> Also worth having a look at this link.
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;323135
> Hope this helps
> John|||Antonio
Last thing I can think of suggesting is to try running the
SQL Server agent as a local domain account with the same
name and password on both servers.
I have seen this work.
Regards
John
and my log shipping databases are then out of sync. where do i begin
to find out why my log shipping is no longer working? my two servers
are in differnet locations but i can ping and returns are under 50ms.
help?
thanks
APAP
Is it one log it has failed to apply or several? Sometimes
one log may fail to apply for a number of reasons. Next
time the target server tries to apply the logs it should
try to apply all logs since it last applied one. Quite
often it can catch up.
One common reason for log shipping failure is having the
restore on the target run too soon after the backup on the
source. Occasionally the restore tries to run before the
backup finishes. This may be because a big work load on
the source has caused a larger than normal log. It may be
the servers have got out of sync time wise.
Hope this helps
John|||thanks you john, it looks to me that the log shipping has never worked
since the backup was restored in the destincation server. these is a
small database like 63 mb so backup times is quick. each site has a
t1 or faster.
how do i get them back to sync?
AP
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message news:<0ec801c3aa02$82d32cc0$a401280a@.phx.gbl>...
> AP
> Is it one log it has failed to apply or several? Sometimes
> one log may fail to apply for a number of reasons. Next
> time the target server tries to apply the logs it should
> try to apply all logs since it last applied one. Quite
> often it can catch up.
> One common reason for log shipping failure is having the
> restore on the target run too soon after the backup on the
> source. Occasionally the restore tries to run before the
> backup finishes. This may be because a big work load on
> the source has caused a larger than normal log. It may be
> the servers have got out of sync time wise.
> Hope this helps
> John|||Antonio
You will need to copy a recent full backup to your standby
server, to re-establish the log shipping.
I don't have a copy of Enterprise edition that I can look
at at the moment to see exactly how you do it. There
should be an option somewhere to re-establish it.
The fact that it has never worked suggests there is a
problem anyway. Permissions on the share on the target
server is quite a common problem. Use 'xp_cmdshell' on
your source database to check the permissions work. (Do
a 'dir' on it).
Also worth having a look at this link.
http://support.microsoft.com/default.aspx?scid=kb;en-
us;323135
Hope this helps
John|||thank you again. i did check permissions. i see on teh source and
destination server where the transaction logs are that all are on both
servers so i see this to mean that the logs are baccking up on the
source server good and they are being transmisssion to the destination
server good. it looks to me that the logs are shipping ok but not
being replayed on the destination server.
this probably changes your recommentations.
what do you think about it now?
thanks
AP
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message news:<0adc01c3aac4$4e192ad0$a501280a@.phx.gbl>...
> Antonio
> You will need to copy a recent full backup to your standby
> server, to re-establish the log shipping.
> I don't have a copy of Enterprise edition that I can look
> at at the moment to see exactly how you do it. There
> should be an option somewhere to re-establish it.
> The fact that it has never worked suggests there is a
> problem anyway. Permissions on the share on the target
> server is quite a common problem. Use 'xp_cmdshell' on
> your source database to check the permissions work. (Do
> a 'dir' on it).
> Also worth having a look at this link.
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;323135
> Hope this helps
> John|||Antonio
Last thing I can think of suggesting is to try running the
SQL Server agent as a local domain account with the same
name and password on both servers.
I have seen this work.
Regards
John
log shipping monitor report error
I have SQL Server 2005 log shipping setup with primary/secondary configuration. I can confirm from the logs that log shipping is working without issue, however, reports generated from the monitor server show this message:
Violation of PRIMARY KEY constraint 'PK__#log_shipping_mo__3ABBDC91'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'. The statement has been terminated.
There is nothing special about the configuration. Any ideas?
Clear out the log on Log shipping monitor and ensure the log shipping is working properly on secondary server too.|||Log shipping is working properly on the secondary server. The error is generated when I try to view the Log Shipping Status report on the secondary server. The monitor server instance is also throwing incorrect alerts.|||I think the problem you are seeing is related to some old information being present in the tables used to store log shipping configuration. There are some scenarios where this can happen and it causes the problem you reported in your first post. We are working on correcting this in a future release.
As you can tell from the error, the problem is caused by an insert to a temp table causing a PK constraint violation. The PK for the temp table is server name and database name. This error is normally caused by old configuration being present in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary. You can view the contents of these tables directly (in msdb) or use some supplied help SP's (see BOL topic titled "Log Shipping Tables and Stored Procedures").
The workaround is to remove the old rows from log_shipping_monitor_primary and/or log_shipping_monitor_secondary tables. Can you determine that you do indeed have stale data in the log shipping tables. If this is the case I can work with you on how to remove the old rows.
The old configuration data is probably causing the incorrect alerts you are seeing.
|||Hi, Is that possible for you to show how to delete the old data in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary.
Thank you
|||Hi Mark,
I am getting a similar error when I try to run the report on the primary server. The report on the secondary server which is also the monitor runs fine.
I queried the tables mentioned by you and got one row in each. FYI, I have only one DB on the primary server being log shipped to the secondary.
For,
select * from dbo.log_shipping_monitor_secondary
I am geeting null values for last_copied_file, last_copied_date, last_copied_date_utc and last restored_file. However, I know that the log shipping is working well for the database.
So How do I go about correcting the report? Should I delete the row in log_shipping_monitor_secondary? In log_shipping_monitor_primary the information is up to date and correct.
Thanks in anticipation.
Amit
sqllog shipping monitor report error
I have SQL Server 2005 log shipping setup with primary/secondary configuration. I can confirm from the logs that log shipping is working without issue, however, reports generated from the monitor server show this message:
Violation of PRIMARY KEY constraint 'PK__#log_shipping_mo__3ABBDC91'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'. The statement has been terminated.
There is nothing special about the configuration. Any ideas?
Clear out the log on Log shipping monitor and ensure the log shipping is working properly on secondary server too.|||Log shipping is working properly on the secondary server. The error is generated when I try to view the Log Shipping Status report on the secondary server. The monitor server instance is also throwing incorrect alerts.|||I think the problem you are seeing is related to some old information being present in the tables used to store log shipping configuration. There are some scenarios where this can happen and it causes the problem you reported in your first post. We are working on correcting this in a future release.
As you can tell from the error, the problem is caused by an insert to a temp table causing a PK constraint violation. The PK for the temp table is server name and database name. This error is normally caused by old configuration being present in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary. You can view the contents of these tables directly (in msdb) or use some supplied help SP's (see BOL topic titled "Log Shipping Tables and Stored Procedures").
The workaround is to remove the old rows from log_shipping_monitor_primary and/or log_shipping_monitor_secondary tables. Can you determine that you do indeed have stale data in the log shipping tables. If this is the case I can work with you on how to remove the old rows.
The old configuration data is probably causing the incorrect alerts you are seeing.
|||Hi, Is that possible for you to show how to delete the old data in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary.
Thank you
|||Hi Mark,
I am getting a similar error when I try to run the report on the primary server. The report on the secondary server which is also the monitor runs fine.
I queried the tables mentioned by you and got one row in each. FYI, I have only one DB on the primary server being log shipped to the secondary.
For,
select * from dbo.log_shipping_monitor_secondary
I am geeting null values for last_copied_file, last_copied_date, last_copied_date_utc and last restored_file. However, I know that the log shipping is working well for the database.
So How do I go about correcting the report? Should I delete the row in log_shipping_monitor_secondary? In log_shipping_monitor_primary the information is up to date and correct.
Thanks in anticipation.
Amit
log shipping monitor report error
I have SQL Server 2005 log shipping setup with primary/secondary configuration. I can confirm from the logs that log shipping is working without issue, however, reports generated from the monitor server show this message:
Violation of PRIMARY KEY constraint 'PK__#log_shipping_mo__3ABBDC91'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'. The statement has been terminated.
There is nothing special about the configuration. Any ideas?
Clear out the log on Log shipping monitor and ensure the log shipping is working properly on secondary server too.|||Log shipping is working properly on the secondary server. The error is generated when I try to view the Log Shipping Status report on the secondary server. The monitor server instance is also throwing incorrect alerts.|||I think the problem you are seeing is related to some old information being present in the tables used to store log shipping configuration. There are some scenarios where this can happen and it causes the problem you reported in your first post. We are working on correcting this in a future release.
As you can tell from the error, the problem is caused by an insert to a temp table causing a PK constraint violation. The PK for the temp table is server name and database name. This error is normally caused by old configuration being present in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary. You can view the contents of these tables directly (in msdb) or use some supplied help SP's (see BOL topic titled "Log Shipping Tables and Stored Procedures").
The workaround is to remove the old rows from log_shipping_monitor_primary and/or log_shipping_monitor_secondary tables. Can you determine that you do indeed have stale data in the log shipping tables. If this is the case I can work with you on how to remove the old rows.
The old configuration data is probably causing the incorrect alerts you are seeing.
|||Hi, Is that possible for you to show how to delete the old data in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary.
Thank you
|||Hi Mark,
I am getting a similar error when I try to run the report on the primary server. The report on the secondary server which is also the monitor runs fine.
I queried the tables mentioned by you and got one row in each. FYI, I have only one DB on the primary server being log shipped to the secondary.
For,
select * from dbo.log_shipping_monitor_secondary
I am geeting null values for last_copied_file, last_copied_date, last_copied_date_utc and last restored_file. However, I know that the log shipping is working well for the database.
So How do I go about correcting the report? Should I delete the row in log_shipping_monitor_secondary? In log_shipping_monitor_primary the information is up to date and correct.
Thanks in anticipation.
Amit
log shipping monitor report error
I have SQL Server 2005 log shipping setup with primary/secondary configuration. I can confirm from the logs that log shipping is working without issue, however, reports generated from the monitor server show this message:
Violation of PRIMARY KEY constraint 'PK__#log_shipping_mo__3ABBDC91'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'. The statement has been terminated.
There is nothing special about the configuration. Any ideas?
Clear out the log on Log shipping monitor and ensure the log shipping is working properly on secondary server too.|||Log shipping is working properly on the secondary server. The error is generated when I try to view the Log Shipping Status report on the secondary server. The monitor server instance is also throwing incorrect alerts.|||I think the problem you are seeing is related to some old information being present in the tables used to store log shipping configuration. There are some scenarios where this can happen and it causes the problem you reported in your first post. We are working on correcting this in a future release.
As you can tell from the error, the problem is caused by an insert to a temp table causing a PK constraint violation. The PK for the temp table is server name and database name. This error is normally caused by old configuration being present in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary. You can view the contents of these tables directly (in msdb) or use some supplied help SP's (see BOL topic titled "Log Shipping Tables and Stored Procedures").
The workaround is to remove the old rows from log_shipping_monitor_primary and/or log_shipping_monitor_secondary tables. Can you determine that you do indeed have stale data in the log shipping tables. If this is the case I can work with you on how to remove the old rows.
The old configuration data is probably causing the incorrect alerts you are seeing.
|||Hi, Is that possible for you to show how to delete the old data in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary.
Thank you
|||Hi Mark,
I am getting a similar error when I try to run the report on the primary server. The report on the secondary server which is also the monitor runs fine.
I queried the tables mentioned by you and got one row in each. FYI, I have only one DB on the primary server being log shipped to the secondary.
For,
select * from dbo.log_shipping_monitor_secondary
I am geeting null values for last_copied_file, last_copied_date, last_copied_date_utc and last restored_file. However, I know that the log shipping is working well for the database.
So How do I go about correcting the report? Should I delete the row in log_shipping_monitor_secondary? In log_shipping_monitor_primary the information is up to date and correct.
Thanks in anticipation.
Amit
Monday, March 19, 2012
Log shipping monitor
Hello,
I am just setting up log shipping on our new web servers. I have S1
primary, S2 secondary and S3 monitoring. The log shipping is working OK.
Logs are being backed up on S1, being copied to S2 and then loaded
successfully.
In my Log Shipping Monitor, on the Log Shipping Pair Properties Status tab,
the last backup file is still showing first_file_00000000000.trn. The last
file copied and Last file loaded on the are correct. When I am viewing the
monitor thru' EM on S3, and on the Source tab try to view the Backup
Schedule, the button is not disabled but does not display anything. If I
view the monitor thru' EM on S1 and view the Backup Schedule it displays the
schedule.
The only thing I have noticed is that the graphic at the top of the monitor
is showing (LOCAL).dbname --> S2.dbname. I would have expected it to show
S1.dbname --> S2.dbname. Is it trying to look at the backup on (LOCAL) so
looking on S3 which is just the monitoring server.
Anyone else seen this? Thanks for any help you can give me.
ClaireB
Sorry - meant to add I am looking at the graphic on S3.
"ClaireB" wrote:
> Hello,
> I am just setting up log shipping on our new web servers. I have S1
> primary, S2 secondary and S3 monitoring. The log shipping is working OK.
> Logs are being backed up on S1, being copied to S2 and then loaded
> successfully.
> In my Log Shipping Monitor, on the Log Shipping Pair Properties Status tab,
> the last backup file is still showing first_file_00000000000.trn. The last
> file copied and Last file loaded on the are correct. When I am viewing the
> monitor thru' EM on S3, and on the Source tab try to view the Backup
> Schedule, the button is not disabled but does not display anything. If I
> view the monitor thru' EM on S1 and view the Backup Schedule it displays the
> schedule.
> The only thing I have noticed is that the graphic at the top of the monitor
> is showing (LOCAL).dbname --> S2.dbname. I would have expected it to show
> S1.dbname --> S2.dbname. Is it trying to look at the backup on (LOCAL) so
> looking on S3 which is just the monitoring server.
> Anyone else seen this? Thanks for any help you can give me.
> ClaireB
I am just setting up log shipping on our new web servers. I have S1
primary, S2 secondary and S3 monitoring. The log shipping is working OK.
Logs are being backed up on S1, being copied to S2 and then loaded
successfully.
In my Log Shipping Monitor, on the Log Shipping Pair Properties Status tab,
the last backup file is still showing first_file_00000000000.trn. The last
file copied and Last file loaded on the are correct. When I am viewing the
monitor thru' EM on S3, and on the Source tab try to view the Backup
Schedule, the button is not disabled but does not display anything. If I
view the monitor thru' EM on S1 and view the Backup Schedule it displays the
schedule.
The only thing I have noticed is that the graphic at the top of the monitor
is showing (LOCAL).dbname --> S2.dbname. I would have expected it to show
S1.dbname --> S2.dbname. Is it trying to look at the backup on (LOCAL) so
looking on S3 which is just the monitoring server.
Anyone else seen this? Thanks for any help you can give me.
ClaireB
Sorry - meant to add I am looking at the graphic on S3.
"ClaireB" wrote:
> Hello,
> I am just setting up log shipping on our new web servers. I have S1
> primary, S2 secondary and S3 monitoring. The log shipping is working OK.
> Logs are being backed up on S1, being copied to S2 and then loaded
> successfully.
> In my Log Shipping Monitor, on the Log Shipping Pair Properties Status tab,
> the last backup file is still showing first_file_00000000000.trn. The last
> file copied and Last file loaded on the are correct. When I am viewing the
> monitor thru' EM on S3, and on the Source tab try to view the Backup
> Schedule, the button is not disabled but does not display anything. If I
> view the monitor thru' EM on S1 and view the Backup Schedule it displays the
> schedule.
> The only thing I have noticed is that the graphic at the top of the monitor
> is showing (LOCAL).dbname --> S2.dbname. I would have expected it to show
> S1.dbname --> S2.dbname. Is it trying to look at the backup on (LOCAL) so
> looking on S3 which is just the monitoring server.
> Anyone else seen this? Thanks for any help you can give me.
> ClaireB
Log shipping monitor
Hello,
I am just setting up log shipping on our new web servers. I have S1
primary, S2 secondary and S3 monitoring. The log shipping is working OK.
Logs are being backed up on S1, being copied to S2 and then loaded
successfully.
In my Log Shipping Monitor, on the Log Shipping Pair Properties Status tab,
the last backup file is still showing first_file_00000000000.trn. The last
file copied and Last file loaded on the are correct. When I am viewing the
monitor thru' EM on S3, and on the Source tab try to view the Backup
Schedule, the button is not disabled but does not display anything. If I
view the monitor thru' EM on S1 and view the Backup Schedule it displays the
schedule.
The only thing I have noticed is that the graphic at the top of the monitor
is showing (LOCAL).dbname --> S2.dbname. I would have expected it to show
S1.dbname --> S2.dbname. Is it trying to look at the backup on (LOCAL) so
looking on S3 which is just the monitoring server.
Anyone else seen this? Thanks for any help you can give me.
ClaireBSorry - meant to add I am looking at the graphic on S3.
"ClaireB" wrote:
> Hello,
> I am just setting up log shipping on our new web servers. I have S1
> primary, S2 secondary and S3 monitoring. The log shipping is working OK.
> Logs are being backed up on S1, being copied to S2 and then loaded
> successfully.
> In my Log Shipping Monitor, on the Log Shipping Pair Properties Status tab,
> the last backup file is still showing first_file_00000000000.trn. The last
> file copied and Last file loaded on the are correct. When I am viewing the
> monitor thru' EM on S3, and on the Source tab try to view the Backup
> Schedule, the button is not disabled but does not display anything. If I
> view the monitor thru' EM on S1 and view the Backup Schedule it displays the
> schedule.
> The only thing I have noticed is that the graphic at the top of the monitor
> is showing (LOCAL).dbname --> S2.dbname. I would have expected it to show
> S1.dbname --> S2.dbname. Is it trying to look at the backup on (LOCAL) so
> looking on S3 which is just the monitoring server.
> Anyone else seen this? Thanks for any help you can give me.
> ClaireB
I am just setting up log shipping on our new web servers. I have S1
primary, S2 secondary and S3 monitoring. The log shipping is working OK.
Logs are being backed up on S1, being copied to S2 and then loaded
successfully.
In my Log Shipping Monitor, on the Log Shipping Pair Properties Status tab,
the last backup file is still showing first_file_00000000000.trn. The last
file copied and Last file loaded on the are correct. When I am viewing the
monitor thru' EM on S3, and on the Source tab try to view the Backup
Schedule, the button is not disabled but does not display anything. If I
view the monitor thru' EM on S1 and view the Backup Schedule it displays the
schedule.
The only thing I have noticed is that the graphic at the top of the monitor
is showing (LOCAL).dbname --> S2.dbname. I would have expected it to show
S1.dbname --> S2.dbname. Is it trying to look at the backup on (LOCAL) so
looking on S3 which is just the monitoring server.
Anyone else seen this? Thanks for any help you can give me.
ClaireBSorry - meant to add I am looking at the graphic on S3.
"ClaireB" wrote:
> Hello,
> I am just setting up log shipping on our new web servers. I have S1
> primary, S2 secondary and S3 monitoring. The log shipping is working OK.
> Logs are being backed up on S1, being copied to S2 and then loaded
> successfully.
> In my Log Shipping Monitor, on the Log Shipping Pair Properties Status tab,
> the last backup file is still showing first_file_00000000000.trn. The last
> file copied and Last file loaded on the are correct. When I am viewing the
> monitor thru' EM on S3, and on the Source tab try to view the Backup
> Schedule, the button is not disabled but does not display anything. If I
> view the monitor thru' EM on S1 and view the Backup Schedule it displays the
> schedule.
> The only thing I have noticed is that the graphic at the top of the monitor
> is showing (LOCAL).dbname --> S2.dbname. I would have expected it to show
> S1.dbname --> S2.dbname. Is it trying to look at the backup on (LOCAL) so
> looking on S3 which is just the monitoring server.
> Anyone else seen this? Thanks for any help you can give me.
> ClaireB
log shipping login sync
Hi,
I have log shipping setup working fine. I created new login (jskow) on primary server and he is a valid user in the database which is configure for log shipping, My secondary server is read-only. I want to give jskow read access on secondary, I added jskow to secondary server, log shipping copied that database user to secondary database , but when I try to login to secondary server and use the read-only database , I get error not a valid user?,
any help?
thanks in advance.
Ram.Run SP_CHANGE_USERS_LOGIN with REPORT clause and see whether the login reported as orphaned user, if so use AUTO_FIX to fix the login.
Refer to BOOKS ONLINE for more information.
I have log shipping setup working fine. I created new login (jskow) on primary server and he is a valid user in the database which is configure for log shipping, My secondary server is read-only. I want to give jskow read access on secondary, I added jskow to secondary server, log shipping copied that database user to secondary database , but when I try to login to secondary server and use the read-only database , I get error not a valid user?,
any help?
thanks in advance.
Ram.Run SP_CHANGE_USERS_LOGIN with REPORT clause and see whether the login reported as orphaned user, if so use AUTO_FIX to fix the login.
Refer to BOOKS ONLINE for more information.
Log Shipping Issue
I have log shipping setup b/t 2 servers and everything is working fine except for one thing. The T-Log Restore to the Standby server fails everytime. Everyone has full permissions to it and I am using the Network Admin account to transfer and restore. Has anyone had any similar issues and might have any solutions? Thank you.What error do you receive?
For log shipping to work properly you have to first restore the database =WITH STANDBY
You then restore your logs
The (destination) database cannot be in use when the log is applied.
Refer to http://sqlguy.home.comcast.net/logship.htm for more =information.
-- Keith
"Don" <donolwert@.hotmail.com> wrote in message =news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft.com...
> I have log shipping setup b/t 2 servers and everything is working fine =except for one thing. The T-Log Restore to the Standby server fails =everytime. Everyone has full permissions to it and I am using the =Network Admin account to transfer and restore. Has anyone had any =similar issues and might have any solutions? Thank you.|||Thanks for responding. There error message I am getting is as follows
Executed as user: FS-SA\fssaadmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed
I verified and made sure I restored the DB in Standby mode as well. Permissions should be fine as the user fssaadmin is our Domain Admins account
I also switched the Database to Single-User mode and then ran the restore job but am still getting the error. Thanks for any help
Do
-- Keith Kratochvil wrote: --
What error do you receive
For log shipping to work properly you have to first restore the database WITH STANDB
You then restore your log
The (destination) database cannot be in use when the log is applied
Refer to http://sqlguy.home.comcast.net/logship.htm for more information
--
Keit
"Don" <donolwert@.hotmail.com> wrote in message news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft.com..
> I have log shipping setup b/t 2 servers and everything is working fine except for one thing. The T-Log Restore to the Standby server fails everytime. Everyone has full permissions to it and I am using the Network Admin account to transfer and restore. Has anyone had any similar issues and might have any solutions? Thank you|||Can you run the appropriate T-SQL (restore database and restore log) =commands within Query Analyzer on the target server when connected as =sa? Does it work?
You may find some helpful infomation within Tibor Karaszi's post:
http://groups.google.com/groups?hl=3Den&lr=3D&ie=3DUTF-8&oe=3DUTF-8&threa=
dm=3D08ea01c18440%249e9828f0%2439ef2ecf%40TKMSFTNGXA08&rnum=3D1&prev=3D/g=roups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DError%2B22029%26b=tnG%3DGoogle%2BSearch%26meta%3Dgroup%253Dmicrosoft.public.sqlserver.*
or
http://tinyurl.com/3f4e4
-- Keith
"Don" <donolwert@.hotmail.com> wrote in message =news:6DF3AC39-C30A-4DBA-9438-C2CA32758BB7@.microsoft.com...
> Thanks for responding. There error message I am getting is as =follows:
> > Executed as user: FS-SA\fssaadmin. sqlmaint.exe failed. =[SQLSTATE 42000] (Error 22029). The step failed.
> > I verified and made sure I restored the DB in Standby mode as well. =Permissions should be fine as the user fssaadmin is our Domain Admins =account.
> > I also switched the Database to Single-User mode and then ran the =restore job but am still getting the error. Thanks for any help.
> > Don
> > > -- Keith Kratochvil wrote: --
> > What error do you receive?
> > For log shipping to work properly you have to first restore the =database WITH STANDBY
> > You then restore your logs
> > The (destination) database cannot be in use when the log is =applied.
> Refer to http://sqlguy.home.comcast.net/logship.htm for more =information.
> > -- > Keith
> > > "Don" <donolwert@.hotmail.com> wrote in message =news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft.com...
> > I have log shipping setup b/t 2 servers and everything is =working fine except for one thing. The T-Log Restore to the Standby =server fails everytime. Everyone has full permissions to it and I am =using the Network Admin account to transfer and restore. Has anyone had =any similar issues and might have any solutions? Thank you.
>|||Keith, Thank you for all your help. I discovered what the issue is...McAfee. I uninstalled McAfee and everything ran perfectly. Thank you.
-- Keith Kratochvil wrote: --
Can you run the appropriate T-SQL (restore database and restore log) commands within Query Analyzer on the target server when connected as sa? Does it work?
You may find some helpful infomation within Tibor Karaszi's post:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=08ea01c18440%249e9828f0%2439ef2ecf%40TKMSFTNGXA08&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DError%2B22029%26btnG%3DGoogle%2BSearch%26meta%3Dgroup%253Dmicrosoft.public.sqlserver.*
or
http://tinyurl.com/3f4e4
--
Keith
"Don" <donolwert@.hotmail.com> wrote in message news:6DF3AC39-C30A-4DBA-9438-C2CA32758BB7@.microsoft.com...
> Thanks for responding. There error message I am getting is as follows:
>> Executed as user: FS-SA\fssaadmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
>> I verified and made sure I restored the DB in Standby mode as well. Permissions should be fine as the user fssaadmin is our Domain Admins account.
>> I also switched the Database to Single-User mode and then ran the restore job but am still getting the error. Thanks for any help.
>> Don
>> -- Keith Kratochvil wrote: --
>> What error do you receive?
>> For log shipping to work properly you have to first restore the database WITH STANDBY
>> You then restore your logs
>> The (destination) database cannot be in use when the log is applied.
> Refer to http://sqlguy.home.comcast.net/logship.htm for more information.
>> --
> Keith
>> "Don" <donolwert@.hotmail.com> wrote in message news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft.com...
>> I have log shipping setup b/t 2 servers and everything is working fine except for one thing. The T-Log Restore to the Standby server fails everytime. Everyone has full permissions to it and I am using the Network Admin account to transfer and restore. Has anyone had any similar issues and might have any solutions? Thank you.
>
For log shipping to work properly you have to first restore the database =WITH STANDBY
You then restore your logs
The (destination) database cannot be in use when the log is applied.
Refer to http://sqlguy.home.comcast.net/logship.htm for more =information.
-- Keith
"Don" <donolwert@.hotmail.com> wrote in message =news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft.com...
> I have log shipping setup b/t 2 servers and everything is working fine =except for one thing. The T-Log Restore to the Standby server fails =everytime. Everyone has full permissions to it and I am using the =Network Admin account to transfer and restore. Has anyone had any =similar issues and might have any solutions? Thank you.|||Thanks for responding. There error message I am getting is as follows
Executed as user: FS-SA\fssaadmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed
I verified and made sure I restored the DB in Standby mode as well. Permissions should be fine as the user fssaadmin is our Domain Admins account
I also switched the Database to Single-User mode and then ran the restore job but am still getting the error. Thanks for any help
Do
-- Keith Kratochvil wrote: --
What error do you receive
For log shipping to work properly you have to first restore the database WITH STANDB
You then restore your log
The (destination) database cannot be in use when the log is applied
Refer to http://sqlguy.home.comcast.net/logship.htm for more information
--
Keit
"Don" <donolwert@.hotmail.com> wrote in message news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft.com..
> I have log shipping setup b/t 2 servers and everything is working fine except for one thing. The T-Log Restore to the Standby server fails everytime. Everyone has full permissions to it and I am using the Network Admin account to transfer and restore. Has anyone had any similar issues and might have any solutions? Thank you|||Can you run the appropriate T-SQL (restore database and restore log) =commands within Query Analyzer on the target server when connected as =sa? Does it work?
You may find some helpful infomation within Tibor Karaszi's post:
http://groups.google.com/groups?hl=3Den&lr=3D&ie=3DUTF-8&oe=3DUTF-8&threa=
dm=3D08ea01c18440%249e9828f0%2439ef2ecf%40TKMSFTNGXA08&rnum=3D1&prev=3D/g=roups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DError%2B22029%26b=tnG%3DGoogle%2BSearch%26meta%3Dgroup%253Dmicrosoft.public.sqlserver.*
or
http://tinyurl.com/3f4e4
-- Keith
"Don" <donolwert@.hotmail.com> wrote in message =news:6DF3AC39-C30A-4DBA-9438-C2CA32758BB7@.microsoft.com...
> Thanks for responding. There error message I am getting is as =follows:
> > Executed as user: FS-SA\fssaadmin. sqlmaint.exe failed. =[SQLSTATE 42000] (Error 22029). The step failed.
> > I verified and made sure I restored the DB in Standby mode as well. =Permissions should be fine as the user fssaadmin is our Domain Admins =account.
> > I also switched the Database to Single-User mode and then ran the =restore job but am still getting the error. Thanks for any help.
> > Don
> > > -- Keith Kratochvil wrote: --
> > What error do you receive?
> > For log shipping to work properly you have to first restore the =database WITH STANDBY
> > You then restore your logs
> > The (destination) database cannot be in use when the log is =applied.
> Refer to http://sqlguy.home.comcast.net/logship.htm for more =information.
> > -- > Keith
> > > "Don" <donolwert@.hotmail.com> wrote in message =news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft.com...
> > I have log shipping setup b/t 2 servers and everything is =working fine except for one thing. The T-Log Restore to the Standby =server fails everytime. Everyone has full permissions to it and I am =using the Network Admin account to transfer and restore. Has anyone had =any similar issues and might have any solutions? Thank you.
>|||Keith, Thank you for all your help. I discovered what the issue is...McAfee. I uninstalled McAfee and everything ran perfectly. Thank you.
-- Keith Kratochvil wrote: --
Can you run the appropriate T-SQL (restore database and restore log) commands within Query Analyzer on the target server when connected as sa? Does it work?
You may find some helpful infomation within Tibor Karaszi's post:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=08ea01c18440%249e9828f0%2439ef2ecf%40TKMSFTNGXA08&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DError%2B22029%26btnG%3DGoogle%2BSearch%26meta%3Dgroup%253Dmicrosoft.public.sqlserver.*
or
http://tinyurl.com/3f4e4
--
Keith
"Don" <donolwert@.hotmail.com> wrote in message news:6DF3AC39-C30A-4DBA-9438-C2CA32758BB7@.microsoft.com...
> Thanks for responding. There error message I am getting is as follows:
>> Executed as user: FS-SA\fssaadmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
>> I verified and made sure I restored the DB in Standby mode as well. Permissions should be fine as the user fssaadmin is our Domain Admins account.
>> I also switched the Database to Single-User mode and then ran the restore job but am still getting the error. Thanks for any help.
>> Don
>> -- Keith Kratochvil wrote: --
>> What error do you receive?
>> For log shipping to work properly you have to first restore the database WITH STANDBY
>> You then restore your logs
>> The (destination) database cannot be in use when the log is applied.
> Refer to http://sqlguy.home.comcast.net/logship.htm for more information.
>> --
> Keith
>> "Don" <donolwert@.hotmail.com> wrote in message news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft.com...
>> I have log shipping setup b/t 2 servers and everything is working fine except for one thing. The T-Log Restore to the Standby server fails everytime. Everyone has full permissions to it and I am using the Network Admin account to transfer and restore. Has anyone had any similar issues and might have any solutions? Thank you.
>
Log Shipping Issue
I have log shipping setup b/t 2 servers and everything is working fine excep
t for one thing. The T-Log Restore to the Standby server fails everytime.
Everyone has full permissions to it and I am using the Network Admin account
to transfer and restore.
Has anyone had any similar issues and might have any solutions? Thank you.What error do you receive?
For log shipping to work properly you have to first restore the database =
WITH STANDBY
You then restore your logs
The (destination) database cannot be in use when the log is applied.
Refer to http://sqlguy.home.comcast.net/logship.htm for more =
information.
--=20
Keith
"Don" <donolwert@.hotmail.com> wrote in message =
news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft.com...
except for one thing. The T-Log Restore to the Standby server fails =
everytime. Everyone has full permissions to it and I am using the =
Network Admin account to transfer and restore. Has anyone had any =
similar issues and might have any solutions? Thank you.|||Thanks for responding. There error message I am getting is as follows:
Executed as user: FS-SA\fssaadmin. sqlmaint.exe failed. [SQLSTATE 42000] (Er
ror 22029). The step failed.
I verified and made sure I restored the DB in Standby mode as well. Permiss
ions should be fine as the user fssaadmin is our Domain Admins account.
I also switched the Database to Single-User mode and then ran the restore jo
b but am still getting the error. Thanks for any help.
Don
-- Keith Kratochvil wrote: --
What error do you receive?
For log shipping to work properly you have to first restore the database WIT
H STANDBY
You then restore your logs
The (destination) database cannot be in use when the log is applied.
Refer to http://sqlguy.home.comcast.net/logship.htm for more information.
Keith
"Don" <donolwert@.hotmail.com> wrote in message news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft
.com...
tore. Has anyone had any similar issues and might have any solutions? Than
k you.|||Can you run the appropriate T-SQL (restore database and restore log) =
commands within Query Analyzer on the target server when connected as =
sa? Does it work?
You may find some helpful infomation within Tibor Karaszi's post:
http://groups.google.com/groups?hl=...=3DUTF-8&threa=
dm=3D08ea01c18440%249e9828f0%2439ef2ecf%
40TKMSFTNGXA08&rnum=3D1&prev=3D/g=
roups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DError%2B22029%26b=
tnG%3DGoogle%2BSearch%26meta%3Dgroup%253
Dmicrosoft.public.sqlserver.*
or
http://tinyurl.com/3f4e4
--=20
Keith
"Don" <donolwert@.hotmail.com> wrote in message =
news:6DF3AC39-C30A-4DBA-9438-C2CA32758BB7@.microsoft.com...
follows:
[SQLSTATE 42000] (Error 22029). The step failed.
Permissions should be fine as the user fssaadmin is our Domain Admins =
account.
restore job but am still getting the error. Thanks for any help.
database WITH STANDBY
applied.
information.
news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft.com...
. I uninstalled McAfee and everything ran perfectly. Thank you.
-- Keith Kratochvil wrote: --
Can you run the appropriate T-SQL (restore database and restore log) command
s within Query Analyzer on the target server when connected as sa? Does it
work?
You may find some helpful infomation within Tibor Karaszi's post:
http://groups.google.com/groups?hl=...53D
micr
osoft.public.sqlserver.*
or
http://tinyurl.com/3f4e4
Keith
"Don" <donolwert@.hotmail.com> wrote in message news:6DF3AC39-C30A-4DBA-9438-C2CA32758BB7@.microsoft
.com...
t for one thing. The T-Log Restore to the Standby server fails everytime.
Everyone has full permissions to it and I am using the Network Admin account
to transfer and restore.
Has anyone had any similar issues and might have any solutions? Thank you.What error do you receive?
For log shipping to work properly you have to first restore the database =
WITH STANDBY
You then restore your logs
The (destination) database cannot be in use when the log is applied.
Refer to http://sqlguy.home.comcast.net/logship.htm for more =
information.
--=20
Keith
"Don" <donolwert@.hotmail.com> wrote in message =
news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft.com...
quote:
> I have log shipping setup b/t 2 servers and everything is working fine =
except for one thing. The T-Log Restore to the Standby server fails =
everytime. Everyone has full permissions to it and I am using the =
Network Admin account to transfer and restore. Has anyone had any =
similar issues and might have any solutions? Thank you.|||Thanks for responding. There error message I am getting is as follows:
Executed as user: FS-SA\fssaadmin. sqlmaint.exe failed. [SQLSTATE 42000] (Er
ror 22029). The step failed.
I verified and made sure I restored the DB in Standby mode as well. Permiss
ions should be fine as the user fssaadmin is our Domain Admins account.
I also switched the Database to Single-User mode and then ran the restore jo
b but am still getting the error. Thanks for any help.
Don
-- Keith Kratochvil wrote: --
What error do you receive?
For log shipping to work properly you have to first restore the database WIT
H STANDBY
You then restore your logs
The (destination) database cannot be in use when the log is applied.
Refer to http://sqlguy.home.comcast.net/logship.htm for more information.
Keith
"Don" <donolwert@.hotmail.com> wrote in message news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft
.com...
quote:
> I have log shipping setup b/t 2 servers and everything is working fine except for one thin
g. The T-Log Restore to the Standby server fails everytime. Everyone has full permissions
to it and I am using the Network Admin account to transfer and res
tore. Has anyone had any similar issues and might have any solutions? Than
k you.|||Can you run the appropriate T-SQL (restore database and restore log) =
commands within Query Analyzer on the target server when connected as =
sa? Does it work?
You may find some helpful infomation within Tibor Karaszi's post:
http://groups.google.com/groups?hl=...=3DUTF-8&threa=
dm=3D08ea01c18440%249e9828f0%2439ef2ecf%
40TKMSFTNGXA08&rnum=3D1&prev=3D/g=
roups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DError%2B22029%26b=
tnG%3DGoogle%2BSearch%26meta%3Dgroup%253
Dmicrosoft.public.sqlserver.*
or
http://tinyurl.com/3f4e4
--=20
Keith
"Don" <donolwert@.hotmail.com> wrote in message =
news:6DF3AC39-C30A-4DBA-9438-C2CA32758BB7@.microsoft.com...
quote:
> Thanks for responding. There error message I am getting is as =
follows:
quote:
> =20
> Executed as user: FS-SA\fssaadmin. sqlmaint.exe failed. =
[SQLSTATE 42000] (Error 22029). The step failed.
quote:
>=20
> I verified and made sure I restored the DB in Standby mode as well. =
Permissions should be fine as the user fssaadmin is our Domain Admins =
account.
quote:
>=20
> I also switched the Database to Single-User mode and then ran the =
restore job but am still getting the error. Thanks for any help.
quote:
>=20
> Don
>=20
> =20
> -- Keith Kratochvil wrote: --
> =20
> What error do you receive?
> =20
> For log shipping to work properly you have to first restore the =
database WITH STANDBY
quote:
> =20
> You then restore your logs
> =20
> The (destination) database cannot be in use when the log is =
applied.
quote:
> Refer to http://sqlguy.home.comcast.net/logship.htm for more =
information.
quote:
> =20
> --=20
> Keith
> =20
> =20
> "Don" <donolwert@.hotmail.com> wrote in message =
news:5A8317ED-4B10-41B5-981E-A35F6008C319@.microsoft.com...
quote:|||Keith, Thank you for all your help. I discovered what the issue is...McAfee
working fine except for one thing. The T-Log Restore to the Standby =
server fails everytime. Everyone has full permissions to it and I am =
using the Network Admin account to transfer and restore. Has anyone had =
any similar issues and might have any solutions? Thank you.[QUOTE]
>
. I uninstalled McAfee and everything ran perfectly. Thank you.
-- Keith Kratochvil wrote: --
Can you run the appropriate T-SQL (restore database and restore log) command
s within Query Analyzer on the target server when connected as sa? Does it
work?
You may find some helpful infomation within Tibor Karaszi's post:
http://groups.google.com/groups?hl=...53D
micr
osoft.public.sqlserver.*
or
http://tinyurl.com/3f4e4
Keith
"Don" <donolwert@.hotmail.com> wrote in message news:6DF3AC39-C30A-4DBA-9438-C2CA32758BB7@.microsoft
.com...
quote:
> Thanks for responding. There error message I am getting is as follows:
> Refer to http://sqlguy.home.comcast.net/logship.htm for more informat
ion.
> Keith
store. Has anyone had any similar issues and might have any solutions? Thank you.[QUOTE][color=d
arkred]
>
Monday, March 12, 2012
Log Shipping file deletes
I have log shipping working on my OLTP server. One and
only one of the log shipped databases is not deleting the
transaction log files on the destination server.
The deletes are happening on the source server, and they
are happening for all other databases on the destination
server.
I have looked at the various log shipping tables in msdb,
and I can't find any differences to explain this.
Has anyone got any ideas ?
TIA.Take a look also a file permissions and ownership... Is there a difference
there?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"im Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:3bc001c37b2e$494b75a0$a601280a@.phx.gbl...
> I have log shipping working on my OLTP server. One and
> only one of the log shipped databases is not deleting the
> transaction log files on the destination server.
> The deletes are happening on the source server, and they
> are happening for all other databases on the destination
> server.
> I have looked at the various log shipping tables in msdb,
> and I can't find any differences to explain this.
> Has anyone got any ideas ?
> TIA.
only one of the log shipped databases is not deleting the
transaction log files on the destination server.
The deletes are happening on the source server, and they
are happening for all other databases on the destination
server.
I have looked at the various log shipping tables in msdb,
and I can't find any differences to explain this.
Has anyone got any ideas ?
TIA.Take a look also a file permissions and ownership... Is there a difference
there?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"im Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:3bc001c37b2e$494b75a0$a601280a@.phx.gbl...
> I have log shipping working on my OLTP server. One and
> only one of the log shipped databases is not deleting the
> transaction log files on the destination server.
> The deletes are happening on the source server, and they
> are happening for all other databases on the destination
> server.
> I have looked at the various log shipping tables in msdb,
> and I can't find any differences to explain this.
> Has anyone got any ideas ?
> TIA.
Wednesday, March 7, 2012
log shipping between sql 2005 clusters
hi
I am working for a small govt dept.We are planning to implement sql 2005.
We will be having sql 2005 instance in active/passive cluster mode
We will also have a standby sql server 2005 in active/passive cluster mode.
i need to know how to configure log shipping between my primary and secondary?
Is logshipping the best option?
Thanks
Is logshipping the best option for what? Logshipping is a fine technology but there are other ways to get data to a secondary server. You can use database mirroring in SQL Server 2005. For failover purposes this is a great option. You can also use database snapshots on the same server for various reasons to. Logshipping is perfectly viable too. What are you trying to accomplish? Having redundancy outside of the cluster? Read Only reporting copy of data?
Having that info can help us help you make your choice. I would say look up in Books Online Logshipping, Database Snapshots and Database Mirroring.
|||Thank you very much for the reply.
I need to have an exact replica of the live enviornment (production cluster) to be switched to (standby cluster) in case of any probs to the primary. We also do switching between primary and standby every week. Basically the standby is like having a disaster recovery site. Its having Redundancy outside the cluster.
Please suggest....
Thanks in advance
|||Options are
Database Mirroring
Can be operated in Synchronous mode which means your standby will be an exact replica of your primary database. Switching between the two is easy.
However it doesn't support replication and in synchronous mode the data has to be written to the standby and so may impact performance of your app if your network connection isn't great.
You can work in Asynchronous mode in which case the data is written to the standby at some point after the application has finished the transaction, i.e. no impact on the application. However in this mode you may loose data if your primary goes down and the transaction hasn't made it to the standby.
Database Mirroing has automatic failover in the event of a failure (when used with a witness server)
Log Shipping
Very much like asynchronous mirroring however it supports multiple targets i.e. you could have 2 standby servers, you can't do that with mirroring. Also the mechanism for applying the logs is via a copy and restore model which is slightly different to mirroring.
Replication
Much more complicated than the two above but allows for your production to be in read/write mode (although changes are not replicated back to the primary). This is a great option for maintaining a reporting server as you can have different indexes on your reporting database.
Personally if your two clusters are connected by a highspeed network (Gb/s) I would go for synchronous mirroring.
More details can be found here
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/udb9/html/b2eda634-0f8e-4703-801b-7ba895544ff5.htm in BOL under the heading "Configuring for high availability."
Subscribe to:
Posts (Atom)