When SQL Server Replication Breaks After Windows Updates...

I received an email from a customer yesterday regarding their Replication, which began failing with this error after Windows updates were applied:

Message Replication-Replication Transaction-Log Reader Subsystem: agent servername-xxx2 failed. Executed as user: domainname\svcaccount. A required privilege is not held by the client. The step failed.

Slightly dummied, but the important content is in red.  What does that mean?  'A required privilege is not held by the client'... he didn't change anything, I didn't change anything - why is Replication suddenly failing with permissions problems?

The only known change was the Windows Update, but every SQL Server DBA really should know that Windows Updates are often known to trigger failures like this due to security changes.  In this case, Group Policy reapplications were performed, and silently overwrote the local security settings for the SQL Server Agent service account.

How did I know this?  

First, I checked the security polices for the SQL Server Agent service account in Run \ secpol.msc \ Local Policies \ User Rights Assignment.  Specifically, 'Log on as a batch job' and 'Log on as a service', which are needed by the Agent to run snapshot and log reader replication tasks.  Neither were enabled for the Agent's service account, DOMAIN\SQLAgentSvc:

So, I input the Agent service account in there, applied and restarted the Agent, but it made no impact.  The errors continued.  I even rebooted the server but still saw no impact from the change.  Then I ran this in powershell:

Get-ResultantSetOfPolicy -ReportType Html -Path C:\GPOReport.html

That cmd retrieves the Resultant Set of Policy (RSoP) information for the computer to html file for review, and it did NOT show 'Log on as a batch job' or 'Log on as a service' for the Agent's service account within Local Policies, User Rights Assignment:

This tells us that the domain-level GPO (Group Policy Object) reasserted itself after the Windows updates and overwrote the local security policy settings for the SQL Server service accounts. Without this policy, the Agent no longer has the permissions needed to run the snapshot and log reader replication tasks.  Hence, the error 'A required privilege is not held by the client'.

The fix?

Assign both of these policies at the Group Level within Group Policy Management Console (gpm.msc) for the SQL Server Agent service account:

  Log on as a batch job

  Log on as a service

Then restart the SQL Server Agent.

After doing so, the failing replication tasks immediately begin to recover.  I believe it took about 5-7 minutes for everything to recover fully.

In my Customer's words... "I just want to call out that you are amazing."

Estimating SQL Server backup sizes — with and without compression

How big will my backup file be?  Very good question.  And of course, it depends... especially if you're using compression.

I was working something today that required a backup beforehand for safety measures, and the server had limited storage.  So, I needed to estimate the backup file size beforehand to confirm whether I had enough space for the bak file.

This script will show you how to estimate the backup size, both uncompressed and compressed, using a 30% reduction, which is a realistic compression ratio.  Please understand, the actual backup sizes vary and depend on table contents, data types, how much empty space exists in the data files and more, but 30% is a safe rule-of-thumb that works well for general estimates.

USE master;

-- input your database name here

DECLARE @dbname SYSNAME = 'DBA';

-- estimate compressed size using 30% compression ratio

DECLARE @compratio DECIMAL(5,2) = 0.30;

-- estimate backup size

SELECT 

    DB.name [Database],

    CAST(SUM(MF.size) * 8.0 / 1024 AS DECIMAL(18,2)) [CurrentDB_SizeMB],

    CAST(SUM(MF.size) * 8.0 / 1024 AS DECIMAL(18,2)) [Estimated_BackupSizeMB_Uncompressed],

    CAST(SUM(MF.size) * 8.0 / 1024 * (1 - @compratio) AS DECIMAL(18,2)) [Estimated_BackupSizeMB_Compressed],

    @compratio [Assumed_Compression_Ratio]

FROM 

    sys.master_files mf INNER JOIN sys.databases db 

  ON mf.database_id = db.database_id

WHERE 

    DB.name = @dbname

    AND MF.type_desc IN ('ROWS', 'LOG')

GROUP BY 

    DB.name;

 

Example output:

 

Of course, you can also use the backup history to estimate backup size, but the above is just a quick method to estimate the backup size, compressed or not, using just the current database size and the compression ratio -- in this case, 30%.  

Provide access to SQL Server Activity Monitor without giving sysadmin perms

 

Is there any way to provide a user access to the Activity Monitor without enabling them as a sysadmin?  Yes.  Of course there is.  More easily managed if we do it with a server-level role than to multiple individual logins, like this:

     USE master;
     -- Step 1: Create the custom server role
     CREATE SERVER ROLE [ActivityMonitorRole];

     -- Step 2: Grant the necessary permission to the role
     GRANT VIEW SERVER STATE TO [ActivityMonitorRole];

     -- Step 3: Add specific logins to the role
     ALTER SERVER ROLE [ActivityMonitorRole] ADD MEMBER [YOURUSER];
     -- Repeat for additional logins as needed

