Log Shipping Monitor Shows Problems when there aren't any

I Have setup a pair of server with log shipping, it works perfectly, logs are
backed up every 2 mins and restored succesfully to the destination server. My
only problem is that the monitor (running on the backup server) thinks that
things are not working correctly. It shows the correct times for the copy and
load jobs but the backup job time never gets updated. I have read the MS KB
article 292586 which states that this could be a permissions issue that the
source server does not have permissions to update the msdb database on the
destination server, however I have tried changing things in this area by
having the sql account running with full admin privs (both domain admin and
sql admin) this didnt work, I also tried running sql in mixed auth mode and
then configurung log shipping to use SQL authentication, it correctly created
the new log_shipping_monitor account, but still the log shipping monitor
refuses to work. Can somebody help me before I pull all my hair out.
Brett
Hello Brett,
My suggestion is that you reset password for log_shipping_monitor_probe
account on all shipping databases (both secondary and primary) and removed
and
reapplied permissions to the log shipping tables on both primary and
secondary servers. If log_shipping_monitor_prob does not exist, create the
login, set the default
database to msdb, and grant it access to msdb.
On both Primary / Secondary server, right click the log shipping database,
select properties. In the general tab, click the details button for log
shipping. Select "Use SQL Server Authentication" and change the password
to log_shipping_monitor_prob account.
More related information:
321247 How To Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Log Shipping Monitor Shows Problems when there aren't any
>thread-index: AcX4xD/DRKnfVG7nSZmLj/7UPPi8Vg==
>X-WBNR-Posting-Host: 194.109.239.116
>From: "=?Utf-8?B?QnJldHQgQ2Fycg==?=" <brettcarr@.news.postalias>
>Subject: Log Shipping Monitor Shows Problems when there aren't any
>Date: Sun, 4 Dec 2005 03:17:01 -0800
>Lines: 15
>Message-ID: <0E563B60-3095-4635-BC0A-3153BEB87CDB@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67206
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>I Have setup a pair of server with log shipping, it works perfectly, logs
are
>backed up every 2 mins and restored succesfully to the destination server.
My
>only problem is that the monitor (running on the backup server) thinks
that
>things are not working correctly. It shows the correct times for the copy
and
>load jobs but the backup job time never gets updated. I have read the MS
KB
>article 292586 which states that this could be a permissions issue that
the
>source server does not have permissions to update the msdb database on the
>destination server, however I have tried changing things in this area by
>having the sql account running with full admin privs (both domain admin
and
>sql admin) this didnt work, I also tried running sql in mixed auth mode
and
>then configurung log shipping to use SQL authentication, it correctly
created
>the new log_shipping_monitor account, but still the log shipping monitor
>refuses to work. Can somebody help me before I pull all my hair out.
>Brett
>
|||"Peter Yang [MSFT]" wrote:

> Hello Brett,
> My suggestion is that you reset password for log_shipping_monitor_probe
> account on all shipping databases (both secondary and primary) and removed
> and
> reapplied permissions to the log shipping tables on both primary and
> secondary servers. If log_shipping_monitor_prob does not exist, create the
> login, set the default
> database to msdb, and grant it access to msdb.
> On both Primary / Secondary server, right click the log shipping database,
> select properties. In the general tab, click the details button for log
> shipping. Select "Use SQL Server Authentication" and change the password
> to log_shipping_monitor_prob account.
>
Ok. I've done the following:
1. log_shipping_monitor_prob did not exist on the primary server so I
created it.
2. I reset the password for log_shipping_monitor_prob on both primary and
secondary.
3. On both primary and secondary servers I have set
log_shipping_monitor_prob to have select,insert,update and delete permissions
on all tables starting with log_shipping in the msdb database.
However I'm afraid the 'Last Backup' date and time listed in the monitor is
still the date and time I set it up originally. The Last copy and Last
restore dates are still being updated correctly.
Brett..
|||Hello Brett,
Going forward, I'd like to confirm if Windows Authentication option is
selected on the Specify The Log Shipping Monitor Server Information screen
during log shipping setup.
If so, you may want to check if the SQL Server Service startup account on
the primary server of a log shipping pair does not have sufficient
privileges to update table msdb..log_shipping_primaries on the monitor
server.
Also, please check if the value in primary_server_name column in
log_shipping_primaries does not match the <@.@.servername> of the primary
server.
If the issue persists, please use SQL profiler on the monitor server to see
if the update operation on log_shipping_primaries occurs or if there is any
related errors in error log.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Log Shipping Monitor Shows Problems when there aren't any
>thread-index: AcX5iaV+Rf+MfqKZTvqVdLuPrgGVlw==
>X-WBNR-Posting-Host: 193.0.2.180
>From: "=?Utf-8?B?QnJldHQgQ2Fycg==?=" <brettcarr@.news.postalias>
>References: <0E563B60-3095-4635-BC0A-3153BEB87CDB@.microsoft.com>
<VAQE#gX#FHA.1236@.TK2MSFTNGXA02.phx.gbl>[vbcol=seagreen]
>Subject: RE: Log Shipping Monitor Shows Problems when there aren't any
>Date: Mon, 5 Dec 2005 02:50:03 -0800
>Lines: 35
>Message-ID: <33C3B088-9CA3-4C3E-98B1-86E784833DDC@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67231
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>"Peter Yang [MSFT]" wrote:
removed[vbcol=seagreen]
the[vbcol=seagreen]
database,[vbcol=seagreen]
password
>Ok. I've done the following:
>1. log_shipping_monitor_prob did not exist on the primary server so I
>created it.
>2. I reset the password for log_shipping_monitor_prob on both primary and
>secondary.
>3. On both primary and secondary servers I have set
>log_shipping_monitor_prob to have select,insert,update and delete
permissions
>on all tables starting with log_shipping in the msdb database.
>However I'm afraid the 'Last Backup' date and time listed in the monitor
is
>still the date and time I set it up originally. The Last copy and Last
>restore dates are still being updated correctly.
>Brett..
>
>

