April 7, 2009 at 3:13 pm
Is there a way I can set AUTOCLOSE OFF for all the databases on a server.I cannot run ALTER DATABASE ['dbname'] SET AUTO_CLOSE OFF WITH NO_WAIT
on all the databases.There are more than 20 databases and there are numerous instances where I need to run these on.
thanks in adavance
April 7, 2009 at 3:16 pm
Try the undocumented sp_MSforeachdb
More details at:
http://www.databasejournal.com/features/mssql/article.php/3441031
.
April 7, 2009 at 3:31 pm
I tried using running and I get errors
sp_MSforeachdb
'
ALTER DATABASE [''?''] SET AUTO_CLOSE OFF WITH NO_WAIT
'
Msg 5011, Level 14, State 5, Line 3
User does not have permission to alter database ''master'' or the database does not exist.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 3
User does not have permission to alter database ''tempdb'' or the database does not exist.
Msg 5069, Level 16, State 1, Line 3
This happens for all the databases.I logged in using a sysadmin windows login and also the sa account but it still errors.
April 7, 2009 at 3:35 pm
OK, I have never tried it for this specific case so hope you can use it as you require after some further research. Good luck with your investigations.
.
April 7, 2009 at 3:42 pm
Hi
Just remove the doubled "'" within the brackets:
sp_MSforeachdb
'
ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT
'
To avoid any errors you have to exclude at least the master and tempdb. I would suggest to exclude all MS databases. Unfortunately it seems that the parser first detects the ALTER ... master before executing and throws an error for master and tempdb if you handle with a usual IF clause. So you have to execute your ALTER statement as an additional dynamic SQL:
EXECUTE sp_MSforeachdb
'
IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))
EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'')
'
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 7, 2009 at 3:49 pm
thank you Florian .Your code worked great .
April 7, 2009 at 3:51 pm
You're very welcome!
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 7, 2009 at 3:54 pm
i typically use this cursor to do the same thing...besides autoclose, i don't need to have the recovery at FULL on my developer machine:
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
Lowell
April 8, 2009 at 8:37 am
Hi Lowell and Flo
Now we have two solutions to the same problem. One with MS Undocumented feature another one is using cursor. Which one should be used !!! from a best practicses point of view. And also why MS doesn't expose Undocumented Stored procedures!!
April 8, 2009 at 8:41 am
thank you lowell.
April 8, 2009 at 8:42 am
From a perfomance prespective I would want to avoid using cursors.What do you guys think is the best approach?
April 8, 2009 at 9:34 am
sp_MsForEachdb still uses a cursor behind the scenes;
using a cursor is not necessarily a bad thing...that's why they exist, because some things cannot be done without RBAR processing;
the issue with cursors is really related to people using a cursor to emulate the way they might do something programmatically, and unintentially miss out on the performance gains that SET based operations from SQL server can do.
There's more than one way to do this; Florian and I both get the job done. A third way is to concatenate all the commands into one big varchar(max), and then execute that big string.
sp_MsForEachdb is not going to be dropped, i think it's just a CYA thing from microsoft so they can say, oh well, that's undocumented and unsupported, you gotta pay extra and we are not responsible for anything you did using it.
there is no significant performance impact in this case...each command will be run individually, whether in a cursor, or building a big string with all the commands and executing that.
It's great that everyone realizes that you should avoid cursors when possible...but this is one of those that doesn't have a performance problem.
Lowell
April 8, 2009 at 11:30 am
thank you Lowell for the info.I did not know the sp_foreach db is using cursors in the background.You are right Since I am using this code only once to turn off the settings and not using cursors witin a proc that is being regularly used it does not matter.
thanks again
May 26, 2009 at 11:26 pm
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply