Replication: A required privilege is not held by the client or Detect nonlogged agent shutdown.

Details

Written by Ranjeet

Sometimes replication log reader agent fail with below error.

Browse replication monitor to view below error

Error messages:

The job failed.  The Job was invoked by User XXX\useraccount.  The last step to run was step 3 (Detect nonlogged agent shutdown.).

Browse SQL Server agent job to view below error

Message: Executed as user: XXX\useraccount. A required privilege is not held by the client.  The step failed.

Both error are related to each other. Microsoft has written an article on this. Adding here as reference. https://support.microsoft.com/en-us/kb/911305

You can follow below steps in order to fix this issue. Don’t try anything directly on production. This is just my view/learning and not recommendation.  

1. Stop SQL Server agent service in window cluster and force not to start automatically
2. Stop the SQL Server agent using SQL Server Configuration Manager
3. Start SQL server agent back with window local account
4. Stop again and start with AD account “xxx\sqlserverbase_Agent”
5. Start SQL Server agent service back in Window cluster

Following above steps should fix the issue. 

0 Comments

Replication: Prefetch objects failed for Database 'sqlserverbase'.

Details

Written by Ranjeet

Snapshot agent is failing and throwing errors.

Browse SQL server agent job and you will see below error

[15%] The replication agent had encountered an exception.

Source: Unknown

Exception Type: Microsoft.SqlServer.Management.Smo.FailedOperationException

Exception Message: Prefetch objects failed for Database 'sqlserverbase'.

Message Code: Not Applicable

When you browse replication monitor you will see below error:

Error messages:

·          Source: Microsoft.SqlServer.Smo
Target Site: Void PrefetchObjectsImpl(System.Type, Microsoft.SqlServer.Management.Smo.ScriptingPreferences)
Message: Prefetch objects failed for Database 'sqlserverbase'.
Stack:    at Microsoft.SqlServer.Management.Smo.Database.PrefetchObjectsImpl(Type objectType, ScriptingPreferences scriptingPreferences)
   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.ObjectPrefetchControl.DoPrefetch(Database database)
   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.PrefetchObjects(ObjectPrefetchControl[] objectPrefetchControls)
   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoPrefetchWithRetry()
   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
   at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
   at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0

·          Server P-SQLSERVERBASE, Level 13, State 56, Procedure , Line 1
Transaction (Process ID 1898) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Source: MSSQLServer, Error number: 1205)
Get help: http://help/1205

We can see above error.

Solution: To avoid this error you can run snapshot in off hours. This error usually comes when there is memory issue on the server or deadlock. Here we can see it is deadlock issue. You can try running same again as I did and went successful.

0 Comments

TEMPDB: Troubleshooting tempdb space usage in SQL Server

Details

Written by Ranjeet

It is always fun working with tempdb. Today, I received below error. We already have optimized tempdb by creating multiple file as per the number of virtual CPU are available on server of same size. We were expecting no more issue with tempdb now. But again. Its okay. This is what tempdb is about. More you dig, more you learn. It’s a shared resource used by all the session and all the user database.

Could not allocate space for object 'dbo.SORT temporary run storage:  140782632435712' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

After looking the error, I decided to dig more on the space used by different objects, sessions and task in the tempdb. As you well known about tempdb stores the user objects, internal objects and version store. You must have two question in mind. What are those objects and how to identify those?

Register to read more...

0 Comments

DMV: Different types of system objects in SQL Server

Details

Written by Ranjeet

Microsoft covered long journey from first version 7 to 2016. During this time, Microsoft improved a lot in terms of providing system tables which helps DBAs to get detailed glimpse of metadata. What happening in sql server level in terms of usage and data distribution. Below are the types of different system object Microsoft has/had.

1. System tables
2. Compatibility views
3. Catalog Views
4. Dynamic Management Views (DMVs)
5. Dynamic Management Functions (DMFs)
6. Information Schema

System Tables

System table was introduced in SQL Server 2000/7. DBAs had direct access to those table which was also not good in terms of integrity and consistency of the data. Since it was direct access, any DBA can changes/alter/customize those tables.

Register to read more...

0 Comments

DMV: What is Dynamic Management View DMV and Dynamic Management Functions DMF in SQL Server

Details

Written by Ranjeet

Dynamic management views and dynamic management functions are system objects which have been designed to bring metadata about database server engine, database objects and other resources associated with it. By collecting these metadata, it helps DBA to monitor and troubleshoot different problem in SQL server like SQL server health, performance tuning.

DMVs exists in sys schema. Microsoft first introduced DMVs in 2005 version. It keep adding new additional DMVs with their new releases. All together DMVs is use to called new SQL Server performance tool in addition of SQL profiler, index tuning adviser and other catalog view, system tables.

Below is the query to get list of all DMVs and DMFs.

SELECT *

FROM sys.system_objects so

WHERE so.name like 'dm_%' 

Register to read more...

0 Comments

WIN CLUSTER: Add third node named Node C and remove node A from cluster and setup active SQL instance between Node B and Node C

Details

Written by Ranjeet

