Databases are not shown in MyLittleAdmin on a Plesk for Windows server: Retrieving data

Symptoms

• MyLittleAdmin shows "Retrieving data.." instead of a list of databases. The following error message appears in Windows Event viewer:

  URL that was requested does not exist.
  The page isn't redirecting properly.

• SQL Server 2008 R2 or newer is installed.

Cause

The Auto Close option is enabled for databases.

Resolution

It is recommended to disable the 'AUTO_CLOSE' option if a database is used often on a production environment.

1. Connect to the Plesk server via RDP.

2. Start SQL Management Studio (if it is not installed, download and install it).

3. Run the following query:

   ALTER DATABASE model SET AUTO_CLOSE OFF

4. Run this script to disable AUTO_CLOSE on all databases:

   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

Additional Information

• Recommendations and guidelines for setting the AUTO_CLOSE database option in SQL Server