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...
>
>
>
>

log shipping restore fail

We've been experiencing above average "Log Shipping
Restore" fails. All copy and restore can be worked for
first two or three times. For no apparent reason, the job
of log shipping restore fails. I checked the windows event
viewer,the message is "SQL Server Scheduled Job 'Log
Shipping Restore for Test2.NorthwindCopy_logshipping'
(0x63382D0A9860D541B191C36DCB658480) - Status: Failed -
Invoked on: 2003-10-31 12:30:00 - Message: The job
failed. The Job was invoked by Schedule 12 (Schedule 1).
The last step to run was step 1 (Log Shipping Restore)."
I'm sure that disk space and netowkr access isn't an issue
and the secondary server isn't busy.Anyone knows what
might cause this .Thanks.Are users accessing the database at the time of failure?
Can you run the restore command yourself? Does it work?
You can get more information regarding log shipping here:
http://sqlguy.home.comcast.net/logship.htm
-- Keith
"kelly" <kelly_lu@.kingston.com.tw> wrote in message =news:45bd01c39f73$9f9f2b90$7d02280a@.phx.gbl...
> We've been experiencing above average "Log Shipping > Restore" fails. All copy and restore can be worked for > first two or three times. For no apparent reason, the job > of log shipping restore fails. I checked the windows event > viewer,the message is "SQL Server Scheduled Job 'Log > Shipping Restore for Test2.NorthwindCopy_logshipping' > (0x63382D0A9860D541B191C36DCB658480) - Status: Failed - > Invoked on: 2003-10-31 12:30:00 - Message: The job > failed. The Job was invoked by Schedule 12 (Schedule 1). > The last step to run was step 1 (Log Shipping Restore)."
> I'm sure that disk space and netowkr access isn't an issue > and the secondary server isn't busy.Anyone knows what > might cause this .Thanks. >

log shipping failover to stand by server

Hi Guys
in logshipping to fail over to stand by server do i have
to do any role change
as far as i know i have to run following script at the
stand by server. am i right?
RESTORE DATABASE database_name WITH RECOVERY
EXEC SP_DBOPTION 'database_name', 'read
only', 'false'
EXEC SP_DBOPTION 'database_name', 'dbo use
only', 'false'
pls advice me
Thanks
Biju
Biju,
have a look at 'log shipping, role changes' in BOL. You don't need to run
your script manually, but run
sp_change_primary_role
sp_change_secondary_role
sp_change_monitor_role
Usually the logins have already been transferred so there's nothing else to
do. These sprocs are useful because they'll try to get the last log (the
live one) if at all possible.
HTH,
Paul Ibison
|||BUT I DONT want to change roles.
>--Original Message--
>Biju,
>have a look at 'log shipping, role changes' in BOL. You
don't need to run
>your script manually, but run
>sp_change_primary_role
>sp_change_secondary_role
>sp_change_monitor_role
>Usually the logins have already been transferred so
there's nothing else to
>do. These sprocs are useful because they'll try to get
the last log (the
>live one) if at all possible.
>HTH,
>Paul Ibison
>
>.
>
|||Biju,
the name is perhaps slightly unintuitive. sp_change_secondary_role:
(a) Copies the final production database transaction log backup to the
standby server along with any additional transaction log backups that were
not previously copied.
(b) Disables the log-shipping transaction copy job on the standby server.
(c) Restores all transaction log backups not previously restored to the
standby database and initiates the recovery of the standby database.
(d) Terminates and rolls back all pending transactions.
(e) Sets the access level of the standby database after completion of the
system stored procedure to multiuser mode.
(f) Creates a log-shipping database maintenance plan on the new primary
server for the new production database. The SQL Server Agent job defined in
this plan backs up the transaction log of the new production database to the
folder location you specified when you set up log shipping.
HTH,
Paul Ibison

log shipping failover

Hi Guys,
in sql server log shipping for fail overing to stand by
server if i need to restore transaction log recovered from
primary server. how i can do that?is it using script or
Enterprise manager?
what is the script used?
pls advice me.
Thanks
Biju
Biju,
the failover process is manual in hte case of log-shipping (as opposed to
clustering).
The main procedure to run is sp_change_secondary_role. This will get hold of
the last log if it can and recover the database.
After that you could consider peripheral data from the system catalog - eg
logins and messages but these should be already there if you have planned in
advance.
You can change DSN values to point your apps towards the new system or you
can alter the computername. If you do this you'll need to update the value
in sysservers (sp_dropserver 'xxx', sp_addserver 'yyy', 'local').
HTH,
Paul Ibison
|||Are you running the log shipping wizard that comes with SQL Server 2000 EE?
If not here is a guide to implementing log shipping.
http://www.sql-server-performance.co...g_shipping.asp
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"biju george" wrote:

