Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
programmerx101
Starting Member
2 Posts |
 Posted - 2009-10-07 : 14:26:19
|
| Three questions actually. The questions apply to SQL Server 2000, 2005, 2008.1) Can a technical glitch (EG:Software, asp classic code, php code, asp.net code, bad cursors, bad lock types, too much data commit, etc etc) cause a database to go into read only mode?2) Can a database go into Read_only mode by itself, with seemingly no reason and nothing in the windows error logs to show a malfunction occured?3) Can a database go into offline mode by itself, again, with seemingly no reason and nothing in the windows error logs to show a malfunction occured?I would VERY strongly appreciate if anyone and everyone would give a yes or no answer to the three questions above :)If the answer to any of those questions is yes, do you have any theories how this could happen?I will also mention that this has happened sporadically over the course of 2 years. It has happened to not 1 but 3 different databases each running on a completely different machine from one another. It has happened on 3 different computers each running a different version of SQL Server, and which have had nothing to do with each other. For one of the database is has happened a few times while using an ASP Classic code front-end. The other two database were/are using an ASP.NET code front-end and being connected to using SubSonic.Would appreciate any insight anyone can give on this matter. Thank you |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
programmerx101
Starting Member
2 Posts |
Posted - 2009-10-08 : 12:52:28
|
| Thank you for your response tkizer - I very much appreciate it. Can you think of any way I can trace HOW, WHO, or WHAT precisely is performing these actions? I suppose I am looking for a bombshell here... definitive proof that it is indeed someone and not something which is performing these actions. The last thing I would want to do is point fingers at someone without knowing my facts before hand, I'm sure you can understand.I am most grateful for your assistance :) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-10-08 : 13:05:54
|
| You would need to run a constant trace, perhaps one that overwrites itself every couple of days. You'd probably only need to trace for the SQL:StmtCompleted event, and you would filter on only those accounts that have permissions to do the action. I would think only sysadmin can make that change, but perhaps there's also a fixed server role that is able to make the change too.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
ViktorKoju
Starting Member
3 Posts |
Posted - 2009-10-08 : 18:58:12
|
| Used to happen with me a lot with sql 2000. After a lot of research it came out to be due to a combination of script timeout and locks - specially during large updates. After setting the locktimeout to alow value and improving the application, everything is fine for last 3 years. We are now in sql 2008. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
HELP! Read only database!