I got a call from my friend he was discussing one scenario. Let say, we have two node cluster having Node A and Node B. There are already SQL Server active passive has been installed and running. Now they want to add Node C and make Node C and Node B as active passive SQL cluster. And then remove Node C.

My first thought was since Node A has a main SQL installation and Node B as add node. We can’t remove Node A from cluster as node C will also be only add node not complete SQL installation. Now next question came in my mind. What happens when we do add node? Does install everything except creating new instance or only some library which requires to take the service ownership and run. See, in SQL Cluster in FCI known in SQL Server 2012/14/16, all the data and log files related to the database stores on shared storage. And all the required SQL server installation pack which is called instance is installed on both primary server and secondary (Add node). Microsoft says, we can add the additional node and can remove any existing node from the cluster. So, the answer is yes, we can do. Now next question is how. There are steps outlined. It is almost the same way we add node. Even to remove node from cluster, you need to run SQL server setup.exe and then follow the instructions.

1. Double click on setup.exe
2. Click on maintenance in left pan
3. Choose “Remove node from a SQL Server failover cluster”
4. Click Ok when all the check is green and it give you go ahead. If anything red, please troubleshoot
5. Click install on setup support first page
6. Click next
7. Select the instance name
8. Click Remove

Please don't try this process on any live system. Development server would be good for checking.  

0 Comments

ALWAYS ON: Timeout or delay from secondary subnet after failover within the subnet of primary subnet in SQL Server

Details

Written by Ranjeet

Let me give you brief about the environment. We did setup three nodes always on setup. It consists of two subnets/Data Center. I will use Data Center and Subnet meaning the same thing in this article. Saying primary Data center is same as Primary subnet. We first setup three node window cluster (WSFC) which spans the data centers. We kept two nodes in primary data center and one node in secondary data center. Once WSFC successfully configured. We installed one SQL Server 2012 instance on each server. Then further enabled always on and did setup always on availability group (AG) with always on Listener (AGL). Everything got setup as expected and functioning accordingly. Let me give little more brief about failover setup. We setup up synchronous automatic failover between Node A and Node B within primary data center and then manual failover to Node C in secondary data center. Just for FYI, Node C was not configured as participating in voting so we added one file share in primary data center to balance failover nature. Data synching and failover everything was working as per setup expectation with all the failover scenario tested.

Problem: The application has load balancing and application can connect the database from both the data center side. It means at one point of time if four connection request coming to AGL two can come from primary subnet and two can come from secondary subnet. There is one scenario when Node a primary replica and node B and Node C is serving as secondary replica. When we do failover from Node A to Node B, AGL starts pointing Node B as primary SQL instance and Node A and Node C as secondary instance/replica. Point to note, there is no failover across the subnet. So, AGL IP is not going to change. So, Still AGL is running on primary subnet IP only but just started to point Node B instance. This is called MAC address change. Now the problem is when it failed over to node B, all the request coming from primary subnet is connecting to SQL Server through AGL without any issue but at same time request coming from secondary data center is timing out. Strange, everything is good at database level. After 10 or 20 minutes, this use to get auto fixed. I setup n number of availability group in past few years but never been fall in this kind of issue. It is really a rare always on availability group error/behavior.

Register to read more...

0 Comments

DBMAIL: sp_send_dbmail failed with At least one of the following parameters must be specified in SQL

Details

Written by Ranjeet

Below code was running on one server but failing on other server. Both the SQL server are having same version. I was surprise seeing this error.

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @ProfileName, 

@body = @body,

@body_format ='HTML',

@recipients = @EMIL,

@subject = @Subject ;

When I run same above exec statement on second server, I got below error. 

Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 242

At least one of the following parameters must be specified. "@body, @query, @file_attachments, @subject".

Register to read more...

0 Comments

T-SQL: Script to find a varchar value in a database in SQL Server 2000

Details

Written by Ranjeet

Today, I found a script which was written by me in year 2006. Actually, that time there was a requirement to find a random varchar data value in a database where you don’t know the table and column name. Any table in the database can contain this value. This script was developed to run on small database. It's not a good practice to run on big database. Below is the script which is compatible for SQL Server 2000 version. I will update this script for later versions. 

/*

Created by Ranjeet

Created on 3rd of March 2006

Purpose: To find the any string value in the database. Just pass your value the proc It will return the searched

      TableName ColumnName with the value.

      This Proc will also generate the Scripts for all that tables so that you can check it out after executing the

      script. The script generation code has been made commented. Please remove the comment for displaying the script

      after find out the code.

*/

Register to read more...

0 Comments

SSRS: The report execution has expired or cannot be found. (rsExecutionNotFound) in SSRS

Details

Written by Ranjeet

In SSRS report server, I opened a report and it displayed the data in expected time. But after few minutes when I ran it again, I encountered below error message.

The report execution a0lwspmfxazmumni5h0bjyye has expired or cannot be found. (rsExecutionNotFound)

Initially, I thought there must some timeout issue from SQL Server. But when I read the error message, it is clearly saying report execution a0lwspmfxazmumni5h0bjyye has expired or cannot found. Then I just refreshed the report and it went well.

Learning: Sometime, thing goes well without doing anything just refresh. If any body seen error in your report server, please put your experience in comment section. 

0 Comments