Log shipping from SQL 2000 to SQL 2005

I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQL
2K5) with norecovery.
Now i want to use the maintenance plan wizard in 2000 to set up log shipping
but since i cannot register the destination i.e the SQL 2K5 box in 2000
Enterprise Manager, I cannot seem to complete it.
How do you all set it up ? Please help.Hi Hassan
Have you looked at manually setting up log shipping as in the topic "How to
set up a Log Shipping Monitor (Transact-SQL)" in Books online?
John
"Hassan" wrote:
> I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQL
> 2K5) with norecovery.
> Now i want to use the maintenance plan wizard in 2000 to set up log shipping
> but since i cannot register the destination i.e the SQL 2K5 box in 2000
> Enterprise Manager, I cannot seem to complete it.
> How do you all set it up ? Please help.
>
>|||I know how to setup log shipping, but what i dont know is how to setup log
shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans wont let
me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D95FCA9-03A5-4082-83BD-1DD974E09D69@.microsoft.com...
> Hi Hassan
> Have you looked at manually setting up log shipping as in the topic "How
> to
> set up a Log Shipping Monitor (Transact-SQL)" in Books online?
> John
> "Hassan" wrote:
>> I have a DB on Server A ( SQL 2K) and just restored the DB on Server B(
>> SQL
>> 2K5) with norecovery.
>> Now i want to use the maintenance plan wizard in 2000 to set up log
>> shipping
>> but since i cannot register the destination i.e the SQL 2K5 box in 2000
>> Enterprise Manager, I cannot seem to complete it.
>> How do you all set it up ? Please help.
>>|||Hassan wrote:
> I know how to setup log shipping, but what i dont know is how to setup log
> shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans wont let
> me.
>
So don't use the maintenance plans!! Log shipping is nothing more than
restoring transaction log backups from one database to another, with a
control wrapper to keep track of which logs have not been restored.
Very easy to roll your own process, and you can make it work the way YOU
want it to, not the way MS wants it to.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I assume that the SSMS / EM GUIs don't allow this, but it's really easy to
script a couple of SQL Agent jobs to perform the LogShipping - on the prod
side, a Maintenance Plan job to backup the log to a shared directory every n
minutes, then a second on the Standby server which polls that directory &
restores the log backups.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uTHS4lM%23GHA.3480@.TK2MSFTNGP04.phx.gbl...
>I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQL
>2K5) with norecovery.
> Now i want to use the maintenance plan wizard in 2000 to set up log
> shipping but since i cannot register the destination i.e the SQL 2K5 box
> in 2000 Enterprise Manager, I cannot seem to complete it.
> How do you all set it up ? Please help.
>|||Tracy,
So right now we have log shipping set up between a source SQL 2K server and
a standby SQL 2K server using the maintenance plan. What I want to
accomplish is to add the SQL 2K5 standby server to it which means I need to
somehow copy the log files and restore the log files on the SQL 2K5 server
through a script.
I dont know how to write that script. Can you help ?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4540E47C.7060908@.realsqlguy.com...
> Hassan wrote:
>> I know how to setup log shipping, but what i dont know is how to setup
>> log shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans
>> wont let me.
> So don't use the maintenance plans!! Log shipping is nothing more than
> restoring transaction log backups from one database to another, with a
> control wrapper to keep track of which logs have not been restored. Very
> easy to roll your own process, and you can make it work the way YOU want
> it to, not the way MS wants it to.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Scripting is not my forte Greg. Do you know how you can help me out here ?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:ONGl$WV%23GHA.4464@.TK2MSFTNGP02.phx.gbl...
>I assume that the SSMS / EM GUIs don't allow this, but it's really easy to
>script a couple of SQL Agent jobs to perform the LogShipping - on the prod
>side, a Maintenance Plan job to backup the log to a shared directory every
>n minutes, then a second on the Standby server which polls that directory &
>restores the log backups.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uTHS4lM%23GHA.3480@.TK2MSFTNGP04.phx.gbl...
>>I have a DB on Server A ( SQL 2K) and just restored the DB on Server B(
>>SQL 2K5) with norecovery.
>> Now i want to use the maintenance plan wizard in 2000 to set up log
>> shipping but since i cannot register the destination i.e the SQL 2K5 box
>> in 2000 Enterprise Manager, I cannot seem to complete it.
>> How do you all set it up ? Please help.
>|||Hi Hassan,
Those are the Simple Backup and Restore commands you have to use in the
script and schedule in Jobs.
You can take BOL Reference and start writing it....
Thanks
Ajay Rengunthwar
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OkQiuLW%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Tracy,
> So right now we have log shipping set up between a source SQL 2K server
> and a standby SQL 2K server using the maintenance plan. What I want to
> accomplish is to add the SQL 2K5 standby server to it which means I need
> to somehow copy the log files and restore the log files on the SQL 2K5
> server through a script.
> I dont know how to write that script. Can you help ?
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:4540E47C.7060908@.realsqlguy.com...
>> Hassan wrote:
>> I know how to setup log shipping, but what i dont know is how to setup
>> log shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans
>> wont let me.
>>
>> So don't use the maintenance plans!! Log shipping is nothing more than
>> restoring transaction log backups from one database to another, with a
>> control wrapper to keep track of which logs have not been restored. Very
>> easy to roll your own process, and you can make it work the way YOU want
>> it to, not the way MS wants it to.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>

