Hello Mates,
In
my last post at the very end I left out to readers to find out
the reason for the behavior. Some of them has come back with the right answer
Pseudo simple recovery Model.
Now
Let's see how to find out that & what is happening.
There
are 2 ways to figure it out one by running sys.database_recovery_status &
the other by making use of DBCC DBINFO. Make sure you turn on trace flag 3604 to get the output from this command.
Dbcc traceon(3604)
Coming
to my previous post there were 2 cases.
Case 1:
1) Database is in simple recovery model we changed it to
FULL
2) you thought not to disturb any of your differential backup strategies so you went with taking copy only full backup
2) you thought not to disturb any of your differential backup strategies so you went with taking copy only full backup
SELECT
name,
COALESCE(Convert(varchar(30),last_log_backup_lsn),
'No Full Backup Taken') as BackupLSN
FROM
sys.databases
INNER
JOIN sys.database_recovery_status
on
sys.databases.database_id = sys.database_recovery_status.database_id
if
you run the above command then we will see
The other way to find is run DBCC DBINFO under the database context and look for the field
dbi_dbbackupLSN & if you see the value like this = 0:0:0 (0x00000000:00000000:0000) then it means full backup has not run for the database.
case 2:
1 )DB is in Simple Recovery model
2) I made the database to read-only ensuring there shouldn't be any DML operations.
3)Changed the recovery model to full
4)Taken Full Backup
The same solution applies to above scenario also. However my question is even though the DB is in Read-only mode how does the recovery model got updated?
For this I ran few more tests one by changing the compatibility level of DB when DB is in READ-& the other to change the collation. Both of them as expected thrown the below Errors
Msg 3906, Level 16, State 1, Line 3
Failed to update database "test1" because the database is read-only.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed
Msg 5073, Level 16, State 1, Line 3
Cannot alter collation for database 'test12' because it is READONLY, OFFLINE, or marked SUSPECT.
Msg 5072, Level 16, State 1, Line 3
ALTER DATABASE failed. The default collation of database 'test12' cannot be set to SQL_Latin1_General_CP1_CS_AS.
Why didn't we get the same error when we change the recovery model?
Well I don't have answer for this. Firstly DBCC DBINFO in turn would run DBCC PAGE looking at page 9, file 1. As we know page 9 is the Boot page so I suspect there is nothing in boot page related to recovery model hence it was able to change metadata however when it comes to collation and compatibility it got failed as these fields can't be updated in Boot page(9) when DB is in read-only mode.
Here is the output for your reference:
DBINFO STRUCTURE:
DBINFO @0x000000EEA5D4D350
dbi_version = 869 dbi_createVersion = 869 dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_LastLogBackupTime = 1900-01-01 00:00:00.000
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_status = 0x40010008
dbi_crdate = 2019-02-12 19:22:44.680dbi_dbname = test12 dbi_dbid = 11
dbi_cmptlevel = 140 dbi_masterfixups = 0 dbi_maxDbTimestamp = 2000
dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000) dbi_RebuildLogs = 0
dbi_differentialBaseLSN = 36:157:74 (0x00000024:0000009d:004a) dbi_RestoreFlags = 0x0000
dbi_checkptLSN = 36:436:72 (0x00000024:000001b4:0048) dbi_dbccFlags = 2
dbi_COWLastLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_DirtyPageLSN = 36:436:72 (0x00000024:000001b4:0048) dbi_RecoveryFlags = 0x00000000
dbi_lastxact = 0x35c dbi_collation = 872468488 dbi_relstat = 0x61000000
dbi_PartitionDbFlags = 0 dbi_familyGUID = 9ddd6e10-0287-481b-9b70-c062033398e4
dbi_maxLogSpaceUsed = 575488
dbi_recoveryForkNameStack
entry 0
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = 9ddd6e10-0287-481b-9b70-c062033398e4
entry 1
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = 00000000-0000-0000-0000-000000000000
dbi_differentialBaseGuid = f9d075ce-3528-4ab5-b7d7-8c44970ad338 dbi_firstSysIndexes = 0001:00000014
dbi_oldestBackupXactLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_versionChangeLSN = 0:0:0 (0x00000000:00000000:0000) dbi_mdUpgStat = 0x0004
dbi_category = 0x0000000000000400 dbi_safetySequence = 0
dbi_dbMirrorId = 00000000-0000-0000-0000-000000000000
dbi_pageUndoLsn = 0:0:0 (0x00000000:00000000:0000) dbi_pageUndoState = 0
dbi_disabledSequence = 0 dbi_dbmRedoLsn = 0:0:0 (0x00000000:00000000:0000)
dbi_dbmOldestXactLsn = 0:0:0 (0x00000000:00000000:0000) dbi_CloneCpuCount = 0
dbi_CloneMemorySize = 0 dbi_updSysCatalog = 1900-01-01 00:00:00.000
dbi_LogBackupChainOrigin = 36:157:74 (0x00000024:0000009d:004a)
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000 dbi_roleSequence = 0
dbi_dbmHardenedLsn = 0:0:0 (0x00000000:00000000:0000) dbi_localState = 0
dbi_safety = 0 dbi_modDate = 2019-02-12 19:22:44.680
dbi_verRDB = 234882024 dbi_delayedDurabilityOption = 0
dbi_svcBrokerGUID = 19ec0cb6-4620-4618-9f89-33c1ec945770 dbi_svcBrokerOptions = 0x00000000
dbi_dbmLogZeroOutstanding = 0 dbi_dbmLastGoodRoleSequence = 0 dbi_dbmRedoQueue = 0
dbi_dbmRedoQueueType = 0 dbi_rmidRegistryValueDeleted = 0 dbi_dbmConnectionTimeout = 0
dbi_AuIdNext = 1099511627953 dbi_MinSkipLsn = 0:0:0 (0x00000000:00000000:0000)
dbi_commitTsOfcheckptLSN = 0 dbi_dbEmptyVersionState = 0 dbi_CurrentGeneration = 0
dbi_EncryptionHistory
Scan 0
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
EncryptionScanInfo:ScanId = 0
Scan 1
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
EncryptionScanInfo:ScanId = 0
Scan 2
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
EncryptionScanInfo:ScanId = 0
dbi_latestVersioningUpgradeLSN = 18:60:69 (0x00000012:0000003c:0045)
dbi_PendingRestoreOutcomesId = 00000000-0000-0000-0000-000000000000 dbi_ContainmentState = 0
dbi_hkRecoveryLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_hkLogTruncationLSN = 0:0:0 (0x00000000:00000000:0000) dbi_hkCompatibilityMode = 0
dbi_hkRootFile = 00000000-0000-0000-0000-000000000000 dbi_hkRootFileWatermark = 0
dbi_hkTrimLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_hkUpgradeLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_hkUndeployLSN = 0:0:0 (0x00000000:00000000:0000) dbi_heapPvsRowsetId = 0
I sincerely request to share your thoughts(apart from the BOOT page) on how the system tables are getting updated for the recovery model when the DB is in READ ONLY STATE.
Credits:
https://www.mssqltips.com/sqlservertip/2974/check-if-a-sql-server-database-is-in-pseudosimple-recovery-model-using-windows-powershell/
https://sqlfascination.com/2009/12/13/how-can-you-tell-if-a-database-is-in-pseudo-fullbulk-logged-mode/
Comments