SQL SERVER – Backup on mapped drive failing with error – Msg 3201, Level 16, State 1 – Operating system error 3(The system cannot find the path specified.)

We are so used to taking backups and it can sometimes give us new experiences. One of the common messages I see are around taking backups on a share. If you have found this blog while searching for error message then you may want to look at below blogs as well if you are NOT dealing with mapped drive.

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

This time I will explain why you are getting this error while taking backup on a mapped drive. Here is the situation:

  1. SQL Server Service running under domain account.
  2. You have already mapped the drive in Windows and can see that drive in Windows Explorer.
    bkp map drive 01 SQL SERVER   Backup on mapped drive failing with error   Msg 3201, Level 16, State 1   Operating system error 3(The system cannot find the path specified.)
  3. You are not able to see the drive which is mapped. Below is the screenshot of screen where you choose backup file name. If you try to take backup from SQL Server Management Studio (SSMS) you are not able to see the drive. As we can see, SSMS is only showing local drive (C and D)
    bkp map drive 02 SQL SERVER   Backup on mapped drive failing with error   Msg 3201, Level 16, State 1   Operating system error 3(The system cannot find the path specified.)
  1. Backup command is failing with below error.

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘Z:\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.

bkp map drive 03 SQL SERVER   Backup on mapped drive failing with error   Msg 3201, Level 16, State 1   Operating system error 3(The system cannot find the path specified.)

If we try to get details about the drives visible from SQL Server by Windows “dir” command then we see below.

xp_cmdshell 'dir Z:'

output
———————————————–
The system cannot find the path specified.
NULL
(2 row(s) affected)

Above confirms that SQL is not able to see Z drive, even though it is mapped on Windows. In case you get below error

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 72
The configuration option ‘xp_cmdshell’ does not exist, or it may be an advanced option.

Then you need to execute below to enable xp_cmdshell as its disabled by default due to security reasons. (Please turn off again once you done with the work)

EXEC sp_configure 'advanced', 1
RECONFIGURE WITH override
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH override
GO

We are seeing “The system cannot find the path specified.” because SQL Server instance as it is running as a service. To fix the error, we need to map the drive via SQL Server. Here are the steps.

-- Map the drive via T-SQL so that SQL can see it.
EXEC xp_cmdshell 'net use Z: \\BigPinal\SharedFolder'
GO

bkp map drive 04 SQL SERVER   Backup on mapped drive failing with error   Msg 3201, Level 16, State 1   Operating system error 3(The system cannot find the path specified.)

Now we can check mapped drive via T-SQL.

xp_cmdshell 'dir Z:'

bkp map drive 05 SQL SERVER   Backup on mapped drive failing with error   Msg 3201, Level 16, State 1   Operating system error 3(The system cannot find the path specified.)

You may want to add more parameters for net use. Refer Net use documentation

Once this is done, we can see that drive in SSMS as shown below

bkp map drive 06 SQL SERVER   Backup on mapped drive failing with error   Msg 3201, Level 16, State 1   Operating system error 3(The system cannot find the path specified.)

… and as expected, backup command would not fail.

Have you ever faced this situation such weird situations? What were your resolution steps? Let me know as we can learn from each other.

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

SQL SERVER – Error – Msg 6401, Level 16: Cannot roll back Transaction

In a recent Usergroup meet that I attended someone asked me this simple yet interesting question about how transactions work and is there any concept like nested transactions inside SQL Server. Though I have talked about this in the past, thought it would be worth revisiting this concept to drive home some learnings again to readers who are new to SQL Server. Let us learn this using the script code as shown below:

-- Create our database for testing
USE MASTER;
GO
CREATE DATABASE TransactionsDemo;
GO
USE TransactionsDemo;
GO
-- Create a table for testing
CREATE TABLE tbl_SQLAuth (Col1 INT);
GO
SET NOCOUNT ON;
GO

Once the database is created, our next logical step is to create some transactions to test if we can mimic the use of nested transactions.

-- Create an explicit transaction
BEGIN TRAN OuterTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (1);
GO 10
-- Create an explicit so called nested transaction
BEGIN TRAN InnerTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (2);
GO 10
-- Look at the trancount?
SELECT @@TRANCOUNT;
GO

In the example above we started using an explicit transaction called as “OuterTransaction” and then inserted values post which we created yet another transaction called as “InnerTransaction”. The @@TRANCOUNT will show a value of 2 now. Now that we know the transaction count is 2, let us try to rollback the inner transaction alone.

-- Rollback the inner transaction
ROLLBACK TRAN InnerTransaction;
GO