> Hi Guys,
> in sql server log shipping for fail overing to stand by
> server if i need to restore transaction log recovered from
> primary server. how i can do that?is it using script or
> Enterprise manager?
> what is the script used?
> pls advice me.
> Thanks
> Biju
>

Log shipping Fail over

Hi Paul,
To switch back roles from the new primary to old primary, why you need to
rename the server, use sp_dropserver, sp_addserver and restart the services?
Thanks for your help.
Pierre,
the reason is that you can't (easily) have 2 computers with the same NETBIOS
name on the same LAN.
Rgds,
Paul Ibison (SQL Server MVP)
|||Pierre,
Normally in log shipping role changes you do not change the server names.
Instead, the application must change its connection string to reference the
correct server.
Ron
Ron Talmage
SQL Server MVP
"Pierre Pilon" <Pierre Pilon@.discussions.microsoft.com> wrote in message
news:36E83F31-CEC9-4082-ABAF-96AC51DBA8BC@.microsoft.com...
> Hi Paul,
> To switch back roles from the new primary to old primary, why you need to
> rename the server, use sp_dropserver, sp_addserver and restart the
services?
> Thanks for your help.
|||Ron,
with respect, I think this depends on the setup
implemented. EG if there are loads of desktop
applications using DSNs which refer to the servername,
then changing each connection string may not be so
feasible. Also, if linked servers are being used to
reference the production server with 4-part qualified
names this will cause issues. Rescuing replication with
existing subscribers would be another issue, although
the 'standard' seems to be setting it up from scratch.
Master-slave jobs would probably be another difficulty.
Rgds,
Paul Ibison (SQL Server MVP)

Log shipping fail over

I assume this is the correct spot for a log shipping question. Pardon if
it's not.
I've set up a log shipping pair (first time) and everything 'appears' to be
running fine, but I have a question about fail over.
In my research I have found that, in order to bring the secondary up as the
primary, I have to run a few procedures. The first of which is to be run on
the primary server to change its role. How would I do this if the server has
crashed? What steps should I take if this is the scenario?
Thanks in advance.
So what's the best fail over method should the primary server crash? This is
how I see it,
1) Run sp_change_secondary_role on the secondary server.
2) Switch app to point to secondary server or rename and re-IP secondary
server.
3) The users log into the application and the app uses one SQL login, so I
assume I don't need to worry too much about resolving logins.
This will get my users back to functional? What about once the old primary
is back up? Should I then run sp_change_primary_role? Or would it be best
to delete the maintenance jobs and recreate the log shipping pair?
Sorry about all of the questions, I've seen plenty of information on how to
change roles if the primary is still functional, but nothing concerning a
primary server crash.
Again, thanks in advance.
"Paul Ibison" wrote:

> Mick,
> you're correct - sp_change_primary_role removes the
> primary server from the maintenance plan and it will not
> usually be possible to do this in reality. The key
> failover procedure is sp_change_secondary_role which gets
> the last log and restores the system with recovery.
> Rgds,
> Paul Ibison (SQL Server MVP)
>
>
|||Mick,
Run sp_change_secondary_role, rename the server and use sp_dropserver,
sp_addserver, restart services. Create the login you require and use
sp_change_users_login if you haven't taken the SID.
There's no simple way of switching back roles to the primary, apart from
setting up a new log shipping pair and reversing the process.
The old maintenance plan at this stage is now defunct and needs to be
removed if the primary server comes online at some later stage.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]

Log shipping and error 22029

