?
Solved

Sporadic Sql Server Login Issue

Posted on 2014-01-19
13
Medium Priority
?
502 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 23

Assisted Solution

by:Patrick Bogers
Patrick Bogers earned 336 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 996 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 668 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 996 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 996 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 668 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

764 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