Load database: A Recurring Issue!

Feb 24

Posted by

Hi Guys,

As we already aware for loading a database , there cann’t be any user online in the DB.

In our env, we are killing these spids before actual database load in script.

Sometime it works, sometime fails as clinet ids logged in so frequently in server so during the actual load statement database again comes in use status.

This is very frequent issue which I have seen in many enviorment where clined ids logged in very rapidally.

For that we using the some alternative as locking the ids manually and once the load starts we unlock.( We cannt wait untill load finish for unlock of ids) . This task required the manual intervention.

I would like to raise a question, why sybase cann’t provide the load database with kill option, beacuse any above such alternative would not be 100% effective as killing the spids ( by any way) and loading the database is two diffrent task. Between both task execution, there must be some time gap may be in millsecond, and that time gap is suffcient for any login id to logged ins.

One more option can be :

We can make database in offline state( something like that so no one can use it) before load database, aftre that we will go for load database.

Kindly let me know your thoughts and suggest if any thing I am missing above.

I have posted the same in various forums as well, will update you if I’ll get any response.

till then..

bye

Posted on February 24, 2011, in ASE and tagged , , , , . Bookmark the permalink. 6 Comments.

  1. Anurag | February 24, 2011 at 10:57 PM

    Hi,
    I don’t know much about DBA side, however i would like to put forth a point that if we run the command of load database, not sure this requires login in database or not, so while killing the login ids (as per your suggestion) it might be possible that it would try to kill the id which is running load database command itself. Similarly while bringing database offline how the id would run command of load database.

    Any thought please.

    –Anurag–

    Reply
  2. Suraj | March 5, 2011 at 7:18 PM

    Hi ,
    In the second option you mention like .. make the database offline before load… Is there any command to make the DB offline..?

    Reply
    • sybanva | March 5, 2011 at 11:17 PM

      Hi Suraj,

      There is no command to make a db offline, we can do it by only loading from a dump ( thts not possible in this case).
      There is one undocumented dbcc command dbcc reboor with shutdown_load option, but that also require the no user in the database. Thnx.

      sybanva.

      Reply
  3. Pankaj | May 31, 2011 at 4:48 PM

    Hi,
    you are doing load using script? If using script, you can add the lock and unlock things into script . So you do not need manual intervention. Else you can think about changing the password for login id , then try load database and finally change back the password.

    Reply

  1. Pingback: Tweets that mention Load database: A Recurring Issue! « Sybase Blog -Anything About Sybase ASE,REP,IQ. -- Topsy.com

Leave a comment