Post reply

After attached with sp_attach_single_file_db,the database is still offline at times

  • July 5, 2005 at 3:51 pm

    #186455

    In my application, each sql command is executed by sqlCommand ---- the data objet provided by DotNet.

    I want to attach and configure database programmaticly.Most time it works fine. But sometimes,there is an error,which says database is offline and fail to configure. At that time,I opened the Enterprise Manager and found the database was really offline. But I can set the database online manually.

    (To make sure there is enough time for database to be attached, a loop query is executed to check that if the database status is ONLINE. After 3 minutes,if the database is not online, the application will exit.)

    Any idea about this problem?

    I do appreciate your help!

     

     

     

     

     

  • July 5, 2005 at 4:07 pm

    #571048

    Is this on MSDE ?

     


    * Noel

  • July 5, 2005 at 4:13 pm

    #571049

    NO. It is sqlServer2000.

  • July 5, 2005 at 6:16 pm

    #571058

    How busy is the server?  How much activity was there on the attached database?  If the attached database is trying to run through recovery and the activity level has been high, it might take longer than 3 minutes on a busy server. 

    And then again, I might be wrong ...
    David Webb

  • July 5, 2005 at 6:21 pm

    #571060

    This kind of problems tend to be found at MSDE level not at Standard/Enterprise Level. Anyway you shouldn't be doing this on a "normal" sql server often

    What I would do to ovecome the issue is :

    AFTER you run the sp_attach_single_file_db I would run:

    ALTER DATABASE dbname SET ONLINE WITH ROLLBACK IMMEDIATE    

    And would give that a try. I am almost positive that you have other problems but without further info could be a good workaround. 

    hth


    * Noel

  • July 5, 2005 at 7:20 pm

    #571064

    Hi All,thanks for your help!

    The codes mentioned above will be executed during installation of a system-level application,which will drop old database at first, then copy the pre-built database file to dest directory and attach the database by sp_attach_single_file_db. Only after the database is ONLINE, subsequent operations can be performed.

    At times ,database is offline after attach.

    I will try to "ALTER DATABASE dbname SET ONLINE WITH ROLLBACK IMMEDIATE   "after attching it and hope it works.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply