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 ?
Patrick SIMONS, MCP