Background process and SINGLE_USER

• PatrickSimons

  Right there with Babe

  Points: 784

  More actions

  September 23, 2010 at 2:37 am

  #157216

  Hi,

  we have a critical heavy loaded task which is running through all user databases of our SQL-Server 2005 (SP3). This operation on each database consists in rebuilding indexes, data changes, updating stored procs....

  To be sure that no one is working on a db we are setting each database to SINGLE_USER, when the operation finished with one db, MULTI_USER is set, then the next db and so on. This works well for all normal cases, except sometimes an error occurs in our app:

  "This database is already open and can only have one user at a time..."

  The application didn't left its connection and nobody else is working on the server! We did some research and found out that a CHECKPOINT background process is swiping the control over our SINGLE_USER connection.

  See sp_who attachement.

  Is there a way to disable or delay the background task? As the Checkpoint writes data from memory to disk, is it possible to manually force a Checkpoint without losing data ?

  Thanks,

  Patrick

  Patrick SIMONS, MCP

• PatrickSimons

  Right there with Babe

  Points: 784

  More actions

  September 28, 2010 at 2:09 am

  #1228187

  any gurus out there?

  Patrick SIMONS, MCP

• walidkilani

  Valued Member

  Points: 55

  More actions

  August 29, 2012 at 2:22 am

  #1531261

  1. Query the USESSION table for the users 'session_id':

  select session_id from USESSION where delete_session_id is NULL and user_id = (select user_id from users where user_name = '<Enter User Name>');

  2. Delete the users records in the table UPKLIST:

  Delete from UPKLIST where session_id = <session_id>;

  3. Delete the users records in the table PROJSHAR:

  Delete from PROJSHAR where session_id = <session_id>;

  4. Delete the users records in the table USESSION

  Delete from USESSION where session_id = <session_id>;