Change your mind for some reason?  Just as easy to revert.

     -- Step 1: Remove login from the role

     ALTER SERVER ROLE [ActivityMonitorRole] DROP MEMBER [your_user];

     -- Step 2: Revoke VIEW SERVER STATE from the role

     REVOKE VIEW SERVER STATE FROM [ActivityMonitorRole];

     -- Step 3: Drop the role if no longer needed

     DROP SERVER ROLE [ActivityMonitorRole];

What SQL Server services are running on that server?

Good question. 😏 Of course, there are many ways to answer this question in Windows gui-land. A couple examples are the SQL Server Configuration Manager or services.msc, but both are more timely, and it's easy to miss something.  This script provides a faster way to gather all SQL Server service details from your server with just one call.

This is the output from one of my v2019 instances:

Here's the script...

/* query status details for all existing SQL Server services  */
SET NOCOUNT ON;
 
-- temp tables
IF (OBJECT_ID ('tempdb..#RegResult')) IS NOT NULL
DROP TABLE #RegResult;
CREATE TABLE #RegResult (
    ResultValue NVARCHAR(4)
       );
 
IF (OBJECT_ID ('tempdb..#ServicesServiceStatus')) IS NOT NULL
DROP TABLE #ServicesServiceStatus;
CREATE TABLE #ServicesServiceStatus(
    ID INT IDENTITY(1,1),
    SQLServerName NVARCHAR(128),
    ServiceName NVARCHAR(128),
    ServiceStatus VARCHAR(128),
    CheckDateTime DATETIME DEFAULT (GETDATE()),
    PhysicalSrvName NVARCHAR(128)
  );
 
IF (OBJECT_ID ('tempdb..#Services')) IS NOT NULL
DROP TABLE #Services;
CREATE TABLE #Services(
    ID INT IDENTITY(1,1),
    ServiceName NVARCHAR(128),
    DefaultInstance NVARCHAR(128),
    NamedInstance NVARCHAR(128)
  );
 
-- load service details
INSERT #Services
VALUES 

 ('MS SQL Server Service','MSSQLSERVER','MSSQL'),
 ('SQL Server Agent Service','SQLSERVERAGENT','SQLAgent'),
 ('Analysis Services','MSSQLServerOLAPService','MSOLAP'),
 ('Full Text Search Service','MSFTESQL','MSSQLFDLauncher'),
 ('Reporting Service','ReportServer','ReportServer'),
 ('SQL Browser Service - Instance Independent','SQLBrowser','SQLBrowser'),
 ('SSIS','MsDtsServer110','MsDtsServer110');
 
-- declarations
DECLARE
    @ChkInstanceName NVARCHAR(128),
    @ChkSrvName NVARCHAR(128),
    @REGKEY NVARCHAR(128),
    @i INT=1,
    @Service NVARCHAR(128);
 
SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
 
