Geek Answers Handbook

An expanding list of databases in SQL Server 2008 Management Studio takes longer than SQL Server 2005

Is it just me or does expanding the list of databases in SQL Server 2008 Management Studio take significantly longer than expanding the list of databases in SQL Server 2005 Management Studio?

If it's not just me, is there an explanation for this behavior? Whatever it does in the background, which makes it take longer, can we turn it off? Is this customizable?

I know this seems trivial, but I constantly wonder how long it will take.

+6
sql-server
source share
3 answers

In SQL Management Studio 2008, select the "Databases" node in Object Explorer. Then click View → Object Browser Information. Right-click on one of the column headers and uncheck all columns except "Name". These columns slow down the process of expanding the list of databases.

+19
source share

Most of the systems I worked on have 200 or fewer tables. It should be almost instant.

The only time I saw a slowdown was a system with a heavy load or a slow connection to the database server. In this case, it completely depends on factors beyond my control.

+1
source share

I had the same problem, database expansion was very slow at Mng Studio 2012 and 2014, but not for 2008. The problem was that for many databases, the AutoClose property was set to True

I used this script to search these databases

SELECT name, is_auto_close_on, is_auto_shrink_on FROM master.sys.databases AS dtb WHERE is_auto_close_on = 1 OR is_auto_shrink_on = 1 ORDER BY name

And then I set the AutoClose property to False in these databases.

From now on, node databases expand instantly

+1
source share

More articles:


All Articles