SQL Server 2005 Express Freezing

[TTops]

I have a SQL Server 2005 Express database that is being touched by Microsoft Access and VBA code written inside AutoCAD 2009. I am having trouble with this database becoming very sluggish and eventually freezing. It seems that when I log onto the server with an administrative account, the database starts functioning normally without taking any action. If I log on with a non-administrative account, I can't see the processes at work and therefore, cannot isolate the issue. Any suggestions?


Thanks,
T-Tops

 

[gmmastros]

There are a variety of reasons. Since you mention Express, I would encourage you to check the "AutoClose" setting. SQLCop has a built-in check for this.

http://sqlcop.lessthandot.com

Download SQL Cop, and then expand Configuration and click on "Auto Close".

If auto-close is turned on, and your app uses disconnected recordsets, this could certainly account for your sluggish behavior.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TTops]

I don't use disconnected recordsets and auto-close is set to false. This behavior seems to suggest that when I (or anyone else) log on as an administrator, it "wakes up" something on the server or in the SQL service. This server is also running Exchange Server 2007.

Thanks,
T-Tops

 

[Qik3Coder]

Have you tried connecting a profiler to it from another machine in both cases?

When you say, log on as admin "No problems"...Log on as user "problems", do you mean your logging on and doing the stuff that you were doing as a user but now it works, or you are giving admin permissions to a user that regularly uses the machine?

There is a big difference between "My test script works" and "The user had the app open for 4hrs, was surfing the internets and etc. etc.

Lod

I'll answer your question, not solve your problem

 

[TTops]

What I mean is that I can log on as an administrator to the SQL Server using RDP from any machine and do nothing else and the performance of the SQL database improves vastly. I gave the user permission to RDP to the SQL Server with a non-administrative account and the performance is still sluggish. The user is not normally logged in to the SQL server directly. He uses AutoCAD on his local machine and the VBA code I wrote for the AutoCAD application accesses the SQL server to retrieve and write data. I have not tried Profiler.

Thanks,
T-Tops