使用SQL Server 2000访问2k3：几个小时后会非常慢 [英] Access 2k3 using SQL Server 2000: will go very slow after several hours
问题描述
主机PC：赢得SBS 2003 2.6 GHz处理器和1GB内存
SQL Server 2000 sp3（或最新）.MS Office 2k3
我有访问2k3前端在服务器上运行（这可能会改变
）并且一直很好，直到1周前升级时我安装了
a新版本的mde（带有通过VB6安装程序setup.exe创建一个新的VB
应用程序。
Access将odbc用于SQL Server上的链接表，加上一些本地
表。
我也为我生成的所有记录集使用DAO（随后
close，set = nothing，宗教信仰）。 />
这个系统已经运行了几个星期很稳定但是现在，在升级之后
和新的VB应用程序，我有几个资源类型错误，例如
错误3035超出系统资源
错误????没有足够的内存来执行此操作
+如果上面清除了一般ODBC错误
也
错误3633无法加载dll MSJTER40 .DLL（毫秒喷射错误处理程序）
当Access第一次运行时，一切都很顺利，但在说了8个小时后，Access
应用程序停止运行。 cpu进程％很低，有400M RAM
可用，其他应用程序都可以，但访问方式不好，仍然可以运行但是大约是1/10速度或更低。
检查任务管理器后，我注意到在8小时左右后，
我的内存高达160MB，处理数高达86,000！
现在，当我启动访问应用程序时，mem = 28M，句柄数=
260.
6个小时，这已经达到了46M和834.
然后在2个小时之后检查手柄计数是每隔几秒钟就会增加100美元。
这与经常基于计时器的查询和我运行的功能有关，
但为什么会有戏剧性的变化？
如果我停止/只启动访问，性能没有太大提高，我也必须停止/启动SQL Server。
我想知道我是否有一些如何使用VB
安装损坏的mdac内容停滞不前的旧dll： -
MSJTER35.DLL
MSJET35.DLL
dao350.dll
msado20.tlb
那么如何解决？
好吧我不想只是改变一切&什么东西来了
想到，我想要更有条理但我不知道在哪里
首先开始，任何帮助赞赏。
2004年10月16日08:25:31 -0700， mo * *****@yahoo.co.uk （B Moor）写道：
我没有看到你提到的行为，我们有2个访问应用程序
（Access 2000）一直在针对SQL Server运行，并且他们可能会花费数周的时间来运行一些严重的数据操作而不需要系统
重新启动。因此它是*可能的。
一些建议：
*确保你拥有最新的Jet服务包：Jet 4.0 SP8。
*使用PerfMon缩小资源消耗的范围。也许
也使用SQL Server Profiler。
*标准调试技巧：简化问题。尝试通过消除步骤来隔离导致此行为的
部分。
*不确定为什么提到dao350;我认为你应该使用dao360。
-Tom。
主机PC：用2.6 GHz处理器赢得SBS 2003 1GB RAM
SQL Server 2000 sp3（或最新）.MS Office 2k3
我有访问2k3前端在服务器上运行（这可能会改变），一切都很顺利直到1周前升级，我通过VB6安装程序setup.exe安装了新版本的mde（进行了很小的更改）和新的VB
应用程序。
Access将odbc用于SQL Server上的链接表，以及一些本地表。
我还使用DAO生成我生成的所有记录集（并且随后
关闭，设置=没有任何宗教信仰）。 br />
这个系统已经运行了几个星期但是现在，在升级后和新的VB应用程序之后，我有几个资源类型错误，例如
错误3035超出系统资源错误????没有足够的内存来执行此操作
+如果上面清除了一般ODBC错误
也错误3633无法加载DLL MSJTER40.DLL（ms jet错误处理程序）
当Access第一次运行时，一切都很顺利，但在说了8个小时后，Access
应用程序停止运行。 cpu进程％很低，有400M RAM可用，其他应用程序都可以，但是访问方式很糟糕，仍然可以运行，但速度大约是速度的1/10。
在检查任务管理器后，我注意到在8小时左右之后
我的mem usage高达160MB，处理数高达86,000！
现在当我启动访问应用程序时mem = 28M，手柄数=
260.
6小时后进行1次测试，最高可达46M和834.
然后在2小时后检查，手柄数量增加了
每隔几秒就会出现一次。
这与基于计时器的常见查询和我运行的功能有关，但为什么会有戏剧性的变化呢？
如果我只停止/开始访问，性能没有大大提高，我也必须停止/启动SQL Server。
我想知道我是否有一些如何损坏的VB与mdac的东西
安装旧版dll时安装： -
MSJTER35.DLL
MSJET35.DLL
dao350.dll
msado20.tlb
那么如何解决？
好吧，我不想只是改变一切&任何想到的东西，我想要更有条理，但我不知道在哪里首先开始，任何帮助赞赏。
围绕需要设置DAO对象有很多误解
引用Nothing。关键不在于它们需要明确地设置为Nothing ber se，而是必须关闭它们并以相反的顺序释放
依赖顺序。 br />
例如，如果我打开一个工作区，打开一个数据库，并打开一个记录集，我需要关闭记录集并释放引用，关闭数据库和
发布参考，然后关闭工作区并释放参考。
2004年10月16日08:25:31 -0700， mo ****** @ yahoo.co.uk （B Moor）写道：
主机PC：使用2.6 GHz处理器和1GB RAM赢取SBS 2003
SQL Server 2000 sp3（或最新版本）.MS Office 2k3
我可以访问2k3前端服务器（这可能会改变）并且一直都很好，直到1周前升级时我安装了新版本的mde（有很小的改动）和一个新的VB
应用程序一个VB6安装程序setup.exe。
我也使用DAO生成我生成的所有记录集（随后
关闭，设置=没有任何宗教信仰） 。
这个系统已经运行了几周但是现在，在升级后和新的VB应用程序之后，我遇到了几种资源类型错误，例如错误3035超出了系统资源
错误????没有足够的内存来执行此操作
+如果上面清除了一般ODBC错误
也错误3633无法加载DLL MSJTER40.DLL（ms jet错误处理程序）
当Access第一次运行时，一切都很顺利，但在说了8个小时后，Access
应用程序停止运行。 cpu进程％很低，有400M RAM可用，其他应用程序都可以，但是访问方式很糟糕，仍然可以运行，但速度大约是速度的1/10。
在检查任务管理器后，我注意到在8小时左右之后
我的mem usage高达160MB，处理数高达86,000！
现在当我启动访问应用程序时mem = 28M，手柄数=
260.
6小时后进行1次测试，最高可达46M和834.
然后在2小时后检查，手柄数量增加了
每隔几秒就会出现一次。
这与基于计时器的常见查询和我运行的功能有关，但为什么会有戏剧性的变化呢？
如果我只停止/开始访问，性能没有大大提高，我也必须停止/启动SQL Server。
我想知道我是否有一些如何损坏的VB与mdac的东西
安装旧版dll时安装： -
MSJTER35.DLL
MSJET35.DLL
dao350.dll
msado20.tlb
那么如何解决？
好吧，我不想只是改变一切&任何想到的东西，我想要更有条理，但我不知道在哪里首先开始，任何帮助赞赏。
Tom van Stiphout< no ************* @ cox.net>在消息新闻中写道：< 96 ******************************** @ 4ax.com>。 ..2004年10月16日08:25:31 -0700， mo ***** *@yahoo.co.uk （B Moor）写道：
我没有看到你提到的行为，我们有2个访问应用程序
（Access 2000）运行SQL Server一直都在运行几周，在没有系统重启的情况下进行一些严肃的数据操作。因此*是*可能。
一些建议：
*确保您拥有最新的Jet服务包：Jet 4.0 SP8。
*使用PerfMon缩小资源消耗的范围。也许
也使用SQL Server Profiler。
*标准调试技巧：简化问题。尝试通过消除步骤来隔离导致此行为的部分。
*不确定为什么提到dao350;我想你应该使用dao360。
-Tom。
谢谢Tom，
我会接受你的建议。
我不清楚，并没有找到太多的信息搜索，因为
什么可能导致过多的句柄数。我知道一个句柄
将用于任何新对象，所以我创建的那些必须关闭，
set = nothing，我这样做。那么这又是什么呢？也许，如果我知道这个问题，我会更多地了解这个问题。
任何想法？
我提到dao35 as虽然我的VB6使用ADO，但它包含在VB6
安装的项目构建中。在安装过程中，我选择了保留现有的新文件，但我没有注意到这是用于哪个
文件。我的直觉（更像是猜测）的感觉就是我
以某种方式得到了两个不同版本的dao / ado / odbc（= mdac？）混合
up导致奇怪的结果？
问候，
Brian
Host PC: Win SBS 2003 with 2.6 GHz processor and 1GB RAM
SQL Server 2000 sp3 (or latest).MS Office 2k3
I have the access 2k3 front end running on server (this may get
changed) and all was well until an upgrade 1 week ago when I installed
a new version of the mde (with very minor changes) and a new VB
application via a VB6 installer setup.exe.
Access uses odbc to linked tables on SQL Server, plus a few local
tables.
I also use DAO for all the recordsets I generate (and subsequently
close , set= nothing religiously).
This system has run quite stable for weeks but now, after the upgrade
and new VB app, I have had several resource type errors eg
Error 3035 System resource exceeded
Error ???? There isnt enough memory to perform this operation
+ general ODBC errors if above cleared
also
Error 3633 Cant load dll MSJTER40.DLL (ms jet error handler)
All is well when Access 1st run but after say 8 hours or so the Access
app grinds to a halt. The cpu process % is low,there is 400M RAM
available, other apps are ok but access is in a bad way, still
functioning but at approx 1/10 the speed or less.
Upon checking the task manager I noticed that after the 8 hours or so
I had mem usuage upto 160MB and handle count upto 86,000 !
Now when I start the access app the mem= 28M and the handle count =
260.
On 1 test after 6 hours this had gone upto 46M and 834.
But then on checking 2 hours later the handle count was jumoing up by
the 100 every few seconds.
This is related to frequent timer based queries and functions I run,
but why the dramatic change ?
If I stop/start access only, performance is not greatly improved, I
have to stop/start SQL Server as well.
I am wondering if I have some how corrupted mdac stuff with the VB
install as it installed older dlls :-
MSJTER35.DLL
MSJET35.DLL
dao350.dll
msado20.tlb
So how to fix ?
Well I dont want to just go changing everything & anything that comes
to mind, I would like to be more structured but I am not sure where to
start first, any help appreciated.
On 16 Oct 2004 08:25:31 -0700, mo******@yahoo.co.uk (B Moor) wrote:
I have not seen the behavior you mention, and we have 2 Access apps
(Access 2000) running against SQL Server all the time, and they may
run for weeks doing some serious data manipulation without the system
being rebooted. So it *is* possible.
Some suggestions:
* Make sure you have the latest Jet service pack: Jet 4.0 SP8.
* Use PerfMon to narrow down where the resources are consumed. Perhaps
also use SQL Server Profiler.
* Standard debugging trick: simplify the problem. Try to isolate the
part that causes this behavior by eliminating steps.
* Not sure why you mention dao350; I think you should be using dao360.
-Tom.
Host PC: Win SBS 2003 with 2.6 GHz processor and 1GB RAM
SQL Server 2000 sp3 (or latest).MS Office 2k3
I have the access 2k3 front end running on server (this may get
changed) and all was well until an upgrade 1 week ago when I installed
a new version of the mde (with very minor changes) and a new VB
application via a VB6 installer setup.exe.
Access uses odbc to linked tables on SQL Server, plus a few local
tables.
I also use DAO for all the recordsets I generate (and subsequently
close , set= nothing religiously).
This system has run quite stable for weeks but now, after the upgrade
and new VB app, I have had several resource type errors eg
Error 3035 System resource exceeded
Error ???? There isnt enough memory to perform this operation
+ general ODBC errors if above cleared
also
Error 3633 Cant load dll MSJTER40.DLL (ms jet error handler)
All is well when Access 1st run but after say 8 hours or so the Access
app grinds to a halt. The cpu process % is low,there is 400M RAM
available, other apps are ok but access is in a bad way, still
functioning but at approx 1/10 the speed or less.
Upon checking the task manager I noticed that after the 8 hours or so
I had mem usuage upto 160MB and handle count upto 86,000 !
Now when I start the access app the mem= 28M and the handle count =
260.
On 1 test after 6 hours this had gone upto 46M and 834.
But then on checking 2 hours later the handle count was jumoing up by
the 100 every few seconds.
This is related to frequent timer based queries and functions I run,
but why the dramatic change ?
If I stop/start access only, performance is not greatly improved, I
have to stop/start SQL Server as well.
I am wondering if I have some how corrupted mdac stuff with the VB
install as it installed older dlls :-
MSJTER35.DLL
MSJET35.DLL
dao350.dll
msado20.tlb
So how to fix ?
Well I dont want to just go changing everything & anything that comes
to mind, I would like to be more structured but I am not sure where to
start first, any help appreciated.
There is a lot of misunderstanding around the need to set DAO object
references to Nothing. The key thing is not that they need to be explicitly
set to Nothing ber se, but that they must be closed and released in reverse of
the order of dependency.
For instance, if I open a workspace, open a database, and open a recordset, I
need to close the recordset and release the reference, close the database and
release the reference, then close the workspace and release the reference.
On 16 Oct 2004 08:25:31 -0700, mo******@yahoo.co.uk (B Moor) wrote:
Host PC: Win SBS 2003 with 2.6 GHz processor and 1GB RAM
SQL Server 2000 sp3 (or latest).MS Office 2k3
I have the access 2k3 front end running on server (this may get
changed) and all was well until an upgrade 1 week ago when I installed
a new version of the mde (with very minor changes) and a new VB
application via a VB6 installer setup.exe.
Access uses odbc to linked tables on SQL Server, plus a few local
tables.
I also use DAO for all the recordsets I generate (and subsequently
close , set= nothing religiously).
This system has run quite stable for weeks but now, after the upgrade
and new VB app, I have had several resource type errors eg
Error 3035 System resource exceeded
Error ???? There isnt enough memory to perform this operation
+ general ODBC errors if above cleared
also
Error 3633 Cant load dll MSJTER40.DLL (ms jet error handler)
All is well when Access 1st run but after say 8 hours or so the Access
app grinds to a halt. The cpu process % is low,there is 400M RAM
available, other apps are ok but access is in a bad way, still
functioning but at approx 1/10 the speed or less.
Upon checking the task manager I noticed that after the 8 hours or so
I had mem usuage upto 160MB and handle count upto 86,000 !
Now when I start the access app the mem= 28M and the handle count =
260.
On 1 test after 6 hours this had gone upto 46M and 834.
But then on checking 2 hours later the handle count was jumoing up by
the 100 every few seconds.
This is related to frequent timer based queries and functions I run,
but why the dramatic change ?
If I stop/start access only, performance is not greatly improved, I
have to stop/start SQL Server as well.
I am wondering if I have some how corrupted mdac stuff with the VB
install as it installed older dlls :-
MSJTER35.DLL
MSJET35.DLL
dao350.dll
msado20.tlb
So how to fix ?
Well I dont want to just go changing everything & anything that comes
to mind, I would like to be more structured but I am not sure where to
start first, any help appreciated.
Tom van Stiphout <no*************@cox.net> wrote in message news:<96********************************@4ax.com>. ..On 16 Oct 2004 08:25:31 -0700, mo******@yahoo.co.uk (B Moor) wrote:
I have not seen the behavior you mention, and we have 2 Access apps
(Access 2000) running against SQL Server all the time, and they may
run for weeks doing some serious data manipulation without the system
being rebooted. So it *is* possible.
Some suggestions:
* Make sure you have the latest Jet service pack: Jet 4.0 SP8.
* Use PerfMon to narrow down where the resources are consumed. Perhaps
also use SQL Server Profiler.
* Standard debugging trick: simplify the problem. Try to isolate the
part that causes this behavior by eliminating steps.
* Not sure why you mention dao350; I think you should be using dao360.
-Tom.
Thanks Tom,
I will take your advice.
I am unclear, and have not found much info searching around, as to
what could cause excessive handle counts. I understand that a handle
will be used for any new object so ones that I create I must close,
set = nothing , which I do. So what does that leave, perhaps if i
Knew this i would know more about the problem.
Any ideas ?
I mention dao35 as it was included in the project build for a VB6
install although my VB6 uses ADO. During the install process I
selected "leave existing newer file", but I did not take note of which
files this was for. My intuitive (more like guess) feel is that I
have somehow got 2 different versions of dao/ado/odbc (=mdac ?) mixed
up causing odd results ?
regards,
Brian
这篇关于使用SQL Server 2000访问2k3：几个小时后会非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