I am having some trouble using MS SQL Server 2000 log shipping. I can get the
database to fail over and become active on the secondary server, but I want
to also have the primary become immediately available for reverse log
shipping.
After I have run the stored procedures to demote the primary, I have to
detach and attach the database on the secondary to be able to run the stored
procedure to promote the secondary. If I don't the procedure fails (sorry, I
don't have the exact error, but it refers to the databse being unable to
access). I also have to manually run the last copy and restore job to make
sure the final transaction log backup gets applied. Once this is done, the
stored procedure to promote the secondary is successful. I then run the
stored procedure to change the monitor server to reflect the new server roles.
Once the database is failed over, I have to go into the maintenance plan for
the reverse log shipping and assign the new destination server. When doing
this, I receive a message stating that the database on the new destination
server is not in standby mode. If I set the database to Read Only in the
Properties dialog box, I can then configure the new destination server. When
I do this, the log backups work fine and so does the copy job. But the
restore job fails with the following error
Executed as user: DMSMAIN\Administrator. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
I can't find any helpful documentation of this message. Listed below are the
stored procedures I am running.
USE master
GO
EXEC msdb.dbo.sp_change_primary_role
@.db_name = 'modelcopy',
@.backup_log = 1,
@.terminate = 1,
@.final_state = 2,
@.access_level = 1
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
USE master
GO
EXEC msdb.dbo.sp_change_monitor_role
@.primary_server = 'sqlserver1' ,
@.secondary_server = 'staging3',
@.database = 'modelcopy',
@.new_source = '\\staging3\logshipping'
Any help is appreciated
TIA,
Ken
Ken,
First of all, do not use the option "Allow database to assume primary role"
when setting up log shipping. It just doesn't work.
Reversing the direction of log shipping is a manual process.
1. Demote the primary database on the Main server using
msdb.dbo.sp_change_primary_role
as you have it below. This will make one last tlog backup to disk.
2. Promote the secondary database on the Standby server using
msdb.dbo.sp_change_secondary_role
as you have it below. This will take that one last tlog backup and apply it
WITH RECOVERY.
3. Remove all log shipping that remains (jobs and rows for that log shipping
pair in the ls tables - if there are any.)
At this point you can install log shipping from scratch, the new direction
now being from the primary database on the Standby server to the secondary
database (in norecovery) on the Main server.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
news:3D4C90B5-93A9-4646-ABB2-17110ACF574F@.microsoft.com...
> I am having some trouble using MS SQL Server 2000 log shipping. I can get
the
> database to fail over and become active on the secondary server, but I
want
> to also have the primary become immediately available for reverse log
> shipping.
> After I have run the stored procedures to demote the primary, I have to
> detach and attach the database on the secondary to be able to run the
stored
> procedure to promote the secondary. If I don't the procedure fails (sorry,
I
> don't have the exact error, but it refers to the databse being unable to
> access). I also have to manually run the last copy and restore job to make
> sure the final transaction log backup gets applied. Once this is done, the
> stored procedure to promote the secondary is successful. I then run the
> stored procedure to change the monitor server to reflect the new server
roles.
> Once the database is failed over, I have to go into the maintenance plan
for
> the reverse log shipping and assign the new destination server. When doing
> this, I receive a message stating that the database on the new destination
> server is not in standby mode. If I set the database to Read Only in the
> Properties dialog box, I can then configure the new destination server.
When
> I do this, the log backups work fine and so does the copy job. But the
> restore job fails with the following error
> Executed as user: DMSMAIN\Administrator. sqlmaint.exe failed. [SQLSTATE
> 42000] (Error 22029). The step failed.
> I can't find any helpful documentation of this message. Listed below are
the
> stored procedures I am running.
> USE master
> GO
> EXEC msdb.dbo.sp_change_primary_role
> @.db_name = 'modelcopy',
> @.backup_log = 1,
> @.terminate = 1,
> @.final_state = 2,
> @.access_level = 1
> 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
> USE master
> GO
> EXEC msdb.dbo.sp_change_monitor_role
> @.primary_server = 'sqlserver1' ,
> @.secondary_server = 'staging3',
> @.database = 'modelcopy',
> @.new_source = '\\staging3\logshipping'
> Any help is appreciated
> TIA,
> Ken
|||Ron,
Thanks again for your help. I had just about came to the same conclusion
about the "assume primary role option" but wanted to make sure. In fact, I
was figuring I owuld have to resign myself to the option of simply rebuilding
from the start all over again.
Thanks,
Ken
"Ron Talmage" wrote:

