Today, I attempt to bust a myth related to transaction log files for read-only databases in Microsoft SQL Server. Read-only databases, as the name suggests are databases containing static data. Quite often, applications use read-only copies of 3rd party data, restored in the data-center as read-only databases. For instance, there are services that provide a universal lookup for ISO codes for currencies & countries. Countries and currency codes do not change daily, and it makes great sense to convert the databases to read-only databases.
Just recently, I read in a book something that jolted me because it was an authoritative book. The sentence goes something like:
“When a database is placed in READ_ONLY mode, SQL Server removes any transaction log file that is specified for the database.”
The above statement is incorrect. What is correct is that the transaction log continues to exist – irrespective of whether or not the database is in READ_ONLY mode.
When in doubt, I always believe that a simple test should be done to confirm. So, here’s a very simple, do-it-yourself test that you can run on your test/development server:
--Step 01: Create a test database & confirm that it is not READ_ONLY USE master GO CREATE DATABASE ReadOnlyTest GO SELECT sdb.is_read_only, sdb.* FROM sys.databases sdb WHERE sdb.name = 'ReadOnlyTest' --Step 02: Confirm that the database has 2 files - one primary data file, one log file USE ReadOnlyTest GO SELECT 'Before',* FROM sys.sysfiles GO --Step 03: Make Database READ_ONLY and confirm USE master GO ALTER DATABASE ReadOnlyTest SET READ_ONLY GO SELECT sdb.is_read_only, sdb.* FROM sys.databases sdb WHERE sdb.name = 'ReadOnlyTest' --Step 04: Confirm that the database still has 2 files - one primary data file, one log file USE ReadOnlyTest GO SELECT 'After',* FROM sys.sysfiles GO --Step 05: Finally drop the test database as cleanup USE master GO DROP DATABASE ReadOnlyTest GO
Here’s what you would see:
What we know for sure is that the log files are not “removed”. Using the undocumented DBCC commands like DBCC LOGINFO and DBCC LOG, I could determine to a fair level of confidence that the log file is not used for some common read-only operations (SELECT, BACKUP).
Do you know why the transaction log file is required for READ_ONLY databases? Do let me know. I look forward eagerly to your response.
Until we meet next time,
Be courteous. Drive responsibly.