There is a rather esoteric setting in sql server called autoclose, which on desktop edition defaults to 'on'. This may lead to very slow response times in the SQL Enterprise Manager. The fix is not obvious. Once we toggled our dev server the increase in response time was marvellous.
The "autoclose" setting closes the connection to the db in question when the last user leaves it, which means that every time someone tries to access a db on the server in question, that has no users in it, it has to open a connection to that db (a time consuming activity). If there are a lot of dbs on the server, and you do something like refresh all of them, then you may well be in for a long wait.
Although, you might want this setting to be on, when using a true desktop, you don't on a desktop that poses as a server.
How you fix it!
AUTOCLOSE can't be accessed through the Enterprise Manager GUI, only via the stored procedure sp_dboption.
You can fix it by database if you like, by running following code in Query Analyser:
exec sp_dboption @dbname = <dbname>, @optname = 'autoclose', @optvalue = 'off''
If you want to be really clever, and run it on all databases on your server at once, by running the following code:
exec sp_msforeachdb @command1 = "print '?'",
@command2 = "sp_dboption @dbname = ?, @optname = 'autoclose', @optvalue = 'off'"
(Thanks Daniel!)
Posted by modius at 06:48 PM | Permalink
Trackback: http://blog.daemon.com.au/cgi-bin/dmblog/mt-tb.cgi/9
Just thought I'd mention the "sp_msforeachdb" is a non documented stored proc. That said, it comes very handly if you want to do do things like:
1. purging data from all your tables (great for development db's);
EXEC sp_msforeachdb @command1 = "print '?'", @command2 = "delete from ?"
2. Disable triggers;
EXEC sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
3. Run DBCC calls;
EXEC sp_MSforeachtable @command1="print '?' dbcc checktable ('?')"
Very handy little critter...
Posted by: John on November 8, 2002 06:06 PM
Thanks for that John, excellent tip!
Posted by: Geoff Bowers on November 8, 2002 06:20 PM