SQL SERVER – Error: Fix for Error Msg 3906 – Failed to update database because the database is read-only

Some error messages are a great source of troubleshooting when things go wrong. I always request my blog readers to read what the error messages are conveying. Most of the solutions are very much documented as part of the message. I must agree that over the years the error messages have become more descriptive in SQL Server.

On a similar note, recently one of the DB’s sent me a mail stating they were getting the below error message and they thought I had something on the blogs to help them solve the same.

Msg 3906, Level 16, State 1, Line 10

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

On careful search, I haven’t seem to have written about the resolution. When I looked at the error message, I thought it was quite explanatory and didn’t need any mention about solution. Your database is marked as ReadOnly and we are not able to write into them. So why the fuss, just mark your database as read-write.

After that incident, I thought of writing this blog to show how this can be solved with a simple code. Let us get into the script next. Let us create our database for testing.

CREATE DATABASE [ReaOnlyDB]
CONTAINMENT
= NONE
ON  PRIMARY
( NAME = N'ReaOnlyDB', FILENAME = N'C:\Temp\ReaOnlyDB.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ReaOnlyDB_log', FILENAME = N'C:\Temp\ReaOnlyDB_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)
GO
USE ReaOnlyDB
GO
CREATE TABLE tbl_SQLAuth (id INT)
GO

We have gone ahead and created the table which we will use for inserting later in the code. Let us mark the just created database as ReadOnly.

USE MASTER
GO
ALTER DATABASE [ReaOnlyDB] SET READ_ONLY
GO

With the above code successfully executed. Let us go ahead and try to insert some value in the table.

USE ReaOnlyDB
GO
INSERT INTO tbl_SQLAuth VALUES (1), (2)
GO

You will be greeted with the below error message inside SQL Server Management Studio.

Msg 3906, Level 16, State 1, Line 20

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

We have successfully replicated this error message. It is as easy as it gets. If you encounter this error message, we need to remove the ReadOnly attribute from our database. This can be achieved using the following TSQL:

USE [master]
GO
ALTER DATABASE [ReaOnlyDB] SET READ_WRITE WITH NO_WAIT
GO

As simple as it gets. Now the database is available for Write operations.

USE ReaOnlyDB
GO
INSERT INTO tbl_SQLAuth VALUES (1), (2)
GO

Now the insert statement would succeed without any errors. With the error message and resolution discussed. Let me give the cleanup script for this blog.

-- Clean up time
USE MASTER
GO
DROP DATABASE ReaOnlyDB
GO

As I wrap up this blog, would love to hear from you if you have every marked and used ReadOnly databases in your environments? What are the scenario’s you have used these options in databases? Or is this the first time you read about ReadOnly databases? Let me know.

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

SQL SERVER – How to fix the SQL Server Error 8657 or 8658?

It is sometimes very difficult to troubleshoot error which are relating to memory. In a recent query from one of my customer DBA, he sent me an error message which read like the ones below:

Error: 8657, Severity: 17, Could not get the memory grant of %I64d KB because it exceeds the maximum configuration limit in workload group ‘%ls’ (%ld) and resource pool ‘%ls’ (%ld). Contact the server administrator to increase the memory usage limit.

Error: 8658, Severity: 17, Cannot start the columnstore index build because it requires at least %I64d KB, while the maximum memory grant is limited to %I64d KB per query in workload group ‘%ls’ (%ld) and resource pool ‘%ls’ (%ld). Retry after modifying columnstore index to contain

The first instinct for me was to ask, hey you are sending me the template. Can you give me more information on the Resource Pool and other values? He said, he had not configured any Resource Governor on his server and was just trying to configure few columnstore indexes on a very large table when some of these errors were coming. Since he was part of a banking domain, he was unable to send me the exact values as per organizations security reasons. With very less information, I thought I need to dig more into these errors to understand why these happen. I was surprised, I had not written about this yet on this blog.

On quizzing few friends, I learnt that the default value for a query is limited to 25% of available memory on a server. This got me interested to find out how can this be viewed. The tip was part of the error message – check the resource governor settings.

SELECT request_max_memory_grant_percent,* FROM sys.dm_resource_governor_workload_groups
WHERE name = 'default'
GO

As you can see, the default value is 25% preconfigured and part of standard setup. Now to answer why the columnstore index creation was erroring.

It is possible for creation of a columnstore index to fail either at the very beginning of execution if it can’t get the necessary initial memory grant, or later during execution if supplemental grants can’t be obtained. If the initial grant fails, we will see error 8657 or 8658. We may get error 701 or 802 if memory runs out later during execution.

Resolution: If out-of-memory error 8657 or 8658 occur at the beginning of columnstore index creation, first, check the resource governor settings. The default setting for resource governor limits a query in the default pool to 25% of available memory even if the server is otherwise inactive. This is true even if we have not enabled resource governor. Consider changing the resource governor settings to allow the create index statement to access more memory in such cases. The TSQL would look like:

-- Increase the value of GRANT to 50% from default of 25%
ALTER WORKLOAD GROUP [DEFAULT] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 50)
GO
-- RECONFIGURE to make the setting take effect
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

