January 25, 2011 at 4:43 pm
Help!! Of course I need this NOW..so I am hoping to get some quick response!! 😛
I am trying to do a loop thru all databases, setting the READ_COMMITTED_SNAPSHOT ON. I have to set the database to SINGLE USER first...set the read and then set to MULTI_USER after. Here is my code, please tell me why it is erroring out, and I mean lots of errors!! LOL
I am not familiar with sp_msforeachdb, so bear with me!
USE [MASTER]
GO
--IF THE DATABASE IS NOT A SYSTEM DATABASE (1-4) or ReportServer (5) or ReportServerTempDB(6)
exec dbo.sp_msforeachdb
'USE [?]
IF DB_ID ''?'' > 6
BEGIN
ALTER DATABASE "?" SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE "?"
SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE "?" SET MULTI_USER;
END'
January 25, 2011 at 5:00 pm
From:
http://technet.microsoft.com/en-us/magazine/2007.02.sqlqa.aspx
Database Mirroring
Q I am using database mirroring and want to enable the READ_COMMITTED_SNAPSHOT database option. When I try to enable it after the mirroring has been set up, I get an exception stating that the db is in a mirroring session, and the command cannot be run.
A This happens because setting the READ_COMMITTED_SNAPSHOT option requires a database restart in order to take effect. Therefore, you need to break the mirroring session, set the option, and restart the database. After those steps are complete, you can reestablish mirroring. The mirror database will pick up the option after the session is established and will use it if failover happens.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply