EM running extremely slow ....
- hi All. A month ago our Enterprise Manager starting running really slow in the box; however in the desktops it run fine. Then we had to change our Domain so we recreated a new one and Enterprise Manager was running a normal speed again. A couple of days ago EM starting going slow again, DTS run very slow and also it takes a while to open each of the folder in EM. This is what we have done so far:
1. We made sure there was sufficient space for SQL Server to run.
2. We changed login accounts and DTS owners to match new domain.
3. We defragmented disks (C is the OS files it has about 4gbs of free space, and D is the sql server program files it has about 30 gbs of free space)
4. We restarted the box.
Can anyone give us any advise?
-
by slow you mean it takes a long time for EM to display the list of databases?
If that is true, then it's a known issue where if each database needs to be "opened" before EM can display it's results....and on a dev machine, a database 's AUTOCLOSE property is true by default...so if the server in EM you are trying to open has 100 databases, they are opened sequentially, and that can take a long time.
here's a cursor I use to set the AUTOCLOSE property on a dev machine to false, so that they never close if they are not used after a period of time.
USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1
GO
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
thank you for your quick reply, but we don't really have 100 databases , we have only 10.
-
hi one thing i remember was that i have sql mail before (not with exchange server) can maybe this be causing the slowness in EM?
-
you didn't explain what you meant by "slowness"... I still suspect the autoclose issue;
you can run this statement to see if anything is set to autoclose=true or not:
SELECT + [name] ,
'autoclose = ' + (CASE status & 1 WHEN 1 THEN 'True' ELSE 'False' END) AS AUTOCLOSE
FROM master.dbo.sysdatabases
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
thank you for all your help! ok so i run your query and all my databases come out as
autoclose = false
is this the way it should be?
another thing, in my sql server box, i tried logging in locally instead than to the domain and everything is working fast.. so maybe it has something to do with the domain that was changed? i recreated all the sql server logins and all users are conecting fine, also changed owners of the dts . is there anything else i have to do?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply