[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

Sporadic Sql Server Login Issue

I am taking full advantage of my subscription with these issues lately.  I have scoured the web and I can find NO REFERENCE to why I would be getting this error SPORADICALLY (there are tons of references where this issue comes up but not on a sporadic basis that I have seen):

Cannot open database "xxx" requested by the login. The login failed.

It happens here: www.gopherstateevents.com

and it happens with this connection string/ query:

Dim conn							
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLNCLI10;Server=216.185.199.1,1433\SQLExpress;Database=xxx;Uid=xxx;Pwd=xxx;"


sql = "INSERT INTO Visitors (When_Visit, Page, IPAddress, Browser) VALUES ('" & Now() & "', 'default', '" 
sql = sql & Request.ServerVariables("REMOTE_ADDR") & "', '" & Request.ServerVariables("HTTP_USER_AGENT") & "')"
Set rs=conn.Execute(sql)
Set rs=Nothing

Open in new window


It also happen in other places but, again, SPORADICALLY.  I am reasonably certain that the user has correct permissions and I don't even thing it is typically a high volume error.

Any help?
0
Bob Schneider
Asked:
Bob Schneider
  • 7
  • 3
  • 2
  • +1
6 Solutions
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Hi

Sounds like a timeout issue, try to extend time out settings a little.
How is described here
0
 
Bob SchneiderAuthor Commented:
I agree that it sounds like a time out issue now that you mention it.  However the link  you pointed me to indicates that  the connection timeout is 600 seconds.  Can't be that.  However if I click on the Connection Properties link on that screen I see that there is a connection timeout of 15 seconds.  That is also long but if I wanted to change it I don't see how because it is disabled.  Any ideas?
0
 
Jim P.Commented:
It looks like you are going to somewhere on the internet ith your data.

So could it be a timeout issue with the firewalls/network/routers and not strictly the SQL Server that is timing out?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
David ToddSenior DBACommented:
Hi,

Do check that your database settings - its on each database - don't have autoclose set to true. This was a default on some earlier versions of SQL Express, and if you've merely upgraded of migrated old databases without checking all the settings (who does that by the way) this could be catching you.

That is, after the database is closed, it takes time to spin it back up, and so the login for database AA which is currently closed times out before the database gets started ...

Just a thought.

Regards
  David
0
 
Bob SchneiderAuthor Commented:
Autoclose was set to True.  Thanks!  Are there any other settings that I should be aware of that might cause this problem?
0
 
Jim P.Commented:
The other one is to check if Auto-Shrink is on.
0
 
Bob SchneiderAuthor Commented:
Should it be?
0
 
Bob SchneiderAuthor Commented:
I did a little research and found that it should be off, which it is.

Thanks
0
 
Jim P.Commented:
Sorry for not expanding on it.

Autoshrink causes file fragmentation and delays.

Another tuning tip is that you want to evaluate the size of your log files. Rarely do you want to shrink them as small as they can get. Because when you are inputting or querying the data it uses the log. If there is no free space, it will delay the process as it grows the log.

Now there are some situations where you want to grow the log and then shrink them.  I had a database that we loaded millions of rows about the 3rd day of every month. The rest of the month it was almost nothing but select statements. So I would make the log about 20GB during the load process and then shrink it back to about 1.5GB for the rest of the time.
0
 
Bob SchneiderAuthor Commented:
I have had absolutely no errors since I set Auto-Close to False.  Thanks!

How do I control the log files?
0
 
David ToddSenior DBACommented:
Hi,

Transaction Log files
If a production system, then most should be in full recovery model, and then having hourly transaction log backups.

Otherwise consider using simple recovery model.

The danger of full recovery model and no transaction log backups is that every transaction is logged, and without the log backups, never cleared, so the log file continues to grow and grow ...

If in simple recovery model, after a checkpoint, space in log file can be reused. In full recovery model, after a transaction log backup, space in the log file can be reused.

So, in simple recovery model, very roughly speaking, the transaction log only needs to be big enough to hold all the transactions between checkpoints.

In full recovery model, the transaction log needs to be big enough to hold all the transactions between transaction log backups. You can therefore reduce the size of the transaction log by increasing the frequency of the transaction log backups.

HTH
  David

HTH
  David
0
 
Bob SchneiderAuthor Commented:
I have full once daily with hourly transaction logs and I have them going to a remote location.  I recently had to completely restore the database for and it worked perfectly so that was nice.

I keep the bak files and trans logs for 1 week.  The bak file sizes range from 17mb to 715mb with the trn files around 5mb or less.

I have unlimted growth and 10% set for my file sizes.

Thanks so much for all the help.  Still no errors since I sent Auto-Close to False.
0
 
Bob SchneiderAuthor Commented:
Another great discussion.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 7
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now