SQL SERVER – Notes and Observations on ReadOnly Databases in SQL Server

In the past couple of weeks, I have written few blogs that revolve around utilizing “readonly” databases. It was fun working on this special case scenario that I had stumbled upon an interesting set of questions from my blog readers. These were not clearly called out in my previous blogs, hence thought of writing them down the responses I had given with examples in this blog. Some of the questions I have been asked were:

  • Can I create temporary tables or global temporary tables when working with ReadOnly databases?
  • Since the database is in ReadOnly mode, we cannot insert any values into the database. How about creation of tables when the DB is marked as ReadOnly?
  • Can we create stored procedures when working with ReadOnly Databases?
  • I have a large database and I see a lot of free space, can I shrink the DB when it is marked as ReadOnly? Is this allowed?

Some of these questions are interesting and require a small script to validate our understanding. A rule of thumb at this point is, this is a ReadOnly database and we need to know we cannot do anything with this database. Temp tables are created in the context of tempdb database and the readonly attribute doesn’t apply to objects created that way.

Next let me build the script to demystify each of the questions asked above:

CREATE DATABASE [ReadOnlyDB] CONTAINMENT = NONE
ON  PRIMARY
( NAME = N'ReadOnlyDB', FILENAME = N'C:\Temp\ReadOnlyDB.mdf' , SIZE = 4024KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ReadOnlyDB_log', FILENAME = N'C:\Temp\ReadOnlyDB_log.ldf' , SIZE = 20480KB , FILEGROWTH = 10%)
GO
USE MASTER
GO
ALTER DATABASE [ReadOnlyDB] SET READ_ONLY
GO

The basic script above is creating our database and then setting the attribute to ReadOnly. Let us start our various tests to validate our understanding.

USE ReadOnlyDB
GO
-- Creating our table
CREATE TABLE tbl_SQLAuth (id INT, Longname CHAR(8000))
GO

Solarwinds

As per our understanding, this must raise an error as shown below:

Msg 3906, Level 16, State 1, Line 15

Failed to update database “ReadOnlyDB” because the database is read-only.

Temp Tables: As discussed above, the below query in the ReadOnlyDB context willnot raise any error.

-- Creating our Temp Tables
CREATE TABLE #t1 (i INT)
GO
DROP TABLE #t1
GO
-- Creating our Global Temp Tables
CREATE TABLE ##t1 (i INT)
GO
DROP TABLE ##t1
GO

Stored procedure creation: If we try to create any objects in our ReadOnlyDB, we will get the same error of 3906.

— Create the stored procedure inside ReadOnlyDB

-- Create the stored procedure inside ReadOnlyDB
CREATE PROC prc1
AS
BEGIN
SELECT
1
END
GO

Msg 3906, Level 16, State 1, Procedure prc1, Line 30

Failed to update database “ReadOnlyDB” because the database is read-only.

Shrink Database command: Shrink database question was an interesting one for me. But on second look, this is logical. Since shrink database will try to alter the header in our mdf file, in the readonly mode that is not allowed. Hence we will get the error.

DBCC SHRINKDATABASE (ReadOnlyDB, TRUNCATEONLY);
GO

Msg 7992, Level 16, State 1, Line 4

Cannot shrink ‘read only’ database ‘ReadOnlyDB’.

All these errors are simple yet questions that come to many of us. This blog post is a dedication to those who took time in writing a line to me even after close to 7+ years of blogging. I get to learn from each one of you out there. SQL Server is an ocean and you make me complete.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Cloud Based Data Integration Made Easy – A Real World Scenario
Next Post
SQL SERVER – Finding Top Offenders in SQL Server 2012 – Notes from the Field #068

Related Posts

No results found

6 Comments. Leave new

  • Great Explanation,
    Thanks
    Karthik

    Reply
  • Hi,

    Although you can’t create a stored procedure, you can create a temporary stored procedure:

    CREATE PROC #prc1
    AS
    BEGIN
    SELECT 1
    END
    GO

    EXEC #prc1
    GO

    Thanks
    Ian

    Reply
    • Absolutely Ian. That is similar to temp tables as the context of the SP is in TempDB. A nice catch and addition. Thanks for the note.

      Reply
  • Nice article. I work at a place that uses readonly/standby databases for reporting. The issue I find most perplexing is how statistics are maintained/created/updated on readonly databases. Supposedly this is handled using tempdb and a special “flag”, per the documentation. I have found the actual implementation to be less reliable.

    This is a big problem because I assume that most folks who are using a readonly/standby for reporting have radically different workloads than that read/write OLTP system. The only reliable way I’ve found to get good stats for readonly workloads on a readonly/standby db is to actually run the queries on the OLTP system once and have those stats saved there, which eventually flow to the readonly/standby.

    This is less-than-optimal.

    I’m wondering if others have experienced this.

    Reply

Leave a Reply

Menu