Msg 6401, Level 16, State 1, Line 33
Cannot roll back InnerTransaction. No transaction or savepoint of that name was found.
Now this is interesting to see the error, we have explicitly created a transaction called as “InnerTransaction” in our code and it is getting ignored.

-- Works?
ROLLBACK TRAN OuterTransaction;
GO

Or the below command works:

-- Works?
ROLLBACK TRAN;
GO

And it is so strange that this above commands worked. If you try to query the table, it will have no rows.

-- What hapenned?
SELECT COUNT(*) FROM tbl_SQLAuth;

This is an important learning one needs to know when working with ROLLBACK command inside SQL Server. There is nothing like a nested transaction perse inside SQL Server. Go ahead and do the clean up.

-- Cleanup time
USE MASTER;
GO
DROP DATABASE TransactionsDemo;
GO

I am curious to know from developers out there, have you ever faced this situation in your environment and learnt it the hard way? If you can share your learning, it would be great.

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

SQL SERVER – FIX – A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running. (Microsoft.AnalysisServices.AdomdClient)

Life gives you learning lessons from unexpected times, here is one such experience that I felt is worth sharing. It may be a surprise to my blog readers that I also have SQL Server Analysis Services installed on my primary machine. I use it occasionally because most of the time I work with the SQL Server Database engine. One fine day, I opened SQL Server Management Studio and instead of database engine, I selected analysis services (this was a mistake) and pressed on the connect button. Below is the error which I received:

TITLE: Connect to Server
——————————
Cannot connect to BIGPINAL
——————————
ADDITIONAL INFORMATION:
A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running. (Microsoft.AnalysisServices.AdomdClient)
——————————
No connection could be made because the target machine actively refused it 127.0.0.1:2382 (System)
——————————
BUTTONS:
OK
——————————

This is the first time I saw such error while connecting to SQL Server, but soon I realized that I was trying to connect to SQL Server Analysis Services. I could have left it there because I don’t use it, but I did not. Since there was a little time, I thought it is worth solving this challenge in front of me. Here is the step by step resolution of what I did:

  1. Made sure that SQL Server Analysis Service was up and running. This got confirmed from my SQL Server Configuration Manager.
  2. As I do always, read the error message again and verified that the SQL Browser service was up and running.
  3. Since this was a local connection, I turned off the firewall as well.

None of the above solved the problem. The Port resolution is done by the SQL browser service in case of SQL Server connectivity. So I thought that it could be a permission issues – just a guess based on the error message.

I noticed that SQL Browser was running with the local system account. I found on internet that changing account helped many. So I used SQL Server Configuration Manager to change service account for the SQL Server Browser. I have used like below.

browser ssas 01 SQL SERVER   FIX   A connection cannot be made to redirector. Ensure that SQL Browser service is running. (Microsoft.AnalysisServices.AdomdClient)

Here are the steps:

  • Open SQL Server Configuration Manager
  • Right click “SQL Server Browser” and choose “Properties”
  • In the “Log On” Tab select “Local System” under “Built-In” account.

Have you ever seen such error and found some other way to fix it? Please comment and let others also know. The best way to learn something is by sharing.

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

SQL SERVER – FIX: SQL Server Not Starting – Msg 864, Level 16, State 1- Buffer Pool Extension Feature

Recently I got an email from one of the student who attended my SQL Server 2014 Pluralsight Course from last year, he was trying to use Buffer Pool Extension feature and got into some unique trouble. Here is the email from him:

Hi Pinal,
I watched your course about SQL Server 2014 administration new features on PluralSight. It is really a great course for someone like me who is trying to do fast ramp-up on SQL Server 2014.

I was trying to experiment with buffer pool extension feature. I have enabled the buffer pool extension feature on SQL Server Standard Edition and now I am not able to start my SQL Server service. Could you please help me out? This is not a business down situation, but want to understand what went wrong.

Regards,

(Name Hidden)

The very first thing which I asked him was to provide the error log information. Whenever there is a problem with the SQL startup, I always start looking at the Errorlog. Here is what I saw in the SQL Server Error Log.

2015-02-28 00:39:11.63 spid8s      Starting up database 'master'.

2015-02-28 00:39:11.84 spid8s      Attempting to allocate 6560086 BUF for buffer pool extension for a maximum of 6560085 page descriptors.
2015-02-28 00:39:11.84 spid8s      Error: 864, Severity: 16, State: 1.
2015-02-28 00:39:11.84 spid8s      Attempting to allocate 6560086 BUF for buffer pool extension for a maximum of 6560085 page descriptors.

