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%.