Solved

Sporadic Sql Server Login Issue

Posted on 2014-01-19
13
484 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 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

18 Experts available now in Live!

Get 1:1 Help Now