Slow machine

[sguslan]

Have a fast Dell server with a fast >1G CPU and 1 GB mem for development.
One of the developers manage to get the transaction log full and ate up all HD space>45 GB. After cleaning the mess the server became very slow.
I defragged system and data drives then copied the data to a new database, dropped the old DB but still the response is dismal; even just looking at the properties of a DB or the server takes long to show up on the Enterprise Manager...

Any thoughts ...

 

[wilk]

How did you clean the transaction log.

The easiest way is to run a checkpoint on the database and then take the database offline, delete the transaction log altogether and then bring the database back online.

This will recreate the transaction log and hopefully the machine will be a bit faster.

but it is important to run checkpoints on a regular basis to keep the log from growing to that size again

 

[sguslan]

Thanx for the quick reponse>
I did checkpoint , take db offline and reacreate the transaction log but the DB is still slow...

 

[forecasting]

Have you checked the paging file size in the OS? It might not be a SQL Server problem but a Windows consequence.

 

[sguslan]

The paging file size is all ok. It is set to >1GB. The server it self is fast. It is only Enterprise Manager that is slow when trying to view/edit properties of an object, e.g. database.

 

[sguslan]

I checked the SQL activities when viewing the properties of the sql server and any database using SQL Profiler.

Interestingly, it seems to take a long time executing the following TSQL command:

declare @retval int
exec @retval=master.dbo.xp_MSADEnabled
IF (@retval=0) select 1 else select 0

The above command took a duration of 21500 and 0 CPU, while the other commands were all quick: duration < 220 .....

 

[sguslan]

Sorry, forgot to mention that this is a stand alone server and not part of any AD or Domain....