Above ERRORLOG explains that, SQL server is not able to allocate the required buffers into the buffer pool extension file. If you look closely at both numbers, the difference of one buffer. This would mean that the size of buffer pool extension was more than the maximum allowed size. I looked into the documentation about the size limitation and found below link on MSDN.

https://msdn.microsoft.com/en-us/library/dn133176.aspx

(The buffer pool extension size can be up to 32 times the value of max_server_memory for Enterprise editions, and up to 4 times for Standard edition)

I went back and looked into the error log and on the top found below.

Microsoft SQL Server 2014 - 12.0.2430.0 (X64)
Oct 15 2014 16:05:37
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

So it means that the size given for BPE file was going beyond the expected limit of 4 times of max server memory because of standard edition.

So now, we know why the error is occurring and we are unable to start SQL Server. To fix the error we need to start SQL Server with minimum configuration so the BPE is not initialized. We can use startup parameter f. Here are the detailed steps.

  1. Open command prompt. Use “Run As Administrator”, if applicable.
  2. Type net start MSSQLServer /f /mSQLCMD

Since my machine has default instance I am using MSSQLServer. For named instance it would be MSSQL$<InstanceName>

We have given additional parameter called “m” and passed SQLCMD to let SQL Server know that only SQLCMD can connect.

BPE 01 SQL SERVER   FIX: SQL Server Not Starting   Msg 864, Level 16, State 1  Buffer Pool Extension Feature

  1. Once SQL is started, connect to SQL via SQLCMD using below on command prompt.
SQLCMD -S(local) -E

Note that you need to provide your own server name. In above example I am connecting to default instance of SQL.

  1. Once connection is made, we need to disable BPE by using below command
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF

BPE 02 SQL SERVER   FIX: SQL Server Not Starting   Msg 864, Level 16, State 1  Buffer Pool Extension Feature

  1. Once it’s turned off, we can stop SQL by net stop command as below
Net Stop MSSQLServer

BPE 03 SQL SERVER   FIX: SQL Server Not Starting   Msg 864, Level 16, State 1  Buffer Pool Extension Feature

  1. Now we can start SQL normally and it should work (unless we have some other problem)

To dig further, I have taken a Virtual Machine of SQL Server Standard Edition in Microsoft Azure Cloud. My machine had below configuration:

RAM = 14 GB
Max Server Memory = 12 GB
D drive is SSD for BPE file. Over there I created folder SQLAuthority_BPE

Here are the steps to reproduce the error in SQL Server Standard Edition.

SP_CONFIGURE 'MAX SERVER MEMORY', 12000
GO
RECONFIGURE WITH OVERRIDE
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON (FILENAME = 'D:\SQLAuthority_BPE\BUFFERPOOLEXT.BPE' ,SIZE = 50 GB)
GO
SELECT * FROM SYS.DM_OS_BUFFER_POOL_EXTENSION_CONFIGURATION
GO
SHUTDOWN WITH NOWAIT

Important: Above script would also shutdown SQL Server because I have added shutdown T-SQL command.

Once it was done, I was not able to start SQL without following the steps which I mentioned earlier. After fixing the problem, I configured BPE with 48 GB is size and it worked fine. Here is the message which we can see in ERRORLOG

2015-02-28 09:57:42.110 spid8s       Buffer pool extension "D:\BPE\BUFFERPOOLEXT.BPE" has been initialized successfully with size is 49152 MB.

Hope this would help in understanding the limitation and fixing the error which I have not seen so far.

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

SQL SERVER – FIX – Error: 905, Severity: 21, State: 1 – Database ‘xxx’ cannot be started in this edition of SQL Server because it contains a partition function ‘xxx’.

Recently one of my blog reader contacted me via mail and told that after upgrading from Enterprise Evaluation Edition to Standard Edition, the database was not accessible. To troubleshoot, I have asked them to share SQL Server ERRORLOG. There are various ways to read Errorlog. Here is the method to read Errorlog:

  • In Object Explorer, expand a server, expand Management, and then expand SQL Server Logs.
  • Right-click a log and click View SQL Server Log.

Other ways to get Errorlog are explained in detail by Balmukund (b|t) on his blog (Help : Where is SQL Server ErrorLog?)

In the Errorlog, I asked him to look for any error related to the database which is not accessible and soon they shared below messages:

2015-02-20 13:47:36.65 spid7s      Error: 905, Severity: 21, State: 1.
2015-02-20 13:47:36.65 spid7s      Database ‘My_Database’ cannot be started in this edition of SQL Server because it contains a partition function ‘myRangePF1′. Only Enterprise edition of SQL Server supports partitioning.
2015-02-20 13:47:36.65 spid7s      Error: 933, Severity: 21, State: 1.
2015-02-20 13:47:36.65 spid7s      Database ‘My_Database’ cannot be started because some of the database functionality is not available in the current edition of SQL Server.

