Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Background process and SINGLE_USER


Background process and SINGLE_USER

Author
Message
PatrickSimons
PatrickSimons
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 208
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
Attachments
spwho.JPG (16 views, 27.00 KB)
PatrickSimons
PatrickSimons
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 208
any gurus out there?


Patrick SIMONS, MCP
walidkilani
walidkilani
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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>;
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search