Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Background process and SINGLE_USER Expand / Collapse
Author
Message
Posted Thursday, September 23, 2010 2:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:11 AM
Points: 71, Visits: 155
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


  Post Attachments 
spwho.JPG (15 views, 27.46 KB)
Post #991813
Posted Tuesday, September 28, 2010 2:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:11 AM
Points: 71, Visits: 155
any gurus out there?


Patrick SIMONS, MCP
Post #994237
Posted Wednesday, August 29, 2012 2:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 6:18 AM
Points: 3, Visits: 9
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>;
Post #1351465
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse