Archive for the ‘Error Messages’ Category


While gathering replication backlog details, ran into this interesting error. The goal was to run the sp_replmonitorsubscriptionpendingcmds stored procedure and store the output in a table. As we’ve seen in a recent post, redirecting output of a stored procedure execution into a table is possible; But in this case, it throws an error saying that is not allowed.

INSERT INTO #DC1_Repl_Backlog
EXEC  sp_replmonitorsubscriptionpendingcmds
		  @publisher	= 'InstanceName'
		, @publisher_db	= 'DBName'
		, @publication	= 'Publication'
		, @subscriber	= 'Subscriber'
		, @subscriber_db= 'DBName2'
		, @subscription_type = '0'
Msg 8164, Level 16, State 1, Procedure sp_replmonitorsubscriptionpendingcmds, Line 233
An INSERT EXEC statement cannot be nested.

(0 row(s) affected)

With the available information, right now, a clear & coherent explanation fo this behavior is not available from my end. But my guess is this; The code inside this stored procedure must be using a similar INSERT INTO #table EXEC sp_xyz, hence the error “INSERT EXEC statement cannot be nested


OPENROWSET helps in getting around this. See the sample code below:

IF OBJECT_ID('tempdb..#DC1_Repl_Backlog') IS NOT NULL
	DROP TABLE #DC1_Repl_Backlog

CREATE TABLE #DC1_Repl_Backlog (
	  pendingcmdcount	BIGINT
	, estimatedprocesstime	BIGINT

INSERT #DC1_Repl_Backlog (pendingcmdcount, estimatedprocesstime)
		'EXEC Ditribution.dbo.sp_replmonitorsubscriptionpendingcmds
					  @publisher = ''PublisherInstance''
					, @publisher_db	= ''DBName''
					, @publication = ''Publication''
					, @subscriber = ''Subscriber''
					, @subscriber_db = ''DBName2''
					, @subscription_type = ''0'''

FROM #DC1_Repl_Backlog
Hope this helps,

Read Full Post »

Quick one today:

Recently, in one of our lab machines, we ran into this errors during replication reconfiguration (rebuilding) efforts.

Msg 18752, Level 16, State 1, Procedure sp_replcmds, Line 1
Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, 
and sp_replshowcmds) can connect to a database at a time. If you executed a 
log-related procedure, drop the connection over which the procedure was executed 
or execute sp_replflush over that connection before starting the Log Reader Agent 
or executing another log-related procedure.

The error message, though verbose, it was a bit confusing to understand the underlying cause for this error. Once, we reiterated the steps that resulted in this error, it became clear as to what could have contributed to this error. We had replication set upon a lab machine, and before this error occurred, we were attempting to drop transactional replication using SSMS GUI.

Turns out Log Reader agent was not properly stopped before attempting this; To resolve this, just stop the Log Reader Agent. To be sure, go to SSMS > Replication > Right click on Publication and go to ‘View Log Reader Agent Status‘.

Once Log Reader Agent is stopped, we were able proceed with dropping Transactional replication configuration.

Hope this helps,

Read Full Post »

Quick one today:

Recently, we ran into this interesting error when running some random queries on a Sql Server machine.

An error occurred while executing batch. Error message is: 
There is not enough space on the disk.

The error message seems nebulous and its hard to decipher what it is referring to. The usual suspects of MDF & LDF files are all good. All data and backup drives on the server have plenty of free space. So it was a bit confusing.

Upon further banging-head-on-the-desk, it became obvious that the problem is not with Sql Server, but with SSMS. On Operating System, Management Studio has its own workspace, where it stores all the results, and that is running out of free space.


  • Go to %TEMP% directory and clear up some space.
  • And clear up some more space on C: (where SSMS is installed).

With these two actions, SSMS works again.

Hope this helps,

Read Full Post »

Quick one today:

Earlier today, in our SSRS lab, after deploying reports using PowerShell, we ran into this error:

The dataset `DataSource1' refers to the shared data source `DataSource1', 
which is not published on the report server. The shared data source `DataSource1' 
must be published before this report can run.

Obviously, since we are still in the process of figuring out the correct “SSRS Deployment Steps” for automation, we may have missed a step or two. In this case, we missed/forgot the “data source association to each RDL” step.


Since we are still figuring out an automated step to it, for now, this is what we did to resolve this issue manually.

  1. Go to Reports Manager URL on IE browser (with Administrator rights)
  2. Go to the individual report/RDL and hover mouse over it.
  3. Click on the right side yellow drop down and go to ‘Manage’
  4. Go to ‘Data Sources’ tab
  5. Assign the correct shared data source (see below image for clarity)
Hope this helps,

Read Full Post »

Quick one today:

Earlier today, when installing Sql Server 2016, ran into this interesting error:

Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase


Looks like JRE (Java Runtime Engine) is needed for Polybase application to run. So, lets get it from Oracle website here. Download from the JRE section (see image below).


In the new page, “Accept License Agreement” & download the version pertinent to your environment.


After installing JRE, the checks ran successfully for install.

Hope this helps,

Read Full Post »

Quick one day:

Lately, while working on SSRS, we’ve uncovered some tidbits that have been helping us identify the root cause. Here is one of them:

In our environment, we pull SSRS reports from a client application (web page); For the most part, things run smooth. On occasion, when we run into issues, we need to be able to pull up the underlying error message. In SSRS, by default, error reporting back to calling application (client webpage) is disabled. But it is easy to enable it as and when needed and then turn it off, once done.

Go to SSMS and connect to Reporting Service. Right click on the root and go to properties. Under Advanced tab, under Security section, look for EnableRemoteErrors and set the value as True from the drop down. This sends error back to the client, so we can see what is going on. See the image below for clarity.



Read Full Post »

Interesting one today:

On one of our production machines, we recently added a new LUN to a SQL cluster. A task like this is a team effort. Sysadmins perform some steps and DBA carry out the remaining. In this article, the main focus is on covering the steps after the LUN is added to the OS & Sql Cluster by the sysadmins.  For context, we’ll start with high level steps before going into details.

Sysadmins steps

  1. Add new storage to the machine/OS as an available storage
  2. Format the available drive with appropriate settings (cluster size) and add it as a new drive
  3. Make drive available to the Cluster using “Add Disk” screen in FailOver Cluster Management tool.

DBAs steps

  1. Add available storage to Sql Cluster
  2. Configure dependency (and check the report before & after)
  3. Add data file on the new cluster storage drive

Here we’ll cover the DBA steps in detail:

Some of these steps were covered under a different recent article as part of dealing with an error message, but here we’ll cover it as a task by itself (which it is).

Add New Storage

Once sysadmins have made the new storage as an ‘available storage’ to OS Cluster, it needs to be added as a new storage location to the SQL Cluster.

In FailOver cluster manager, go to Sql Server Resource Group for this SQL Cluster and right click for detailed options and choose “Add Storage” (see image below)


Once successful, go to Storage\Disks under in FailOver Cluster Manager to confirm the availability. See image below:


Configure Dependency

Adding the storage is an important step, and equally important step is adding the new drive to Sql Cluster Dependency Chain. Dependency Chain informs Sql Sever “how to act”, when any resource in the Cluster becomes unavailable. Some resources automatically trigger cluster failover to other node; some resources do not. This decision is made based on the configurations in Dependency Chain.


Critical: Data drive/LUN that has database files is critical for optimal availability of the Sql Cluster. So, if it becomes unavailable, failing over to other available nodes is imperative to keep the cluster available.

Non-Critical: In some scenarios, Sql Server Agent is not considered as Critical. So if it stops for some reason, Cluster will make multiple attempts to start it on the same node, but may not necessarily cause failover.

This is a business decision. All these “response actions” will be configured in Cluster settings.

Now, check the dependency report (before); We can see that new drive exists in Cluster, but is not yet added to the Dependency Chain.


To Configure Dependency Chain, go to the Sql Server Resource Group under Roles in FailOver Cluster Manager. See the image below for clarity:

Then go to the bottom section for this Resource Group, where all the individual resources that are part of this Resource Group are displayed.

Under “Other Resources“, right click on Sql Server Resource and choose properties.

do As show


In the “Sql Server Properties” window, we can see the existing resources already added to dependency chain logic.


Now, go to the end of the logic list and choose “AND” for condition and pick the new Cluster Storage to be included. See image below for clarity:


After saving the settings, regenerate the Dependency Chain report. Now, we’ll see the new drive as part of the logic.


Add Database Data File to New Cluster Storage

Now, that the new drive is ready, we could easily add a new data file to the new location.

-- Add data file to new storage location
USE [master]
		  NAME 			= 	N'SampleDB_Data3'
		, FILENAME 		= 	N'U:\MSSQL\Data\SampleDB_Data3.NDF'
		, SIZE 			= 	3500 GB
		, FILEGROWTH 	= 	100 GB
		, MAXSIZE 		= 	3900 GB
Hope this helps,

Read Full Post »

Older Posts »