Remove inactive entries from transaction log and log shipping

When I try to take a Transaction log backup of a database (Recovery model
set to Full), I get an option to truncate the transaction log by checking
"Remove inactive entries from transaction log" check box in the backup
dialog. This basically causes the inactive portion of the transaction log
(where transaction is completes, either rolled back are committed) to be
truncated. So this potentially leads to saving of huge amount of hard disk
space.
Now, when I setup log shipping is there any similar option I can set on the
primary database? After every transaction log backup I want inactive entries
from the transaction log to be removed. Is there any way to do this? If yes,
how?. If no, why is that so? Once the backup is taken inactive log entries
on the primary database are of no use to log ship, right?
Rathna RajThe option in the backup dialog is very misleading. It basically means that
EM does *not* send the WITH NO_TRUNCATE option. This option is designed for
doing an "emergency" backup in case the database is corrupted, and I have
communicated to MS that the dialog shouldn't be misleading in this way. The
normal way to do log backup is by not specifying this option (having the
checkbox checked (I believe), the default). If the log shipping was written
by a sane individual ;-), then it should not specify this option, I.e., you
already have the desired behavior. To be certain, use a Profiler trace to
see what is going on.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rathna Raj" <RathnaRajT@.icode.com> wrote in message
news:%2365JEWJyDHA.1272@.TK2MSFTNGP12.phx.gbl...
> When I try to take a Transaction log backup of a database (Recovery model
> set to Full), I get an option to truncate the transaction log by checking
> "Remove inactive entries from transaction log" check box in the backup
> dialog. This basically causes the inactive portion of the transaction log
> (where transaction is completes, either rolled back are committed) to be
> truncated. So this potentially leads to saving of huge amount of hard disk
> space.
> Now, when I setup log shipping is there any similar option I can set on
the
> primary database? After every transaction log backup I want inactive
entries
> from the transaction log to be removed. Is there any way to do this? If
yes,
> how?. If no, why is that so? Once the backup is taken inactive log entries
> on the primary database are of no use to log ship, right?
> Rathna Raj
>|||Hi Rathna,
Thanks for your post. Based on my research, log shipping does done backup
log part, so transaction log should not be large. Therefore, it seems that
truncating the transaction log of the primary server manually in log
shipping is not necessary.
For more information regarding log shipping, please refer to the following
article on SQL Server Books Online. Also, the example in this article will
help describe how the log shipping works.
Topic; "Log Shipping"
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.sql

Remote Server Not Visible

Hi All,
I am trying to set up log shipping between SQL servers on two different
networks (diff forests) with a two way trust relationship.
Problem is that I can't see the remote SQL server in the destination
server drop down list (I can see local SQL servers).
The SQL agents on both machines are running under different accounts.
I have tried to add the account running the remote SQL agent to my local
SQL servers administrators group but this has no effect.
I can ping the server by name and it resolves OK.
I have registered the server with Enterprise manager, and even tried to
add it as a linked server to no effect.
Any ideas to resolve this would be much appreciated.
Ranj.
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
Ranj,
I have no way of emulating your situation here, but one thing I'd try is
using the client network utility to create an alias to the other server
based on IP address, register the alias in EM and then see if that appears
in the Log-shipping wizard.
HTH,
Paul Ibison
|||Hi Paul,
Thanks for the response.
I tried adding the server through the client network utility with tcp/IP
and then named pipes, but neither worked.
Is it possible to script transaction log shipping through stored
procedures ?
Ranj.
On Wed, 14 Jul 2004 17:09:24 +0100, Paul Ibison
<Paul.Ibison@.Pygmalion.Com> wrote:

> Ranj,
> I have no way of emulating your situation here, but one thing I'd try is
> using the client network utility to create an alias to the other server
> based on IP address, register the alias in EM and then see if that
> appears
> in the Log-shipping wizard.
> HTH,
> Paul Ibison
>
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
|||Ranejet,
there is a list of SPs for log shipping (and other sections) in BOL :
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_sp_00_519s.htm
It is possible to script it, but not possible to script it out from an
existing one, so this could take some time.
Please can you post back to let me know what OSs you are using?
Regards,
Paul Ibison
|||Hi Paul,
thanks for the info - I am using Windows 2000 Server edition on local and
remote - both have SQL Enterprise installed.
Both networks have their own forests and are connected with a two way
trust.
There is a checkpoint firewall between them used to esatblish a VPN.
Ranj.
|||Ranj,
you can setup logshipping using the resource kit utilities. I use the ones
that was for sql7 on sql2000 with no problems at all, it is eay to setup as
well.
let me know if you need the scripts and documentation from resource kit from
sql7.
regards
m
"ranejet" <not-tellint> wrote in message
news:opsa55bmjgcakp78@.idt.toare.co.uk...
> Hi Paul,
> Thanks for the response.
> I tried adding the server through the client network utility with tcp/IP
> and then named pipes, but neither worked.
> Is it possible to script transaction log shipping through stored
> procedures ?
> Ranj.
> On Wed, 14 Jul 2004 17:09:24 +0100, Paul Ibison
> <Paul.Ibison@.Pygmalion.Com> wrote:
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
|||Ranejet,
pinging the server doesn't mean port 1433 is open in the firewall. I'd check
that.
Also, use dnslookup to verify the ip address after you have successfully
pinged the server - it may be another server in a different domain.
HTH,
Paul Ibison
|||try an odbcping to verify 1433 is open.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eeD$nBoaEHA.3596@.tk2msftngp13.phx.gbl...
> Ranejet,
> pinging the server doesn't mean port 1433 is open in the firewall. I'd
check
> that.
> Also, use dnslookup to verify the ip address after you have successfully
> pinged the server - it may be another server in a different domain.
> HTH,
> Paul Ibison
>
|||If You can ping the Server, that probably means You have full IP connectivity, since ICMP is the first thing to block in a firewall.
What You might lack, however, is NetBIOS resolution, which Is possibly what the Enterprise Manager is looking for. Do You see the remote Server in the "network" in Windows Explorer ?
If not, activating NetBIOS over TCP/IP might work, alternatively, You need a common WINS server between the two servers to establish a browse list.

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
TerryI should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posting.google.com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:
> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
TerryI should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posti
ng.google.com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:

> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
Terry
I should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posting.google. com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry
|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:

> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>