Solved

Sporadic Sql Server Login Issue

Posted on 2014-01-19
13
479 Views
Last Modified: 2014-01-21
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
Comment
Question by:Bob Schneider
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 19

Assisted Solution

by:Patricksr1972
Patricksr1972 earned 84 total points
ID: 39792728
Hi

Sounds like a timeout issue, try to extend time out settings a little.
How is described here
0
 

Author Comment

by:Bob Schneider
ID: 39792742
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
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 249 total points
ID: 39792920
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
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 167 total points
ID: 39793380
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
 

Author Comment

by:Bob Schneider
ID: 39794147
Autoclose was set to True.  Thanks!  Are there any other settings that I should be aware of that might cause this problem?
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 249 total points
ID: 39794176
The other one is to check if Auto-Shrink is on.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Bob Schneider
ID: 39794418
Should it be?
0
 

Author Comment

by:Bob Schneider
ID: 39794426
I did a little research and found that it should be off, which it is.

Thanks
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 249 total points
ID: 39794524
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
 

Author Comment

by:Bob Schneider
ID: 39795785
I have had absolutely no errors since I set Auto-Close to False.  Thanks!

How do I control the log files?
0
 
LVL 35

Accepted Solution

by:
David Todd earned 167 total points
ID: 39795874
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
 

Author Comment

by:Bob Schneider
ID: 39796620
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
 

Author Closing Comment

by:Bob Schneider
ID: 39796623
Another great discussion.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now