Explanation

From the error we can see that the database is using partition function which is enterprise only feature. Here is the link which has edition comparison.  The database contains one or more partitioned tables or indexes. Standard edition of SQL Server cannot use partitioning. Therefore, the database cannot be started correctly. Partitioned tables and indexes are not available in every edition of Microsoft SQL Server. Books online topic “Features Supported by the Editions of SQL Server 2012” has complete list of feature.

Solution

I asked him to restore the database MDF and LDF files to another instance which is either developer, enterprise or enterprise evaluation edition. Then get rid of the features which are enterprise only feature. They can easily be found using the DMV sys.dm_db_persisted_sku_features

USE <DatabsaeName> -- Change the name to the database where we need to check
GO
SELECT *
FROM sys.dm_db_persisted_sku_features

Note that the query has to be run in the database which is under question, not the master database. Here are the possible values on SQL Server 2014.

  • TransparentDataEncryption
  • Compression
  • ChangeCapture
  • ColumnStoreIndex
  • InMemoryOLTP
  • Partitioning

Once the object referring to that feature is removed, the backup can be taken and restored on “lower” edition of SQL Server.

Here is another error which can be received while restoring database using compression

Msg 909, Level 21, State 1, Line 1
Database ‘SqlAuthority’ cannot be started in this edition of SQL Server because part or all of object ‘PageCompressionTest’ is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

I always advice to run the DMV and check the features whenever there is a downgrade of edition. Even though evaluation to standard is an upgrade but I would call it as downgrade because there are few feature of enterprise editions would not be available in standard edition.

Have you ever encountered any downgrade scenarios and got some errors?

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

SQL SERVER – Installation Error – INSTALLSHAREDDIR parameter is not valid because this directory is compressed or is in a compressed directory

One of my blog reader contacted me for assistance. Here is his email:

Hello Pinal,
I am trying to install SQL Server on my laptop but getting below errors:

  • SqlServer.Configuration.Sco.DirectoryAttributesMissmatch: Folder C:\Program Files\Microsoft SQL Server has an unsupported attribute (Compressed) set. Please resolve this issue by removing the unsupported attribute from the folder using folder properties dialog.
  • SqlServer.Configuration.SetupExtension.CompressedDirException: The specified directory, “C:\Program Files\Microsoft SQL Server\”, for the INSTALLSHAREDDIR parameter is not valid because this directory is compressed or is in a compressed directory. Specify a directory that is not compressed.
  • SqlServer.Configuration.SetupExtension.CompressedDirException: The specified directory, “C:\Program Files (x86)\Microsoft SQL Server\”, for the INSTALLSHAREDWOWDIR parameter is not valid because this directory is compressed or is in a compressed directory. Specify a directory that is not compressed.

Can you help me in understanding the error and the fix of the issue?

Since the error messages were talking about compression so I checked MSDN to find whether SQL can be installed on compressed directory or not. And I found https://msdn.microsoft.com/en-us/library/ms143506.aspx (Hardware and Software Requirements for Installing SQL Server). This says “SQL Server Setup will block installations on read-only, mapped, or compressed drives”

Having said all this, in this era of faster disks and infinite storage. I am still wondering how many of you out there is using compression on your disks for files stored by applications? If we read the message it, the solution is simple. Here are the steps to un-compress a folder.

  • On the desired folder (in our case “C:\Program Files”) right click and go to properties.
  • Click on “Advanced” button.
  • Uncheck the box “Compress the drive to save space”
  • Click Apply and then Ok.
  • Let the process finish.

compress 01 SQL SERVER   Installation Error   INSTALLSHAREDDIR parameter is not valid because this directory is compressed or is in a compressed directory

Later I got message from the reader that he was able to install SQL without any further issues. As I say always, most of the error messages are self-explanatory. All we need is to understand how the system is being used. In this context it is worth to know there are few KB Articles (231347)

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

SQL SERVER – Msg 8152, Level 16, State 14 – String or binary data would be truncated

Earlier this week, I have blogged about how to suppress Warning: Null value is eliminated by an aggregate or other SET operation SQL SERVER – Warning: Null value is Eliminated by an Aggregate or Other SET Operation.

If you read that blog, I mentioned during closure that this setting might cause unexpected behavior if not used properly.  First, let’s understand the error which I am talking about:

