Category: Issues

Scenario

At this point, you have probably heard about the feature called System Versioned Temporal Tables released on SQL Server 2016. So, let’s imagine that your team has decided to implement that on a table called “Orders”.

The application is using different access patterns to modify the data in this table. There are direct statements coming from the application or the application is running long batches in explicit transactions where it makes several changes to multiple tables. The update on the “Orders” table is not the first statement in those long batches! So, suddenly you start seeing the following error message:

Data modification failed on system-versioned table “Orders” because transaction time was earlier than period start time for affected records.

But, what’s the problem here?

The system generated start and end times are keyed to the server’s system time at the moment the BEGIN TRANSACTION is executed.

The times recorded in the system datetime2 columns are based on the begin time of the transaction itself. For example, all rows inserted within a single transaction will have the same UTC time recorded in the column corresponding to the start of the SYSTEM_TIME period.

Share this:

Like this:

Many times I face questions from development teams “Why ado.net queries run so slow and my SSMS queries are so fast?”.

The most common reason of this behavior is about the option ARITHABORT.
Ado.net defaults are different than SSMS settings:

Your application connects with ARITHABORT OFF, but when you run the query in SSMS, ARITHABORT is ON and thus you will not reuse the cache entry that the application uses, but SQL Server will compile the procedure anew, sniffing your current parameter values, and you may get a different plan than from the application. So there you have a likely answer to the initial question of this article. There are a few more possibilities that we will look into in the next chapter, but the by far most common reason for slow in the application, fast in SSMS in SQL 2005 and later is parameter sniffing and the different defaults for ARITHABORT.

Like this:

You might lose communication between the primary and secondary replicas.

As known, there is a policy on SQL Server to check the data synchronization state of the database replica. The policy is in an unhealthy state when the data synchronization state is NOT SYNCHRONIZING or the state is not SYNCHRONIZED for the synchronous-commit database replica.

After investigating the sql server errorlog, I got the following msg:

The target database, ‘YourDatabase’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. (Microsoft SQL Server, Error: 976)

According to Technet, this issue can be caused by the following:

The availability replica might be disconnected.

The data movement might be suspended.

The database might not be accessible.

There might be a temporary delay issue due to network latency or the load on the primary or secondary replica.

Share this:

Like this:

The node runner application used by SharePoint 2013 preview for search related process slows down the system due to memory leak issues.

To fix this issue follow the below 2 steps

1. Open SharePoint PowerShell window and execute the below command

Set-SPEnterpriseSearchService -PerformanceLevel Reduced

2. Open the config file at C:\Program Files\Microsoft Office Servers\15.0\Search\Runtime\1.0\noderunner.exe.config and change the value of memoryLimitMegabytes in node to some other value other than 0, say 100 or 200 which sets the cap limit for memory to be used.

Like this:

Days ago I was installing the Sharepoint 2013 on a new BI environment of a costumer, but during the configuration I had some issues that I will share with you.

Scenario

1 – For safety, we have separated the database service (SQL Server 2012) from the analysis/report/sharepoint services.
2 – We have changed the port number of database and analysis services.

Issues

1 – During the installation of Sharepoint, we have received an error on the step of creation databases.
I don’t know the reason, but when we Install the Sharepoint with another port number, the installation loses itself.
So, we created an alias on SQLServer and tried again setting the alias name. (Success)

2 – After the Sharepoint installation, I tried to connect on the site and we receveid an error 404.
Then, we created a log on IIS and we got the message bellow.

Handler “PageHandlerFactory-Integrated” has a bad module “ManagedPipelineHandler” in its module list

Share this:

Like this:

Danilo is a Database Administrator with over 12 years of experience. He is a Microsoft Certified IT Professional (MCITP) Database Administrator and Developer. His experience includes solutions for management, assessment and implementation of SQL Server environments making use of best practices, performance tuning, and high availability.