ERRORLOG flooded with "Service Broker endpoint" messages.

During system cleanup I hit an issue when SQL Server ERRORLOG file with a size of about 10 GB was 99.9% flooded by messages like this:

The Service Broker endpoint is in disabled or stopped state.

There were millions of them and every 15 minutes were adding the new ones.

I've searched the internet and got a not proven suggestion to create a Service Broker Endpoint.
Fortunately enough, the organization I'm servicing right now has Microsoft Service support hours assigned to a SQL DBA group. I've opened a ticket and got an answer from Microsoft.
MS representative referred me an online article: Service Broker with Always On Availability Groups (SQL Server) That article clearly states that all machines with Availability Groups must have
the Service Broker endpoint exists and is correctly configured.

So, the solution to stop that avalanche of useless messages will be very simple: just apply following script on a SQL Server, where you have Availability Groups and experiencing the issue:

CREATE ENDPOINT [SSBEndpoint]      STATE = STARTED      AS TCP  (LISTENER_PORT = 4022, LISTENER_IP = ALL )      FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS);

To prove that action is successful you can run following script:

SELECT name, protocol_desc, type_desc, state_desc, port FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER';

Also you can search in the ERRORLOG file that your Service Broker is Listening and does not produce garbage messages anymore:

EXEC sys.xp_readerrorlog 0, 1, N'listening';

Deleting a SQL Agent Job originated from an MSX server

Hit very weird situation when tried to remove jobs running on remote-target servers in multi-server managed environment.

First what I've tried is to unassign target servers from multi-server job on MSX server, however, it did not delete jobs on some targets.

When I've tried to delete them manually via SSMS I've got following error:

TITLE: Microsoft SQL Server Management Studio ------------------------------ Drop failed for Job 'CPU_Usage_By_Database'.  (Microsoft.SqlServer.Smo) For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18131.0+((SSMS_Rel).190606-1032)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. (Microsoft SQL Server, Error: 14274) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.5223&EvtSrc=MSSQLServer&EvtID=14274&LinkId=20476 ------------------------------

When I tried to follow help links, provided by newest SSMS, they led me to Microsoft's advertisement portal.

The second I've tried was manual deletion of that job by a script:

EXEC msdb.dbo.sp_delete_job @job_name=N'My_MSX_Created_Job', @delete_unused_schedule=1;

It was not successful and gave me following short error without Microsoft adds:

Msg 14274, Level 16, State 1, Procedure sp_delete_job, Line 91 [Batch Start Line 59] Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.

I've looked in "sysjobs" table and saw there a column "originating_server_id", which is referencing to "sysoriginatingservers_view" view, which has "originating_server_id" value always to be zero for the local server.
So the fixing script is as easy as this:

UPDATE msdb..sysjobs SET originating_server_id = 0 WHERE name=N'<Your Job Name>'; GO EXEC msdb.dbo.sp_delete_job        @job_name=N'<Your Job Name>',        @delete_unused_schedule=1; GO

SQL Server 2019. Use of UTF8 vs Unicode.

SQL Server 2019 is life and it is time to play around and discover some interesting features of it.

Will start from UTF8 and how it is better than UNICODE.

Here is a simple script to demonstrate abilities of newest SQL Server UTF8 feature:

GO USE tempdb GO DROP TABLE IF EXISTS dbo.tbl_Use_Of_UTF8 GO CREATE TABLE dbo.tbl_Use_Of_UTF8 (        ID INT IDENTITY (10,10)        , UnicodeColumn NVARCHAR(4000)        , UTF8Column VARCHAR(4000) collate LATIN1_GENERAL_100_CI_AS_SC_UTF8        , UTF8_Ukr_Column VARCHAR(4000) collate Ukrainian_100_CI_AS_SC_UTF8 ) GO INSERT dbo.tbl_Use_Of_UTF8 (UnicodeColumn) VALUES (N'༈༉ШЧݪݫݬݭܕܖܗܘܙऔकखഈ㔃㔄刀刁⠴⠵ȀȁȂȃꔂꔃՈՉ') ,(N'abcde༈༉ШЧݪݫݬݭܕܖܗܘܙऔकखഈ㔃㔄刀刁⠴⠵ȀȁȂȃꔂꔃՈՉ') ,(N'абвгдеАБВГДЕ') ,(N'abcdefghABCDEFGHабвгдеАБВГДЕ') ,(N'abcdefghABCDEFGH'); GO UPDATE tbl_Use_Of_UTF8 SET UTF8Column = UnicodeColumn, UTF8_Ukr_Column = UnicodeColumn; GO SELECT Chars = LEN(UnicodeColumn)        , UnicodeSize = DATALENGTH(UnicodeColumn)        , UTF_Size = DATALENGTH(UTF8Column)        , Ukr_Size = DATALENGTH(UTF8_Ukr_Column)        , UTF8Column, UTF8_Ukr_Column FROM dbo.tbl_Use_Of_UTF8; GO

The result will be like this:

From that result we can make following observations:
1. Use of multiple languages in UTF8 column takes more space than Unicode column. (First and Second rows)
2. Use of single non-Latin language in UTF8 field does not provide any size reduction at all. (Third row)
3. Use of a combination of Latin and a single non-Latin language bring some size reduction.(Fourth row)
4. Use of only Latin characters in UTF8 column gives us 50% size reduction over Unicode.

Conclusion

UTF8 is not suitable to store purely non-Latin languages.
However, it can be extremely useful when you store only Latin characters, but have in mind a possibility of storing small number of records, which contain non-Latin characters.

The most useful case for that can be database's "Address" field to store names of streets and cities in the US.
99.9% of which, will be in Latin, but names like "Doña Ana", "Lindström" or "Utqiaġvik" would require use of Unicode. With UTF8 you can save exactly 50% of your disk space and eventually have some performance improvements.