SQL Studio Management Expand Databases Slow

by Joe Payne 14. April 2014 10:53

So on my development PC, I’ve noticed that it takes 2-3 minutes for the Databases node to expand in my SQL Server Studio Management 11.0.2100.60

I don’t remember exactly when it started.   But it’s been several months I think.  It was just too easy to ignore since I could alt-tab and do something else while it worked through the delay.

Today I decided to research it and determined it’s because some databases have auto_close turned on.   This causes the SQL server to have to start up the database before it can render it in the Databases node.    This creates the significant delay when you have several databases configured for auto_close.

A quick way to fix all of them at once time AND set recovery mode = simple (anything more is useless in my dev environment), use this query:

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)
        select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
        select @isql = replace(@isql,'@dbname',@dbname)
        print @isql
        exec(@isql)
        select @isql='USE @dbname checkpoint'
        select @isql = replace(@isql,'@dbname',@dbname)
        print @isql
        exec(@isql)
        
        fetch next from c1 into @dbname
        end
    close c1
    deallocate c1

Tags: ,

Personal | Tech Support

Month List