-- SQL Server Services selection
WHILE (@i<=(SELECT MAX(ID) FROM #Services))
BEGIN
IF (@ChkSrvName IS NULL OR (
 SELECT COUNT(*) FROM #Services 

 WHERE ServiceName IN ('SQL Browser Service - Instance Independent','SSIS')
 AND ID = @i) > 0

)
SELECT @Service= DefaultInstance FROM #Services WHERE ID = @i
  ELSE
SELECT @Service= NamedInstance+'$'+CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
FROM #Services WHERE ID = @i
 
SET @REGKEY = 'System\CurrentControlSet\Services\' + @Service
INSERT #RegResult ( ResultValue )
EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
 
-- check service status
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
   INSERT #ServicesServiceStatus (ServiceStatus)
   EXEC xp_servicecontrol N'QUERYSTATE',@Service
END
ELSE
BEGIN
   INSERT #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
END
 
UPDATE #ServicesServiceStatus
SET
 ServiceName = (SELECT ServiceName FROM #Services WHERE ID = @i),
 SQLServerName = @@SERVERNAME,
 PhysicalSrvName = (

    SELECT CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS')AS VARCHAR(128)))
WHERE
  ID = @@IDENTITY
 
  TRUNCATE TABLE #RegResult
  SET @i = @i + 1;
 
END
 
-- return all details
SELECT * FROM #ServicesServiceStatus
 
SET NOCOUNT OFF;

Hope to have helped!

Where is the 1205 error for the SQL Server Deadlock ?!!

Just a short post, but I believe still helpful.  Yesterday I was playing around with different methods of capturing deadlock notifications.  I used XE's (Extended Event Sessions), Service Broker Queue / Event Notifications and SQL Server Alerts. While testing the Alerts, I simulated my own deadlock and produced the deadlock event myself, like this:

The expectation here was that the SQL Server Alert would send me a notification for the deadlock event... but it didn't.  Curious.  I verified the mail and the operator, I verified the last_occurrence_date of the alert... everything checked out good, but there still wasn't any 1205 recorded in the SQL Server Error log.  Then I just decided to be sure the 1205 was in sys.messages:

We can see it IS there, but do you see that "is_event_logged" = 0 ?  This means it is not going to be recorded in the SQL Server Error log.  Easily amended:

               EXEC master.sys.sp_altermessage
                  @message_id = 1205,
                  @parameter = 'WITH_LOG',
                  @parameter_value = 'true';

I generated the next deadlock and the 1205 is now in my Error Log:

All is well now, and I can move forward w/the deadlock capture.  I will post that here when it is complete.  See you soon!

More than one database transaction log file?

You can have more than one transaction log file for your database... but why would you?   

SQL Server will only write to one log file at a time, regardless of how many you give the database.  The transaction log file is written to sequentially, not serially, and there are ZERO performance benefits to having more than one log file for your database.  In fact, multiple log files can actually degrade your performance in some cases.  

But again, why would you have more than one log file?  Easy.  Your log file blows up due to a rogue transaction or some other unexpected reason, and your drive is filling up fast.  You cannot afford the downtime, so your only choice is to add a 2nd transaction log file temporarily.

Here's how:

USE master;
ALTER DATABASE Nautilus ADD LOG FILE (
        name = 'NewLogFilename',
        filename = 'D:\MSSQL\2017\Log\Nautilus_log_2.ldf',
        size = 1048MB,
        filegrowth = 5%
        )
GO

Now you can do whatever you need to do operationally until you reach a point where you can clean things up and remove that 2nd log file.  When it doesn't contain any transactions, the log file can be removed with this ALTER statement:

ALTER DATABASE Nautilus REMOVE FILE NewLogFilename;

If the log file is not completely empty, your statement may fail with this error:

The fast way to resolve that is to backup the log first:

BACKUP LOG Nautilus TO DISK = 'F:\Backup\Nautilus.bak'

Now run the same ALTER statement again and it should succeed:

We've got to remember that there is no reason to create more than one transaction log file for your database under normal circumstances.  The above method can be used in the abnormal or unexpected situations when you're running out of disk and need to do something fast to keep your database online.

See these for more details:   

Adding & removing data or transaction log files
Multiple transaction logs for SQL Server databases

Change SSAS server mode from Multidimensional to Tabular

I had to change a SQL Server Analysis Service (SSAS) instance from Multidimensional to Tabular today, and want to share the steps with you, my loyal readers.  😊

Why was this needed?  Because I didn't ask beforehand what deployment mode was desired, and I just used the default of 0, which is multidimensional.  My mistake.  Haste makes waste, I believe they say.  But fortunately, the fix is easy.  No reinstallation needed.  

These are my steps:

1.      Backup any databases and detach (Multidimensional databases are not usable in Tabular instance).

2.      Stop SSAS service

3.      Open notepad as administrator, then File + Open, browse to your \OLAP\Config directory:

D:\Program Files\Microsot SQL Server\MSAS16.MSSQLSERVER\OLAP\Config

4.      Open the msmdsrv.ini file, change DeploymentMode to 2, save and close file.

5.      Restart SSAS service

6.      All done

This is where your msmdsrv.ini file is, and the location of DeploymentMode setting within it:

There are 3 DeploymentModes, and the same steps can be used to change to Multidimensional or SharePoint:

0  Multidimensional

1  SharePoint

2  Tabular

    

Unsure what your current SSAS DeploymentMode is?  Launch SSMS, connect to Analysis Services, right click server, choose 'Properties' and here you go:

Any reference up there to the \OLAP\Config directory will change based on your SSAS build.  Mine is at \MSAS16.MSSQLSERVER\OLAP\Config for v2022, but yours will vary if your version is different.

Further reading on your SSAS server mode:

https://learn.microsoft.com/en-us/analysis-services/instances/determine-the-server-mode-of-an-analysis-services-instance?view=asallproducts-allversions&viewFallbackFrom=asallproducts-allversionshttps%3A%2F%2Fwww.google.com

Hope to have helped!