moonshadow
Programmer
I have a server (twin processor 2GB RAM, 70GB hard disk) running SQL Server 7 & an instance of SQL2000. If I try and do any task on the SQL 2K instance via Enterprise Manager (either remotely or on the box), the response time can be measured in minutes. The SQL2000 instance has 58 databases, and by using SQL Profiler I can see that most of my tasks (e.g. backup/restore), result in running the following query a lot of times:
select name, DATABASEPROPERTY(name, N'IsDetached'), (case when DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else DATABASEPROPERTY(name, N'IsShutdown') end), DATABASEPROPERTY(name, N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'), DATABASEPROPERTY(name, N'IsInLoad'), (case when DATABASEPROPERTY(name, N'IsInRecovery') is null then -1 else DATABASEPROPERTY(name, N'IsInRecovery') end), (case when DATABASEPROPERTY(name, N'IsNotRecovered') is null then -1 else DATABASEPROPERTY(name, N'IsNotRecovered') end), DATABASEPROPERTY(name, N'IsEmergencyMode'), DATABASEPROPERTY(name, N'IsInStandBy'), has_dbaccess(name), status, category, status2 from master.dbo.sysdatabases
This is taking a minimum of 12 seconds to run. Additionally the memory on the machine pages like crazy (220 pages/sec), until Enterprise Manager responds to me. Anyone got any ideas why ? and how I can get it to be more responsive?
Many thanks
select name, DATABASEPROPERTY(name, N'IsDetached'), (case when DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else DATABASEPROPERTY(name, N'IsShutdown') end), DATABASEPROPERTY(name, N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'), DATABASEPROPERTY(name, N'IsInLoad'), (case when DATABASEPROPERTY(name, N'IsInRecovery') is null then -1 else DATABASEPROPERTY(name, N'IsInRecovery') end), (case when DATABASEPROPERTY(name, N'IsNotRecovered') is null then -1 else DATABASEPROPERTY(name, N'IsNotRecovered') end), DATABASEPROPERTY(name, N'IsEmergencyMode'), DATABASEPROPERTY(name, N'IsInStandBy'), has_dbaccess(name), status, category, status2 from master.dbo.sysdatabases
This is taking a minimum of 12 seconds to run. Additionally the memory on the machine pages like crazy (220 pages/sec), until Enterprise Manager responds to me. Anyone got any ideas why ? and how I can get it to be more responsive?
Many thanks