Msg 8152, Level 16, State 14, Line 8
String or binary data would be truncated.
The statement has been terminated.

I am sure that many developer might have seen this error at least once in their lifetime. This particular error message is raised by SQL Server when we try to insert long literal sting is longer than the defined table field datatype.  For example, if we try to insert a varchar with more than 100 characters into a varchar(50) field, we will get the following error. Here is an example script to reproduce the error:

USE tempdb
GO
IF OBJECT_ID ('MyTable') IS NOT NULL
DROP TABLE MyTable
GO
CREATE TABLE MyTable(Num INT, Hi VARCHAR(2), I VARCHAR(6), Am VARCHAR(2), Pinal VARCHAR(3), Who VARCHAR(9), Loves VARCHAR(11), SQLAuthority VARCHAR(10))
GO
INSERT INTO MyTable VALUES (1, 'Hi', 'I', 'Am', 'Pinal', 'Who', 'Loves', 'SQLAuthority')
GO

I have put the same column name as the value which I am inserting so that you can easily see the cause of the error. Since I am inserting ‘Pinal’ in varchar(3) and ‘SQLAuthority’ in varchar (10), we are getting the error.

One of the problems with this message is that SQL Server doesn’t tell you which table or column has the problem. The correct way to fix the issue is to find the column causing error and correct the data or column length.

Coming back to set option, if we use ansi_warnings as OFF, the error would be suppressed and whatever can fit in the column, would be inserted, the rest would be truncated.

SET ansi_warnings OFF
GO
USE tempdb
GO
IF OBJECT_ID ('MyTable') IS NOT NULL
DROP TABLE MyTable
GO
CREATE TABLE MyTable(Num INT, Hi VARCHAR(2), I VARCHAR(6), Am VARCHAR(2), Pinal VARCHAR(3), Who VARCHAR(9), Loves VARCHAR(11), SQLAuthority VARCHAR(10))
GO
INSERT INTO MyTable VALUES (1, 'Hi', 'I', 'Am', 'Pinal', 'Who', 'Loves', 'SQLAuthority')
GO
SELECT * FROM MyTable
GO

string truncate 01 SQL SERVER   Msg 8152, Level 16, State 14   String or binary data would be truncated

As we can see that the error was suppressed in the above code, but we lost part of the actual data which we were supposed to have on the table. So be cautious when working with such SET options.

Conclusion – Use ANSI_WARNING OFF with care, it might have effect on data insertion.

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

SQL SERVER – Warning: Null value is Eliminated by an Aggregate or Other SET Operation

Working with NULL’s are always a challenge – not many developers understand how NULL works sometimes. In a related note, long back I wrote below blog in which I explained two set options Quoted Identifier and ANSI NULL.

SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

In future blogs, I would try to explain another one called ANSI_WARNINGS. Let’s look at the various warnings which can be suppressed when working with NULL.

Warning: Null value is eliminated by an aggregate or other SET operation

Let us run below script in SQL Server Management Studio to see the effect.

SET NOCOUNT ON
GO
DECLARE @ItemSale TABLE (ID INT, Qty INT, Price MONEY)
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 1, 25, 100
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 2, NULL, 200
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 3, 5, NULL
GO
SELECT SUM(Qty) 'Sum - 1' FROM @ItemSale WHERE ID IN (1,3)
-- no warning
GO
SELECT SUM(Price) 'Sum - 2' FROM @ItemSale WHERE ID IN (1,3)
-- Warning: Null value is eliminated by an aggregate or other SET operation.
GO
SELECT AVG(Qty) 'Avg' FROM @ItemSale WHERE ID IN (1,2)
-- Warning: Null value is eliminated by an aggregate or other SET operation.
GO

Here is the output

ansi warning 01 SQL SERVER   Warning: Null value is Eliminated by an Aggregate or Other SET Operation

As the error says, NULLs are being ignored because we are using aggregate function (SUM, AVG). To avoid the warning we can use “set ansi_warnings off” before the script. Here is the modified script.

SET ANSI_WARNINGS OFF
GO

Here is the output after adding the set option:

ansi warning 02 SQL SERVER   Warning: Null value is Eliminated by an Aggregate or Other SET Operation

First of all we should remember that default value of the setting is OFF, which is actually good. In subsequent blogs, we would cover an error which can be suppressed using same set option. Tuning it ON can be dangerous as well. Stay tuned!

What would be interesting is if anyone out there has turned this setting ON deliberately. Is there a scenario where you found it useful? Let me know.

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

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

memory grant 01 SQL SERVER   How to fix the SQL Server Error 8657 or 8658?

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)