SQL is slow for the larger of two active DBs

Question

SQL is slow for the larger of two active DBs

Scott Schmidt

Grasshopper

Points: 22
More actions
June 6, 2006 at 9:14 am

#172746

I have two databases running on a SQL 2000 machine and the larger of the two, 4GB, is running very slow causing a web service to time out from time to time. I have noticed that the log files for both are the same size and are updated at the same time. Did something get setup wrong? Could it be possible that the both log files are indeed getting updated at the same time with the same information and this could be causing the problem?
Any help is greatly appreciated.
Scott

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

You could create jobs to run DBCC shrinkfile on both databases and SQL Server will return space less what it calls active log. You have to decide how many times the job will run after some tests. In situations like this you have to open the taskpad every morning to see the file growth and increase the file sizes manually and run some tests with DBCC statements. The taskpad is context sensitive so open a database before opening it. Run a search for DBCC Shrinkfile and DBCC Shrinkdatabase in SQL Server BOL (books online). Try the links below for more. Hope this helps.

http://msdn2.microsoft.com/en-US/library/ms189493.aspx

http://msdn2.microsoft.com/en-US/library/ms190488.aspx

kind regards,

Gift Peddie

Kind regards,
Gift Peddie

Mark Yelton SSCommitted Points: 1570 More actions · Answer 2

Are the .ldf files in the same directory folder as the .mdf files? If so, move the .ldf files to a different drive and folder.

Scott Schmidt Grasshopper Points: 22 More actions · Answer 3

The .ldf files are on different mapped drives on the same hard drive. I am currently running Index tuning on a trace file I created through SQL Profiler.

Thanks for the advise.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

Here's my 'short list' for tuning:

Round I

DBCC UPDATEUSAGE

UPDATE STATISTICS (with FULL scan) for all tables

exec sp_recompile for all tables

exec sp_refreshview for all views

Round II

DBCC DBREINDEX

UPDATE STATISTICS (with FULL scan) for all tables

exec sp_recompile for all tables

exec sp_refreshview for all views

Round III

Profiler

Query Hints

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."