Log shipping from SQL 2000 to SQL 2005

I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQL
2K5) with norecovery.
Now i want to use the maintenance plan wizard in 2000 to set up log shipping
but since i cannot register the destination i.e the SQL 2K5 box in 2000
Enterprise Manager, I cannot seem to complete it.
How do you all set it up ? Please help.Hi Hassan
Have you looked at manually setting up log shipping as in the topic "How to
set up a Log Shipping Monitor (Transact-SQL)" in Books online?
John
"Hassan" wrote:

> I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQ
L
> 2K5) with norecovery.
> Now i want to use the maintenance plan wizard in 2000 to set up log shippi
ng
> but since i cannot register the destination i.e the SQL 2K5 box in 2000
> Enterprise Manager, I cannot seem to complete it.
> How do you all set it up ? Please help.
>
>|||I know how to setup log shipping, but what i dont know is how to setup log
shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans wont let
me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D95FCA9-03A5-4082-83BD-1DD974E09D69@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> Have you looked at manually setting up log shipping as in the topic "How
> to
> set up a Log Shipping Monitor (Transact-SQL)" in Books online?
> John
> "Hassan" wrote:
>|||Hassan wrote:
> I know how to setup log shipping, but what i dont know is how to setup log
> shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans wont let
> me.
>
So don't use the maintenance plans!! Log shipping is nothing more than
restoring transaction log backups from one database to another, with a
control wrapper to keep track of which logs have not been restored.
Very easy to roll your own process, and you can make it work the way YOU
want it to, not the way MS wants it to.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I assume that the SSMS / EM GUIs don't allow this, but it's really easy to
script a couple of SQL Agent jobs to perform the LogShipping - on the prod
side, a Maintenance Plan job to backup the log to a shared directory every n
minutes, then a second on the Standby server which polls that directory &
restores the log backups.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uTHS4lM%23GHA.3480@.TK2MSFTNGP04.phx.gbl...
>I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQL
>2K5) with norecovery.
> Now i want to use the maintenance plan wizard in 2000 to set up log
> shipping but since i cannot register the destination i.e the SQL 2K5 box
> in 2000 Enterprise Manager, I cannot seem to complete it.
> How do you all set it up ? Please help.
>|||Tracy,
So right now we have log shipping set up between a source SQL 2K server and
a standby SQL 2K server using the maintenance plan. What I want to
accomplish is to add the SQL 2K5 standby server to it which means I need to
somehow copy the log files and restore the log files on the SQL 2K5 server
through a script.
I dont know how to write that script. Can you help ?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4540E47C.7060908@.realsqlguy.com...
> Hassan wrote:
> So don't use the maintenance plans!! Log shipping is nothing more than
> restoring transaction log backups from one database to another, with a
> control wrapper to keep track of which logs have not been restored. Very
> easy to roll your own process, and you can make it work the way YOU want
> it to, not the way MS wants it to.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Scripting is not my forte Greg. Do you know how you can help me out here ?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:ONGl$WV%23GHA.4464@.TK2MSFTNGP02.phx.gbl...
>I assume that the SSMS / EM GUIs don't allow this, but it's really easy to
>script a couple of SQL Agent jobs to perform the LogShipping - on the prod
>side, a Maintenance Plan job to backup the log to a shared directory every
>n minutes, then a second on the Standby server which polls that directory &
>restores the log backups.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uTHS4lM%23GHA.3480@.TK2MSFTNGP04.phx.gbl...
>|||Hi Hassan,
Those are the Simple Backup and Restore commands you have to use in the
script and schedule in Jobs.
You can take BOL Reference and start writing it....
Thanks
Ajay Rengunthwar
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OkQiuLW%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Tracy,
> So right now we have log shipping set up between a source SQL 2K server
> and a standby SQL 2K server using the maintenance plan. What I want to
> accomplish is to add the SQL 2K5 standby server to it which means I need
> to somehow copy the log files and restore the log files on the SQL 2K5
> server through a script.
> I dont know how to write that script. Can you help ?
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:4540E47C.7060908@.realsqlguy.com...
>

