SQL Databse keeps resetting to Single User

[bograt]

Hi, we have a SQL enabled site on a co-hosted server. The Database keeps resetting to Single User. Does one have to have admin rights to do this (we do not) or would a bit of asp code be doing this?

Thank you for you help

Quentin

 

[osjohnm]

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

 

[bograt]

Thanks for you reply. SQL2000 with latest updates.
If the transaction logs reach max would that switch database to single user?

Quentin

 

[SQLSister]

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.

 

[osjohnm]

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

 

[crot]

Could you please give more details, how "to run a trace with SQL Profiler".

Thanks

 

[osjohnm]

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.

http://www.sql-server-performance.com/sql_server_profiler_tips.asp

John