Script upgrade may fail when Database snapshot is created on subscriber database
Consider a scenario where you are applying Service Pack or Cumulative Update for SQL Server instance and after which the SQL Server instance does not start. Below errors are logged in SQL Server errorlogs.
Error: 3906, Severity: 16, State: 1.
Failed to update database "DBSnapshot_20120512" because the database is read-only.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 3906, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
As the SP or CU scripts are trying to upgrade the snapshot database which is a read-only database the upgrade script was failing. Below workaround should help you with starting the SQL Server service.
Start the SQL Server service from command line using trace flag -T902 which will by-pass running upgrade scripts
Sqlservr.exe -s Instance_Name -T902 -T3608
From another command prompt connect to the SQL instance
Sqlcmd –S Server_Name\Instance_Name
Drop the snapshot database and stop the SQL Service which was started from command prompt.
Start the SQL Service normally and it should start fine and you should be able to see from SQL Errorlog that the upgrade scripts completed successfully.
The reason for failure was because the parent database for database snapshot was also a subscriber in replication setup.
Within the upgrade script procedure “master.sys.sp_vupgrade_replsecurity_metadata proc”, a cursor is being opened that walks through all user databases and for each database where HAS_DBACCESS()returns 1, we then check if an object with name MSsubscription_properties exists and if sys.columns contains an entry with name ‘job_step_uid’ and if both conditions are met, we call sys.sp_MSupgrade_subdb_security_metadata on the database. This procedure fails with error 3906 if the database is read only.
In this particular scenario the failure happens on a snapshot database which meets the two conditions.
Run the below script to identify any database snapshots that can cause this issue. If the script returns any results then drop those database snapshots before applying the Service Pack/ Cumulative Update
declare @name sysname, @has_problem bit, @stmt nvarchar(512)
declare @tab table (name sysname)
declare dbcur cursor local fast_forward for
select name from sys.databases
where database_id > 4 and has_dbaccess(name) = 1 and is_read_only = 1 and source_database_id is not null
fetch next from dbcur into @name
while @@fetch_status = 0
set @stmt = N'SET @has_problem = CASE WHEN EXISTS (SELECT * FROM ' + quotename(@name) +
N'.sys.objects WHERE name = N''MSsubscription_properties'') AND EXISTS (' +
N'SELECT * FROM ' + quotename(@name) + N'.sys.columns WHERE name = N''job_step_uid'') ' +
N'THEN 1 ELSE 0 END'
exec sp_executesql @stmt, N'@has_problem bit output', @has_problem output
if @has_problem = 1
insert @tab values (@name)
fetch next from dbcur into @name
select name as problem_databases from @tab