I sent this script to my friend and told him to see the effect. I got a response last week that the query now ran without any problem and he was no longer getting the errors during index creation. Post this email conversation, I thought of uploading this script back into this blog for future reference.

I am curious to know, have you ever encountered 8657 or 8658 errors in your environments? What did you do to mitigate the errors? Would love to learn from you too.

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

SQL SERVER – FIX – Error: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device. Operating system error 3(The system cannot find the path specified.)

This blog is in continuation to using the default values. Earlier this week I wrote a blog which talked about the failure of Create Database Command.

While working on the reproduction of the issue, I changed the parameters and later realized that the backup database command was also failing. If you rely on the default values for folders that don’t exist, this will raise an error message as shown below. Here is the simple command which I have run:

BACKUP DATABASE MASTER TO DISK = 'master.bak'

The command was failing with below error.

Here is the text of the message shown in image:
Msg 3201, Level 16, State 1, Line 1

Cannot open backup device ‘E:\InvalidPath\master.bak’. Operating system error 3(The system cannot find the path specified.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

If you notice the error message is pretty clear. The backup is going to path “E:\InvalidPath” which is an invalid folder on my system. Important point to note here is that I have NOT given any file path in the command, I have just given file name master.bak.

The question is, from where the path is picked? If you have read my previous blog, it’s picked from “BackupDirectory” value in registry location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer

The highlighted portion might change based on SQL Version and Instance ID. For me it’s MSSQL12 because this is SQL Server 2014 instance. And my instance name is SQL2014:

SQL Version Folder Name
SQL Server 2008 MSSQL10
SQL Server 2008 R2 MSSQL10_50
SQL Server 2012 MSSQL11
SQL Server 2014 MSSQL12

For example, if you have SQL Server 2012 default instance, it would be MSSQL11.MSSQLServer.

Here is the registry value

We can change it via SQL Server Management Studio too. You can right click on server node and go to properties. Then choose “Database Settings” tab.

If you want to use T-SQL then here is the script.

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory', REG_SZ, N'E:\InvalidPath'
GO

NOTE: Once you have changed the value, SQL Service restart is needed so that SQL can pick-up changed values.

Now that we have seen a couple of examples where using the default values have got us into trouble. As readers, I would like to know if you have got into any errors because of using default values in your coding stints. Let me know via your comments.

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

SQL SERVER – Error: Fix: Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.) – Part 2

Errors are a great starting point for learning. Especially, my inbox is always flooded with atleast 2-3 error messages almost every other day. People keep asking me how to solve them, what to do when we encounter an error and so on. Long time back I wrote a simple blog which talks about error message 5133 while creating database. You can read it here:

SQL SERVER – Error : Fix : Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.)

One of the readers sent me email that she is simply running Create Database <DatabaseName> command and still get the above error. In my blog I mentioned “There must be some typo or error in filepath” but in this case there is no path given. This time I wanted to reproduce the error and was intrigued to why they were getting this error with the simple TSQL Create database command.

I spent some time researching and spoke to few friends about this. They informed that there is a setting in SQL Server which has default path of database files which is picked if nothing is specified. And it made complete sense. Based on this information,  I was finally able to reproduce the error by following his instructions. Here is the error:

Msg 5133, Level 16, State 1, Line 14
Directory lookup for the file “E:\InvalidPath\SQLAuth.mdf” failed with the operating system error 2(The system cannot find the file specified.).
Msg 1802, Level 16, State 1, Line 14
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

If you look at the path it is “E:\InvalidPath\SQLAuth.mdf”. The path is picked from the below registry value:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer

In my case, I have SQL 2014 named instance called SQL2014 – that’s why we are seeing MSSQL12.SQL2014

Here is the T-SQL way to get the values.

SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS [Default_Data_path]
SERVERPROPERTY('InstanceDefaultLogPath') AS  [Default_log_path]

We can change it via SQL Server Management Studio too. You can right click on server node and go to properties. Then choose “Database Settings” tab.

Following is the T-SQL way to achieve the change.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData', REG_SZ, N'E:\InvalidPath'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog', REG_SZ, N'E:\InvalidPath'
GO

NOTE: Once you have changed the value, SQL Service restart is needed so that SQL can pick-up changed values.

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

SQL SERVER – Error: Msg 701, Level 17, State 103. There is insufficient system memory in resource pool to run this query

Talking and exploring In-Memory topics inside SQL Server 2014 has been interesting to me. When I wrote the blog around table variable not being just an In-Memory structure, one of my course listener (SQL Server 2014 Administration New Features) pinged me on twitter to ask, if In-Memory OLTP was really In-Memory? Wouldn’t SQL Server like to swap the data or memory data to pagination file when there is memory pressure? I told them the concept of In-Memory is that data always resides in memory and the reason for feature name “In-Memory OLTP”.

The next question that came out of this interaction, what happens when we hit the memory boundary? Will SQL Server error out even if there is Memory available in the system or do something else. This was an easy setup to try.

Script Usage

We will create an In-Memory database, restrict it to have limited memory and add some rows to see if there is any error. The basic 4 steps to follow are:

-- Step 1: Create the Resource Pool. Limit to 5% memory.
CREATE RESOURCE POOL InMemory_Pool WITH (MAX_MEMORY_PERCENT = 5);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

-- Step 2: Create the InMemory DB
CREATE DATABASE InMemory
ON PRIMARY(NAME = InMemoryData,
FILENAME = 'd:\data\InMemoryData.mdf', size=100MB),
-- Memory Optimized Data
FILEGROUP [InMem_FG] CONTAINS MEMORY_OPTIMIZED_DATA(
NAME = [InMemory_InMem_dir],
FILENAME = 'd:\data\InMemory_InMem_dir')
LOG ON (name = [InMem_demo_log], Filename='d:\data\InMemory.ldf', size=50MB)
GO

-- Step 3: Bind the resource pool to DB
EXEC sp_xtp_bind_db_resource_pool 'InMemory', 'InMemory_Pool'
GO

-- Step 4: For RG to take effect, make DB Offline and Online
USE MASTER
GO
ALTER DATABASE InMemory SET OFFLINE
GO
ALTER DATABASE InMemory SET ONLINE
GO

After this we will create a wide table and add rows to exhaust the memory for this resource pool.

USE InMemory
GO
-- Step 5: Create a Memeory Optimized Table
CREATE TABLE DummyTable_Mem (ID INT NOT NULL,
Name CHAR(8000) NOT NULL
CONSTRAINT ID_Clust_DummyTable_Mem PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT=1000000))
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)
GO

-- Step 6: Add a lot of rows to get the error
SET NOCOUNT ON
DECLARE
@counter AS INT = 1
WHILE (@counter <= 1000000)
BEGIN
INSERT INTO
dbo.DummyTable_Mem VALUES(@counter, 'SQLAuthority')
SET @counter = @counter + 1
END
GO

The Step 6 will not complete because of insufficient memory. We will get an error as shown below:

The statement has been terminated.
Msg 701, Level 17, State 103, Line 49
There is insufficient system memory in resource pool 'InMemory_Pool' to run this query.

To add more rows to the table:

  • Make sure more memory is allocated to the resource pool and reconfigure the same.
  • Delete some rows from In-Memory tables on this database to make space for new allocations.