> Ken,
> First of all, do not use the option "Allow database to assume primary role"
> when setting up log shipping. It just doesn't work.
> Reversing the direction of log shipping is a manual process.
> 1. Demote the primary database on the Main server using
> msdb.dbo.sp_change_primary_role
> as you have it below. This will make one last tlog backup to disk.
> 2. Promote the secondary database on the Standby server using
> msdb.dbo.sp_change_secondary_role
> as you have it below. This will take that one last tlog backup and apply it
> WITH RECOVERY.
> 3. Remove all log shipping that remains (jobs and rows for that log shipping
> pair in the ls tables - if there are any.)
> At this point you can install log shipping from scratch, the new direction
> now being from the primary database on the Standby server to the secondary
> database (in norecovery) on the Main server.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
> news:3D4C90B5-93A9-4646-ABB2-17110ACF574F@.microsoft.com...
> the
> want
> stored
> I
> roles.
> for
> When
> the
>
>
|||Hello Ken
If I understand correctly, there are 2 issues that you have brought up in
this message -
1. You are unable to configure the primary server to reverse log ship at
the time of failover
2. You are having problems with running the sp_change_secondary_role stored
procedure because you get some error that mentions that the database is in
use.
Let me address these -
1. You should use sp_change_primary_role in the following manner if you
would like to leave the primary database in standby(or norecovery) state
for reverse log shipping :
EXEC msdb.dbo.sp_change_primary_role
@.db_name = 'modelcopy',
@.backup_log = 1,
@.terminate = 1,
@.final_state = 3 --3 = Standby, 2 = Norecovery
This performs transaction log backup and leaves the database in a state
where no further changes can be made. PLEASE MAKE SURE THAT THIS STORED
PROCEDURE COMPLETES SUCCESSFULLY OTHERWISE YOU WILL GET ERRORS WHEN YOU
SETUP THIS SERVER AS SECONDARY. If the output indicates any errors, try
running the stored procedure again. If you continue to get errors, please
post the output that you get from this stored procedure in reply to this
message.
2. Please refer to the following KB articles to see if you are running into
one of the known issues :
294397 BUG: sp_change_secondary_role Fails with Error 3101 if There Are
http://support.microsoft.com/?id=294397
308774 FIX: Sp_change_primary_role May Store Transaction Log Backup in the
http://support.microsoft.com/?id=308774
300497 FIX: Log Shipping: Cannot Change Role From Secondary to Primary When
http://support.microsoft.com/?id=300497
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Pankaj,
You're correct in that there are two basic issues causing problems. However,
the sp_change_primary_role always completed successfully. I would run this in
Query Analyzer and would get successful messages.
Also, the original secondary server would, if I followed the steps outlined
in my message, become a primary with relative ease.
The biggest issue I ran into was that when I tried to configure the original
primary, now the secondary, to accept log shipping so that it again may
become the primary during another role change, the restore jobs would fail.
The backup and copy jobs were always successful.
Thanks,
Ken
"Pankaj Agarwal [MSFT]" wrote:

> Hello Ken
> If I understand correctly, there are 2 issues that you have brought up in
> this message -
> 1. You are unable to configure the primary server to reverse log ship at
> the time of failover
> 2. You are having problems with running the sp_change_secondary_role stored
> procedure because you get some error that mentions that the database is in
> use.
> Let me address these -
> 1. You should use sp_change_primary_role in the following manner if you
> would like to leave the primary database in standby(or norecovery) state
> for reverse log shipping :
> EXEC msdb.dbo.sp_change_primary_role
> @.db_name = 'modelcopy',
> @.backup_log = 1,
> @.terminate = 1,
> @.final_state = 3 --3 = Standby, 2 = Norecovery
> This performs transaction log backup and leaves the database in a state
> where no further changes can be made. PLEASE MAKE SURE THAT THIS STORED
> PROCEDURE COMPLETES SUCCESSFULLY OTHERWISE YOU WILL GET ERRORS WHEN YOU
> SETUP THIS SERVER AS SECONDARY. If the output indicates any errors, try
> running the stored procedure again. If you continue to get errors, please
> post the output that you get from this stored procedure in reply to this
> message.
> 2. Please refer to the following KB articles to see if you are running into
> one of the known issues :
> 294397 BUG: sp_change_secondary_role Fails with Error 3101 if There Are
> http://support.microsoft.com/?id=294397
> 308774 FIX: Sp_change_primary_role May Store Transaction Log Backup in the
> http://support.microsoft.com/?id=308774
> 300497 FIX: Log Shipping: Cannot Change Role From Secondary to Primary When
> http://support.microsoft.com/?id=300497
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>