• slowness in Specify/SQL
• slowness in Specify/SQL
Posted by
mMayfield
at
2007-06-28 11:21 AM
We run SQL Server 2000 on a dedicated SQL database server that is essentially brand new.
While most of the time, we are running pretty smoothly, there are times when saving a record can take several minutes and the server seems to slow significantly in general (scrolling, etc). This tends to happen in the middle of the day from about 11 AM to 3 PM, but it speeds back up to normal at around 3:00PM daily. In response to my pleas for help our computer IT person here increased the memory allocated to SQL and it seemed to help temporarily. Why would this be? While looking at the data directory recently, I noticed that the Logfile (.ldf) is absolutely huge. The database comes in at 865mb but the log file is 7.5Gb. The other thing I noticed is that both the .mdf and the .ldf are timestamped to *3PM* on the previous day at any given time (envision lightbulb over head). Does this mean that SQL holds all database entry for a given period of time and saves the data and log files at the same time on a schedule? If so, while the server is trying to save this file to the disk, I can imagine it might cause a huge slowdown for a significant period of time. Has anyone else run into this problem? Do you think this is a possibility? If so, where would I start in trying to reduce the size of the log file? Perhaps our problem could be solved by changing the schedule of events to save the file in the middle of the night?
_________________________
Mark H. Mayfield Herbarium, Data Manager Kansas State University |
• Re: slowness in Specify/SQL
Posted by
mMayfield
at
2007-06-28 11:38 AM
Additional Info: I just noticed that the save date and time on the .mdf and .ldf are set at the time the database was last attached so that's probably a dead end. However, my question still remains for the forum. How should I tackle the slowness issue? it's really debilitating for data entry workers waiting on records to save and for scrolling to occur. We have only had this slowness problem for the last few months and it seems to be getting worse.
_________________________
Mark H. Mayfield Herbarium, Data Manager Kansas State University |
• Re: slowness in Specify/SQL
Posted by
kcoggins
at
2007-07-02 01:21 PM
Increasing memory enables the tables to be stored in memory and thus increases speed. Also huge log files can cause speed problems. I have copied below suggestions that I have found via google for large log files.
|
Manager
Posts:
7
|