Log shipping and Manual backups

I have set up a log shipping on my master and backup database, where a full
backup of the master database is done each day at midnight and restored to
the backup database. The master databases transaction logs are then backed
up and restored to the backup database every 10 minutes.
My users want to also have the ability to do manual full database backups as
well. However I am unsure whether or not this will have the affect that the
log shipping operations.
Will this cause the master and backup databases to get out of synch until
the full backup and restore is performed at midnight?
Thanks in advance
No worries. Database backup does not break the log backup chain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Parks" <treatyourselfnow@.hotmail.com> wrote in message
news:%23OJ4KyGHGHA.3120@.TK2MSFTNGP10.phx.gbl...
>I have set up a log shipping on my master and backup database, where a full backup of the master
>database is done each day at midnight and restored to the backup database. The master databases
>transaction logs are then backed up and restored to the backup database every 10 minutes.
> My users want to also have the ability to do manual full database backups as well. However I am
> unsure whether or not this will have the affect that the log shipping operations.
> Will this cause the master and backup databases to get out of synch until the full backup and
> restore is performed at midnight?
> Thanks in advance
>

Log shipping and Manual backups

I have set up a log shipping on my master and backup database, where a full
backup of the master database is done each day at midnight and restored to
the backup database. The master databases transaction logs are then backed
up and restored to the backup database every 10 minutes.
My users want to also have the ability to do manual full database backups as
well. However I am unsure whether or not this will have the affect that the
log shipping operations.
Will this cause the master and backup databases to get out of synch until
the full backup and restore is performed at midnight?
Thanks in advanceNo worries. Database backup does not break the log backup chain.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Parks" <treatyourselfnow@.hotmail.com> wrote in message
news:%23OJ4KyGHGHA.3120@.TK2MSFTNGP10.phx.gbl...
>I have set up a log shipping on my master and backup database, where a full backup of the master
>database is done each day at midnight and restored to the backup database. The master databases
>transaction logs are then backed up and restored to the backup database every 10 minutes.
> My users want to also have the ability to do manual full database backups as well. However I am
> unsure whether or not this will have the affect that the log shipping operations.
> Will this cause the master and backup databases to get out of synch until the full backup and
> restore is performed at midnight?
> Thanks in advance
>

Log shipping and Manual backups

I have set up a log shipping on my master and backup database, where a full
backup of the master database is done each day at midnight and restored to
the backup database. The master databases transaction logs are then backed
up and restored to the backup database every 10 minutes.
My users want to also have the ability to do manual full database backups as
well. However I am unsure whether or not this will have the affect that the
log shipping operations.
Will this cause the master and backup databases to get out of synch until
the full backup and restore is performed at midnight?
Thanks in advanceNo worries. Database backup does not break the log backup chain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Parks" <treatyourselfnow@.hotmail.com> wrote in message
news:%23OJ4KyGHGHA.3120@.TK2MSFTNGP10.phx.gbl...
>I have set up a log shipping on my master and backup database, where a full
backup of the master
>database is done each day at midnight and restored to the backup database.
The master databases
>transaction logs are then backed up and restored to the backup database eve
ry 10 minutes.
> My users want to also have the ability to do manual full database backups
as well. However I am
> unsure whether or not this will have the affect that the log shipping oper
ations.
> Will this cause the master and backup databases to get out of synch until
the full backup and
> restore is performed at midnight?
> Thanks in advance
>

Log Shipping Alert Firing for database that no longer exists

I have an error 14421 (database has restore threshold for 45 minutes and has not restored in more than "so many" minutes)firing for a database that no longer exists. How can I remove this alert? I have other log shipping plans that are running and so I can't delete the Log shipping alert job.Okay - that was too easy - I've struggled with this for 2 days - ran a select on the table in msdb - found the row that was screwy, and deleted .... now all is fine. Gotta use that tsql every once in a while!