Skip to content

Specify Forums

Personal tools
You are here: Home » Discuss Specify! » Specify Forum » slowness in Specify/SQL

 • slowness in Specify/SQL

Document Actions
Replies: 2   Views: 5847
Up one level
You need to be a registered member to post to this forum.

 • 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
Member
Posts: 12

 • 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
Member
Posts: 12

 • 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.
Please note that this only seems to apply to SQL Server 2000. I have not seen this to be an issue for SQL Server 2005 Express or SQL Server 2005 Full version.
Please let me know if this helps!
Please note the instructions include both transaction commands and Enterprise Manager commands. You only need to do one. Most folks will do Enterprise Manager, but if you only have MSDE, then you will have to do the transaction commands.

Common cause of large transaction log file (.ldf)

Unfortunately the sql server default (except local editions) leaves the databases in full recovery mode.
This menas that if no action is taken no tr log entries will be freed and the log file will eventally fill the disk and crash the system.
The SQL Server installation process is very simple and commonly carried out by inexperienced personel. This will appear to work happily but cause problems later.
I would recommend always setting the model database to simple recovery mode to set the default for new databases.

Stopping the transaction log file (.ldf) from growing

If the log file has grown do to being in full recovery mode then set it to simple before going any further. This should immediately stop the log from growing.
Enterprise manager
Right click on the database, properties, Options, set model to simple, OK.
t-sql
sp_dboption [dbname], 'trunc. log on chkpt.', 'true'


Shrinking the transaction log file (.ldf)

Before this make sure there are free entries by setting the recovery model to simple or backing up the log.

Enterprise manager
Right click on the database, All tasks, Shrink database, Files, Select log file, OK.

t-sql
dbcc shrinkfile ([db_log_name])
Here [db_log_name] is the logical name of the log file as found from sp_helpdb or the table sysfiles


Shrinking the log file via detach/attach

Always take a full backup before a detach.

Detach the database, delete/rename the log file, attach the database - this will create a minimum size log file.
Note that the log file must be deleted/renamed otherwise it will be re-used even though it is not mentioned in the attach.

Enterprise manager
Right click on the database, All tasks, Detach database, OK.
Delete/rename the disk log file.
Right click on databases, All tasks, Attach database, Select the .mdf file, OK, Yes (to the create new log message).

t-sql
sp_detach_db [dbname]

Delete/rename the disk log file.
sp_attach_single_file_db [dbname], [filename]
where [filename] is the name of the physical data file (.mdf).

Manager
Posts: 7

 

Powered by Plone

This site conforms to the following standards: