none
Log shipping and closing connections issue

    Question

  • We are using log shipping to update a reporting database on a nightly basis. We keep running into issues of users/developers leaving for the day and leaving either a report running or a SMS open and connected to the reporting database. This is causing our log shipping jobs to fail.  After getting in and forcing the connection close we have seen where SMS will connect again in the middle of the log shipping job and fail once more. Has anyone else had to deal with an issue like this? Is there a script that can be run to force the connections closed and keep them off? We have tried putting the reorting database into single-user mode for this but it has a 30% success rate.

    Any thoughts or ideas would be greatly appreciated.

    Tuesday, June 12, 2012 2:15 PM

Answers

  • Hello,

    That’s the way it works. To perform the restores exclusive access is required.

    I would like to suggest you to try creating a mirror between instances, then create a database snapshot from the mirror database, and run reports against the database snapshot. This requires Enterprise Edition.

    Another option may be replication.

    Hope this helps.

                      
    Regards,
    Alberto Morillo
    SQLCoffee.com


    Tuesday, June 12, 2012 5:43 PM
    Moderator