Post reply

Single User mode, closing open connections in T-SQL

  • March 19, 2008 at 7:36 am

    #184402

    I'm writing a script in which I am setting the database into single user mode before running some other commands. How can I programmatically change the user access mode AND have it close any open connections that may exist to the database in question. (BTW this is for a recovery script, hence the reason I'm not too bothered about kicking off users that are still connected!)

  • March 19, 2008 at 9:10 pm

    #791844

    Check books online "ALTER DATABASE (Transact-SQL)". The example:

    USE master;

    GO

    ALTER DATABASE AdventureWorks

    SET SINGLE_USER

    WITH ROLLBACK IMMEDIATE;

    GO

    I prefer restricted_user to single_user. Just leave yourself extra doors in case any process takes over this single user access unexpectedly. Like backup, etc.

    BOL also says 3 tasks need to be done before single_user mode. You may need in your case.

    1. Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.

    2. Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.

    3. If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.

  • March 20, 2008 at 5:17 am

    #791922

    I had this in my script but didn't realise it was killing connections! A re-read of BOL I can see now it's the WITH ROLLBACK IMMEDIATE is termination information that's doing that. More haste less speed!

    Thanks for the other tips!

Viewing 3 posts - 1 through 2 (of 2 total)

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