DBCC CHECKDB & READ ONLY DATABASES

DBCC CHECKDB & READ ONLY DATABASES:

Can you run a checkdb command on Read-only databases? If yes, does this get logged into boot page of the database? If checkdb finds an error can it fix on a read-only database?

These were some of the questions running through my mind as I manage some read-only databases in an SQL server which is mostly used for archiving and wanted to have the same level of checks and monitoring stuff we do an read write OLTP databases.

Can you run a checkdb command on read-only database? Is it logged in the boot page as it does for a read-write database?

Lets start by creating a sample database and call that readonly_check and run a checkdb on it. Once the checkdb is run on the read-only database we will use the dbcc page command to look into the dbcc last good status flag.

CREATE DATABASE READONLY_CHECK

GO

ALTER DATABASE READONLY_CHECK SET READ_ONLY

GO

DBCC CHECKDB (READONLY_CHECK)

GO

DBCC TRACEON (3604)

DBCC PAGE (6,1,9,3) WITH TABLERESULTS

I have managed to get the output from dbcc page and truncated the output to show only what is required for our discussion

This gives us a clear picture or proof that even after checkdb was run successfully this doesnt get into boot page as the database is in read-only state which is completely making sense. We at our environment use an individual DBA_admin database for each instance which tracks and stores all error logs, checkdb, backups run dates, performance counter values, DMV data and whole lot of other data used for troubleshooting or narrowing down any future issue in SQL server. I personally tend to use Sankars script http://sankarreddy.com/2010/03/elegant-solution-finding-last-clean-dbcc-checkdb-ran-date-quickly-for-all-databases-2/ for capturing checkdb run date, as this wont work or record correct time when dbcc checkdb is run for a read-only database its our (DBA) responsibility to get that date / time during run time and record using a custom process.

NOTE:

The last full backup time and last log backup time that is shown in the database properties page on SSMS is taking values from MSDB Backupset table, so whenever a backup is completed be it a read-only or read write database the correct date/time is shown in the page. Backups data / times shown in database properties doesnt come boot page of the database.

BACKUP DATABASE READONLY_CHECK TO DISK ='C:READONLY_CHECK.BAK'

GO

If checkdb finds an error can it fix on a read-only database?

Download a sample corrupt database from SQLskills.com website http://www.sqlskills.com/BLOGS/PAUL/post/Conference-corruption-demo-scripts-and-example-corrupt-databases.aspx and restore it on a test or dev SQL server. After having so I have got the database called DEMONCIndexon my instance.

ALTER DATABASE DemoNCIndex SET READ_ONLY

GO

DBCC CHECKDB (DemoNCIndex)

GO

-- You will see some errors with index 

Now lets run a repair statement with DBCC CHECK for this corrupt database. Remember you have to set the database in single_user mode for running a repair command

ALTER DATABASE DemoNCIndex SET SINGLE_USER

GO

DBCC CHECKDB (DemoNCIndex, REPAIR_ALLOW_DATA_LOSS)

GO

There we go we hit a error message as below and none of the repair command can be run with the read-only database.

Msg 2596, Level 16, State 1, Line 1
The repair statement was not processed. The database cannot be in read-only mode.

The only way to repair this error is to set the database to read write and then run a repair command.

Hope this article helps a bit more about maintenance tasks performed on a read-only database.