EXECUTE sp_MSforeachdb
'
IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))
EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'')'
Now, this does not look that much similar to SQL, since it is T-SQL. But it will execute to disable auto_close for every database on your database server (that is, 'instance') and this will make
expanding databases in your database tree fast again in SSMS !
This is a variant of what Pinal Dave mentions here, I just made an iterative variant of what he suggests :
https://blog.sqlauthority.com/2016/09/22/sql-server-set-auto_close-database-option-off-better-performance/
Sunday, 4 September 2022
Faster expansion of databases in Sql Server Management Studio
I have observed a slow expansion of databases tree in Sql Server Management Studio. This can actually be fixed on many Sql Server instances by running this T-SQL :
No comments:
Post a Comment