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.