March 24, 2003 at 8:16 am
I'm not sure what's causing this problem, but on one of our SQL Servers, a database (any of them) changes itself to SINGLE USER MODE. It occurs sporadically.
I'm wondering if backups have something to do with this. This server automatically does a log backup every hour and then a full backup to tape at 2:00 AM in the morning. If a backup is occuring, and someone has a table open, would this cause the database to automatically go into single user mode, and then stay there?
March 24, 2003 at 8:32 am
SQL 2000 or 7? Not that it matters for a user in the db. The db backups (full, diff, and log) are designed to run with users in the database.
Do you have any maintenence plans set? These can be cuplrits at night if they detect errors and attempt to fix them, but they would not be able to get the database into single user mode without all users being out of the database. Could that be the case?
Are you using any dev or other tools? They might be issuing this command as well.
Best thing might be to setup profiler when there is a highprobability of this happening and getting a trace of who set it.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 24, 2003 at 9:38 am
Check for any job that might be causing this. If a job executes a stored procedure, check the stored procedure to see if it change the status of the db.
Agree to trace with profiler too.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply