Skip to content

SQL Server

Microsoft SQL Server in practice: installation, Always On availability groups, backup and restore, performance tuning, security, PowerShell automation, SQL Server on Linux and upgrades.

SQL Server: Cannot execute as the database principal “dbo” does not exist

By a customer this month, I see a very big big ErrorLog file (more than 1,5GB). When I open the file, every 5 seconds, the following message is added: The activated proc ‘[dbo].[IdentityServerNotificationCleanup]’ running on queue ‘<database>.dbo.IdentityServerNotificationsQueue’ output the following:  ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated,… 

SQL Server – Granting SQL Server permission to service SID

Introduction We know that SQL Server itself is using it own SID NT SERVICEMSSQL$ and NT SERVICESQLAGENT$ to grant permission to the database engine and the SQL agent services. But the same method can be used for other application services to acces and use the SQL Server instances. The enables to allow the access to the SQL instance when the… 

SQL Server database design – Moving table to new filegroups

Introduction A database is very often created without thinking of its growing and in the most cases that is not really an issue until the data volume is not increasing a lot. And most of the time when a high data volume is expected, some actions are taken to organize the database in a more complex way, creating several data… 

dbi Event Recap: SQL Server Automatic Tuning and Query Store

Earlier this month, dbi services hosted a free multi-technology event. Details are available on our website. I had the opportunity to present a session about the Query Store and the Automatic Tuning feature available on SQL Server and Azure SQL Database. The first session in Lausanne was in French. A week later in Olten, I did it again in English.… 

SQL Server: Is Fill Factor applied during INSERT?

The Fill factor setting controls the amount of space that is left available on the index data pages. The goal is to allow new rows to be inserted or existing ones to change size (UPDATE)  without resulting in a page split. By default, the server setting is set to “0” which means that the leaf-level pages are filled to their… 

Configuring ODBC : SQLServer Access From Oracle

By Mouhamadou Diaw Sometimes you need to access to your SQLServer from your Oracle database. There is many ways and tools to do this. In this blog I am describing how to perform this task using ODBC. The configuration is below -Oracle Linux server : serverora 192.168.56.41 with Oracle 19 database orcl -Windows server : winrac1 192.168.56.100 with SQLServer Express… 

How to find the TLS used for the SQL Server connection

For a customer, I do some research to find which TLS is used on the SQL Server environment. The only way is to create an Extended Event. A big limitation is that the event used is only available on SQL Server 2016 and +. Before use the Built-In Diagnostics (BID) traces. After I implanted the first TLS Monitoring on  a… 

Automate restore from Rubrik with PowerShell (part 2)

As promised during my last Rubrik blog post I’m writing a second one to explain how to: log your different steps in a file add an integrity check after the restore and also improve my first scripting I will start by the end and improve my first scripts by creating functions for each step of the process. With Rubrik you… 

SQL Server: table variables performance limitations

Doing some performance troubleshooting for one of my customers I identified some issues with very large table variables inside Stored procedures. Table variables limitations are not well understood by developers although they are now well documented. Table variable rows estimation Let’s have a look at an example with my customer context which is SQL Server 2016, so compatibility level 130… 

SQL Server 2019: What’s new on database scoped configuration options?

SQL Server 2019 added three new options in sp_configure and sys.configurations. First, how can we find the difference between these SQL Server Versions. This simple query will give us the number of options and the SQL Server Version: select count(*) as number_of_configurations, @@version FROM sys.database_scoped_configurations In SQL server 2016, we have 4 parameters for the database configuration: In SQL server… 

SQL Server: Fixing another huge MSDB database – 80GB+

I have blogged several times about unusually large MSDB databases like here. Most of the time the problem comes from the backup history which is never purged. This time it’s different. Try to guess, if not the backup history, what can cause MSDB to increase abnormally in size? Let’s start by looking at the total size of the database: It’s…