Solved

Sporadic Sql Server Login Issue

Posted on 2014-01-19
13
476 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 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

11 Experts available now in Live!

Get 1:1 Help Now