SQL Server Myth: Log files are removed when a database is made READ_ONLY


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:

image

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.

Advertisements

4 thoughts on “SQL Server Myth: Log files are removed when a database is made READ_ONLY

  1. hshripati

    Hi Nakul,

    The book appears to be incorrect. Changeing DB to Read_only or Read_write will not make logfile dissappear.

    When we set up T-log shipping we have the option to set DR db to Restore with Standby option. This option allow to restore the T-log backups also DB will be in Readonly mode.

    Think only DB snapshots will be created without the logfiles.

    Thanks,

    Shree

    Like

    Reply
  2. Patrick Lambin

    Hello,

    According to Kaleen Delaney in her book “Inside Microsoft SQL Server 2005 : The Storage Engine
    The READ_WRITE/READ_ONLY options describe the updatabilty of the database.In READ_ONLY mode, no INSERT,UPDATE or DELETE operations can be executed.;Because no modifications are done when a database is in READ_ONLY mode, automatic recovery is not run on this database when SQL Server is restarted and no locks need to be required during any SELECT operations, moreover shrinking the database is not possible”.
    So , i think there only only an error , “removed” should be replaced by “not used”.
    If any of yours is a 2011 MCC, i would suggest him to have a look at Safari ONLINE.He/she should be able to read the pdf of the same book for SQL Server 2008.

    Have a nice day

    Like

    Reply
  3. Pingback: #0370 – SQL Server – Myths – CREATE DATABASE FOR ATTACH_REBUILD_LOG will not work for read-only databases | SQLTwins by Nakul Vachhrajani

  4. Pingback: #0370 – SQL Server – Myths – CREATE DATABASE FOR ATTACH_REBUILD_LOG will not work for read-only databases - SQL Server - SQL Server - Toad World

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s