Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL Databse keeps resetting to Single User
- Thread starter bograt
- Start date
A login with sysadmin rights can change database options like single user etc.
Does it happen all the time or at same time everday?
Are there any sql jobs or maintenance plans that are failing?
Often database maintenance plans have an option to repair any minor errors before it backs up a database. If it can't fix the error it often leaves the database in single user mode. This happens in SQL Server 7 quite often but SP3 fixes this but I don't think it happens in SQL Server 2000.
What version of SQL Server is running and which service pack?
John
Does it happen all the time or at same time everday?
Are there any sql jobs or maintenance plans that are failing?
Often database maintenance plans have an option to repair any minor errors before it backs up a database. If it can't fix the error it often leaves the database in single user mode. This happens in SQL Server 7 quite often but SP3 fixes this but I don't think it happens in SQL Server 2000.
What version of SQL Server is running and which service pack?
John
- Thread starter
- #3
Suggest you talk to whoever does have system rights and explain the problem you are having. They may know exactly why it is being set to single user and may not have realized that it wasn't setting back to multiuser after the task was done or that it impacted someone. Also, if they don't know anything, you may want to change your sa password (and the passwords of any user who has sa rights) because it is possible someone has broken into your system and is changing it to single user deliberately to create problems.
Bograt
I'm not sure if the transaction log running out of space would set a database into to single user mode but it would cause any number of problems, so actually there is a possibilty that the logs might be the issue.
I agree with SQLSister, speak to someone who does have rights and see if they are executing something against the database.
Another idea is to run a trace with SQL Profiler to see what is happening on the server and who is logged in.
John
I'm not sure if the transaction log running out of space would set a database into to single user mode but it would cause any number of problems, so actually there is a possibilty that the logs might be the issue.
I agree with SQLSister, speak to someone who does have rights and see if they are executing something against the database.
Another idea is to run a trace with SQL Profiler to see what is happening on the server and who is logged in.
John
Hi Crot
In Enterprise Manager under tools select SQL Profiler.
You then need to open a new trace file and select which events you want to trace such as transactions, stored prcedures etc.
You can also filter out data that you don't want by choosing options under the filters tab and choose which data cloumns you want under the Data Columns tab.
Profiler gives you an option to save the data into a file or sql table.
All you do then is run it and stop it when you have accumulated enough data.
Here is a link that covers performance tips when running sql profiler. Note SQL profiler uses a lot of resources and can become quite an overhead on the server so I wouldn't recommend that you run it too often.
John
In Enterprise Manager under tools select SQL Profiler.
You then need to open a new trace file and select which events you want to trace such as transactions, stored prcedures etc.
You can also filter out data that you don't want by choosing options under the filters tab and choose which data cloumns you want under the Data Columns tab.
Profiler gives you an option to save the data into a file or sql table.
All you do then is run it and stop it when you have accumulated enough data.
Here is a link that covers performance tips when running sql profiler. Note SQL profiler uses a lot of resources and can become quite an overhead on the server so I wouldn't recommend that you run it too often.
John
- Status
Similar threads
- Question
