Re: Manual log shipping

From: Rob (anonymous_at_discussions.microsoft.com)
Date: 02/20/04 

Date: Fri, 20 Feb 2004 09:30:28 -0800

But even in single user mode, there could be multiple
connections to the database, which can cause manual log
shipping failures. In this case, I find killing all user
connections more effective, to ensure no connections
exists prior to restoring either the full backup and/or
the tran log.

Thanks.

>-----Original Message-----
>Rather than doing a KILL command on each SPID, a cleaner
>way to do it is to put the database in single user mode
>with rollback immediate for the duration of the log
>restore and then put it back in multi user mode. This
>works very well. Here's an example:
>
>alter database database_name set SINGLE_USER with
rollback
>immediate
>
>restore log database_name from disk
>= 'c:\database_name_log.bak' with standby
>= 'c:\standby\database_name.bak'
>
>alter database database_name set MULTI_USER
>
>>-----Original Message-----
>>Your observations are correct. Log shipping will fail
if
>there are users connected to the database. I am
wondering
>if perhaps someone changed the dboptions when you had the
>log shipping fail after a BCP import.
>>
>>And no, I have not experienced any other issues. The
>custom log shipping approach works very well.
>>
>>You can find a script that will kill any connections to
>the specified database here:
>>http://sqlguy.home.comcast.net/logship.htm
>>
>>--
>>Keith
>>
>>
>>"Rob" <anonymous@discussions.microsoft.com> wrote in
>message news:1332101c3f7bf$64a958e0$a301280a@phx.gbl...
>>> Hello:
>>>
>>> I've implemented a stand-by server solution, where the
>>> tran log backup from the primary server gets restored
>to
>>> the secondary server at every 15-min interval.
>>>
>>> I understand that there are some limitations with this
>>> approach (could not implement MS SLS as our business
>unit
>>> could not afford to purchase the Ent. Ed.), and was
>>> wondering if anyone has encountered any other issues
or
>>> observations when implementing a similar manual log
>>> shipping process, other than my own observations
listed
>>> below:
>>>
>>> Log Shipping will fail if....
>>>
>>> - ...there are any open connections to the database
>where
>>> the transaction log files are restored to; though
>querying
>>> tables using the fully qualified name is possible from
>>> another database connection or via a linked server
>>> connection.
>>>
>>> I've also had one incident where my log shipping
>process
>>> failed due to a LSN out of sync issue. This happened
>when
>>> I ran a BCP IN operation. Other times, both BCP and
>BULK
>>> INSERT operations ran successfully, funnelling changes
>to
>>> the secondary server's database as expected.
>>>
>>> Thank you for all your responses.
>>>
>>> Regards,
>>>
>>> - Rob.
>>.
>>
>.
>

Relevant Pages

  • Re: How can I open an SQL database and be the only one who has access to it?
    ... you can determine how SS permits access to your server. ... SINGLE_USER allows one user at a time to connect to the database. ... controlled by the termination clause of the ALTER DATABASE statement. ... To allow multiple connections, the database must be changed to ...
    (microsoft.public.data.ado)
  • Re: ORA-12518, 04030 errors after 9i-10G upgrade.
    ... All went ok until users started connecting in 100+ numbers, database is ... set up for dedicated server connections, this was never a problem in 9i. ... This did not solve problems so bounced database and decreased SGA to ... out of process memory errors. ...
    (comp.databases.oracle.server)
  • Re: ORA-12518, 04030 errors after 9i-10G upgrade.
    ... All went ok until users started connecting in 100+ numbers, database is ... set up for dedicated server connections, this was never a problem in 9i. ... out of process memory errors. ... that Oracle is having issues with OS resources. ...
    (comp.databases.oracle.server)
  • Re: ORA-12518, 04030 errors after 9i-10G upgrade.
    ... New server has 6GB ram. ... All went ok until users started connecting in 100+ numbers, database is ... set up for dedicated server connections, this was never a problem in 9i. ... This did not solve problems so bounced database and decreased SGA to ...
    (comp.databases.oracle.server)
  • Re: How to handle large number of users
    ... The client app will work on mobile phones using WinCE and it is just a small app updating some tables in the database on the server. ... use a Database Pooling mechanism, that controlled the number of active connections to the DB server through the pool. ... You are not going to be storing mountains of data, the streams will be small and the data capture should be relatively small (unless you are collecting transaction times, rates, etc). ... Lets assume that your transaction rate for inserting and/or updating the database for the 10K data stream is 100 milliseconds. ...
    (borland.public.delphi.non-technical)
Loading