Infrastructure at your Service

All Posts By

Stéphane Haby

Stéphane Haby

SQL Server: msdb too big with the table dbo.sysmaintplan_logdetail

By | Database Administration & Monitoring, Database management, SQL Server | No Comments

Today, I was by a new customer and the I see that the msdb data file was over 4GB. A little too big… To see which table are so big, in SSMS, I select the msdb database, right-click, go in Reports>Standards Reports> Disk usage by Top Tables As you can see, the table dbo.sysmainplan_logdetail is the big one with a size of 2,3GB and 13205 records. This table is the log for the maintenance plan…

Read More
Stéphane Haby

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

By | Database Administration & Monitoring, Database management, SQL Server | No Comments

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, or you do not have permission.’ My first reflex was to open SSMS and right-click…

Read More
Stéphane Haby

SQL Server: how to have the number of core-based licenses used on my environment?

By | Database Administration & Monitoring, Database management, Development & Performance, SQL Server | No Comments

This week, a customer asks me to find out how many SQL Server Standard licenses are used in their environment. The licensing is core based and to not be used for Sserver + Cals licenses. To have an idea, I create a script following the Microsoft document about the licensing here. This script is valid for the SQL Server 2016, SQL Server 2017 and SQL Server 2019. If you have another version of SQL Server,…

Read More
Stéphane Haby

How to find the TLS used for the SQL Server connection

By | Database Administration & Monitoring, Database management, Security, SQL Server | No Comments

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 SQL Server 2016 with the query: CREATE EVENT SESSION [TLS_monitoring] ON SERVER              ADD EVENT…

Read More
Stéphane Haby

How to view and change SQL Server Agent properties with T-SQL queries

By | Database Administration & Monitoring, Database management | No Comments

Few days ago, after a reboot, we had this warning on the Agent Error Logs on many servers: Warning [396] An idle CPU condition has not been defined – OnIdle job schedules will have no effect “The CPU idle definition influences how Microsoft SQL Server Agent responds to events. For example, suppose that you define the CPU idle condition as when the average CPU usage falls below 10 percent and remains at this level for…

Read More
Stéphane Haby

SQL Server: Using Client Statistics to validate a query regression after a change in the database compatibility level

By | Database Administration & Monitoring, Database management, Development & Performance | No Comments

Few days ago, I have very poor performance on a database that I migrate from SQL 2012 to SQL 2019. The developer Team asks me to upgrade the number of CPU and the Memory to have better performance. First, I ask the Team to give me some queries sample to test the performance between the old environment in SQL server 2012 and the new one in SQL server 2019. To do the test, I use…

Read More
Stéphane Haby

SQL Server: “Cannot resolve the collation conflict” in a Stored Procedure

By | Database Administration & Monitoring | No Comments

This week I have a new little challenge by a customer. By executing a “standard” stored procedure, I have the error: Msg 468, Level 16, State 9, Procedure Get_logsessions, Line 56 [Batch Start Line 119] Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “Latin1_General_CI_AS_KS_WS” in the equal to operation. The Stored Procedure named Get_Logsessions is to log all sessions running on the server in a table. Looking with the query I develop and present you…

Read More
Stéphane Haby

SQL Server SCOM database: A lot of errors 777970008 in the errorlog file

By | Database Administration & Monitoring | No Comments

Few weeks ago, during a audit, I have a lot of error 777970008 in the errorlog file from the SCOM instance. All 5 minutes, we had 4 lines more in the ErrorLog and the file growth a lot. After using google to find a cause of this message and resolve it, I finally find a good blog from Kevin Holman. I downloaded the script here and executed the part for the error 777970008 only: DECLARE…

Read More
Stéphane Haby

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

By | Database management, SQL Server, Technology Survey | No Comments

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 2017, we have 5 parameters for the database configuration: In SQL server 2019, we have…

Read More
Stéphane Haby

SQL Server 2019: What’s new in sp_configure and sys.configurations options?

By | Database Administration & Monitoring, Database management, SQL Server, Technology Survey | No Comments

SQL Server 2019 added 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(*),@@version FROM sys.configurations In SQL server 2016, we have 74 parameters for the instance configuration: In SQL server 2017, we have 77 parameters for the instance configuration: In SQL server 2019, we have 84 parameters for the…

Read More