Let me help you clean up the script after this experiment.

-- Clean up
USE MASTER
GO
DROP DATABASE InMemory
GO
DROP RESOURCE POOL InMemory_Pool
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

To learn such interesting SQL Server 2014 enhancements, feel free to listen to my Pluralsight course for more such topics.

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

SQL SERVER – Identify and Filter In-Memory Optimized Tables – SQL in Sixty Seconds #079

Earlier I had written a blog about SQL SERVER – Beginning In-Memory OLTP with Sample Example which covers the basics of working with In-Memory OLTP. Though that post gets you started, one of my colleague asked me if there was an easier way to identify In-Memory Tables when working with SQL Server Management Studio. As a follow up I wrote another blog post over here where I demonstrate the same with images and query over here: SQL SERVER – Filter In-Memory OLTP Tables in SSMS.

I have converted the same blog post in SQL in Sixty Seconds video over here.

Let me know your opinion about it.

Here are few other blog posts related to this concept, which I have written earlier.

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

SQL SERVER – Schema Change Reports – SQL in Sixty Seconds #078

Earlier, I wrote a blog post about Schema Change Reports and Finding Tables Created Last Week – DBA Tip. I received quite a few emails about the same. It seems like a quite a popular topic. As per few requests I have created a very short 60 second video on this subject.

Let me know your opinion about it.

Here are few other blog posts related to this concepts, which I have written earlier.

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

SQL SERVER – Fix – Missing “Mirroring” and “Transaction Log Shipping” option in the Database Properties

If you are a SQL Server developer or DBA, the chances are you are quite familiar working with SQL Server Management Studio. The longer you have been in the industry, more likely are you to take these tools for granted. Sometimes, the tool or our basic mistakes in understanding the tool can baffle the best of the best.

So, how many times have you seen something unexpected in SQL Server or with SQL Server Management Studio? If you are seasonal DBA, you might say – “many times”. This blog would share one such experience which I also had. Recently I was trying to configure Database Mirroring on a freshly created database. I was surprised to see that database mirroring option was missing in database properties. If you right click on the System database (master, model, msdb, tempdb) the options of “Mirroring” and “Transaction Log Shipping” will not be available but for me it was a freshly created user database.

I was under the impression that the option might not be available for database in simple recovery model but that was not the case. Irrespective of recovery model, the option should be available. I connected to the same instance remotely and to my surprise the options were available.

Above troubleshooting proved that the issue was not due to SQL Server Engine installation, but something incorrect with client tools installation.

I looked further and launched discovery report from “SQL Server Installation Center”. This can be launched by clicking “Installed SQL Server features discovery report”.

Here is the same report from a server where this option was not visible.

And here is the report from working server.

To fix the issue, I have run the SQL installation again and Added below on the server which has the problem.

After that I was able to get the option in SSMS. There are other option which might not be available in “basic” version of SSMS. Balmukund has blogged about one of such missing option over here.

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

SQL SERVER – Fixing Backup Error – Operating system error 5(Access is denied.) – SQL in Sixty Seconds #077

This error has to be one of the most famous error. I have installed SQL Server quite a many times, but I keep on getting this error once in a while. This is also one of the most searched error online. I have previously blogged about this but today, I planned to create a small video on the same subject. In this video I demonstrate how one can resolve the permissions issue with this error.

ere are few other blog posts related to this error, which I have written earlier.

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

SQL SERVER -Fix Error – Cannot open backup device. Operating system error 5(Access is denied.)

This has to be the most popular error in taking database backups. There are many reasons for this error and there are different ways to resolve it. I recently faced this error and for a while I was taking backup and found a very interesting solution to the problem. The error was as mentioned below:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘d:\Log\aw.bak’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Personally, I know this error happens when

  1. There is no directory with Log
  2. There is not enough space on drive d:\
  3. There is not enough permissions on d:\Log drive

In my case all of the above were not the reason for above error. The error was there because of the file was read only. There was already another backup file which existed in the folder with the name aw.bak and it was marked as read only. Due to the same reason, my backup was failing. Once I removed the checkbox for reading only, the backup was successful.

Here are few other blog posts related to this error, which I have written earlier.

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