I am not a DBA, but have dealt with a number of POS applications that use SQL Server 2005 as their back end.
That said, I'm no pro, but I can generally keep a DB running. Here is my problem scenario:
Everyday, at least three times a day, the server spontaneously starts rejecting all connections. The event log shows repeated messages of Event ID 18456 which is a logon issue. The server continues to reject all connections from that point until the machine is restarted. Restarting the SQL server without restarting the machine accomplishes nothing...
Looking at the SQL logs gets some further information:
Event ID: 18456, Severity: 14, State: 10
Apparently, state is of some significance though all of the links I have found that explain what the various states mean leave out number 10. (such as here)
This problem did not start happening until 4 days ago. The only event I can think of that also happened at this time was the installation of PHP (which doesn't even touch SQL Server...)
Sorry, when I say "the server starts rejecting connections", I am referring to the SQL Server. The error message posted, with the severity and state specifications, was taken straight from the SQL Server Log.
As far as being online, the SQL server is hosted locally though it is accessed by some .NET 2.0 web services, also on the same server. Is that what you meant by online?
I could not find any "Access Denied" type questions in any of the event logs...
No, that's not quite what I meant. A database can be offline (unavailable to users) or online (available).
The reason I asked about the error messages was because we had an issue whereby the number of TCP connections in a WAIT state was exceeding the default value of 4000 and the log file of the application that was trying to connect to the server contained a lot of the errors that I mentioned.
After doing some monitoring to determine the peak number of attempted connections, we increased the MaxUserPort Registry setting an this resolved the issue by allowing a higher number of connections to have a WAIT status.