File ID X of database ID XX cannot be shrunk as it is either being shrunk by another process or is empty.

I recently went into this error in SQL 2012 EE.

File ID 1 of database ID 27 cannot be shrunk as it is either being shrunk by another process or is empty.

For some reason the internal table gets lock and cannot shrink the file OR you have enabled auto shrink.

You need to increase the size of the data/log file to be shrunk. The increase can be 1 MB only to get out of the “internal table lock” it will allow you to shrink afterwards.

IBM DB2 Driver Error – OLE DB provider IBMOLEDB for linked server “” returned message “[DB2] SQL0332N Character conversion from the source code page “1208” to the target code page “65534” is not supported. SQLSTATE=57017

My team recently faced a curios issue whit the DB2 driver version 11.1 that we installed in the servers using Windows 2016 and SQL 2017.

The problem is when using a piece of code that is in line 13, “where sos.actp_plan_sosst_fg <> ‘C'” below. This same script works perfectly in the old server Windows 2008 R2 with DB2 driver version 9.5. Doing a little research we found that adding the “OleDbReturnCharAsWChar = 0” to the DB2CLI.ini file it doesn’t send any warning. You may experience slowness but at least the error and warning is gone.

Original Error:

OLE DB provider “IBMOLEDB.DB2ALIAS1” for linked server “DB2PRODUCTION” returned message “[DB2] SQL0332N  Character conversion from the source code page “1208” to the target code page “65534” is not supported.  SQLSTATE=57017

INNER JOIN (select veh.veh_id
,max(veh.acn_plan_id) as acn_plan_id
ON tplan.actp_plan_id = veh.acn_plan_id
ON tplan.actp_plan_id = sos.actp_plan_id
where sos.actp_plan_sosst_fg <> 'C'
and tplan.actp_plan_typ_cd = 90
group by veh.veh_id
) id
on id.acn_plan_id = sos.actp_plan_id

There is already an object named ‘TargetServersRole’ in the database. – Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 2714, state 6, severity 25


After patching or upgrading a SQL 2008 R2 or SQL 2012 the instance cannot be initialized in any node (if cluster) or individual (standalone).  If you take a look at the ERRORLOG you will something like this:

2019-03-24 07:50:43.29 spid5s      Setting object permissions...

2019-03-24 07:50:43.43 spid5s      Error: 2714, Severity: 16, State: 6.

2019-03-24 07:50:43.43 spid5s      There is already an object named 'TargetServersRole' in the database.

2019-03-24 07:50:43.43 spid5s      Error: 2759, Severity: 16, State: 0.

2019-03-24 07:50:43.43 spid5s      CREATE SCHEMA failed due to previous errors.

2019-03-24 07:50:43.43 spid5s      Error: 912, Severity: 21, State: 2.

2019-03-24 07:50:43.43 spid5s      Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

2019-03-24 07:50:43.43 spid5s      Error: 3417, Severity: 21, State: 3.

2019-03-24 07:50:43.43 spid5s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

2019-03-24 07:50:43.44 spid5s      SQL Server shutdown has been initiated

2019-03-24 07:50:43.44 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.



Before doing any modification, copy the system DB’s data and log files into a safe location the chances of master being corrupted are medium.

  1. Start by checking the ERRORLOG and identify the error. The path for ERRORLOG is in the properties of Configuration Manager:
  2. Start your SQL Server in emergency mode. You need to open a CMD as administrator and execute : NET START MSSQLSERVER /T902
  3. Once SQL has been started you can use SSMS and locate the path for msdb110_upgrade.sql
  4. Open the file and execute it in the impacted instance, you will see an error about TargerServerRole. See below.
  5. Delete the existing schema as shown below.
  7. Finally, start the services as normal. Everything should be back to normal. However, if you got a corrupted master and have no backups, you can follow up these steps:




Cluster resource ‘Enterprise Single Sign-On Service’ of type ‘Generic Service’ in clustered role ” failed.

Long story short: uninstall the SQL patches and make both node same patching level.

I recently faced an issue on our Biztalk SQL Server cluster and it couldn’t failover from node 1 to node 2. After troubleshooting I found the SSO service was not able to start in the second node, this is because SQL Server was unable to start as well, and somehow there is a dependency.

Some of the errors in the Windows Error Log and Cluster Error Log are:

Error 1:

Cluster resource ‘Enterprise Single Sign-On Service’ of type ‘Generic Service’ in clustered role ‘XXXXXBIZTSQL’ failed.

Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

Error 2: 

Clustered role ‘XXXXXBIZTSQL’ has exceeded its failover threshold.  It has exhausted the configured number of failover attempts within the failover period of time allotted to it and will be left in a failed state.  No additional attempts will be made to bring the role online or fail it over to another node in the cluster.  Please check the events associated with the failure.  After the issues causing the failure are resolved the role can be brought online manually or the cluster may attempt to bring it online again after the restart delay period.

I found the SQL patching level is different in node two, and it had installed GDR 5207 SQL Server 2014 (KB4019093) in the affected node.


After unistalling the patch in the affected node the failover worked just fine.



As a general comment, there are some of the SSO configurations that need to be enabled (checked) and by default they are not. At those are in our environments.  Go to Cluster Adminstrator –> Roles –> SQL Server –> Right click on Enterprise Single Sign-On Service.


How to restore encrypted databases (Cannot find server certificate with thumbprint)

This article is only valid for SQL 2008 and SQL 2008 R2 as some feature were removed/improved in further versions.


When you restore an encrypted backup in another SQL Server, it either restored it fully encrypted with null values only, or doesn’t restore at all by raising the following error:

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xE11A199C1059C6F1E0223B56581CDCF3F043DFE8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


In order to successfully make the restore in a different server you will need to create a master certificate in the detonation and transfer the certificates and backups in that order.



First, identify all the objects affected. This includes the certificate, keys, and databases. With the query below you can list the encrypted databases. Notice the thumbprint is the same as the error raised originally. In this case we only have one certificate.

Use master
SELECT    name,DEK.*
FROM      sys.databases D
JOIN      sys.dm_database_encryption_keys DEK
ON        DEK.database_id = D.database_id
ORDER BY  name

Result of encrypted databases

Second, identify the certificate by navigating in the source server to Master –> Security –> Certificates

Identify the certificate

Next, create a master key in the destination server. By default SQL creates one that is valid for system databases only. You need to create your own with the following syntax:

Command(s) completed successfully.

Next, you have to backup the certificate in the source and create a copy of it in the destination server.  It must be with password and private key otherwise you will have the following error in the destination server “Msg 15507, Level 16, State 1, Line 1 A key required by this operation appears to be corrupted.”

USE Master
TO FILE = 'Z:\Backup\DB_Encrypt_Cert.cer'
FILE = 'Z:\Backup\DB_Encrypt_Cert.prvk',

Restore and create a certificate in the destination based on the backup file you took in the step before. Notice the name must remain the same, you will need the private key, password, and pay attention on the syntax it changed from Encryption to Decryption. The warning is OK, this is because the original certificate was not set with an expiration date.

FROM FILE = 'E:\MSSQL\DB_Encrypt_Cert.cer'
FILE = 'E:\MSSQL\DB_Encrypt_Cert.prvk',

Warning: The certificate you created is expired.


Finally, you can restore the database with your normal method and it will with no issues.

Encrypted DB erstored
Encrypted DB restored

Useful links


Cannot fetch a row from OLE DB provider IBMDADB2 for linked server

We have a very peculiar error where we spent months working with Microsoft and IBM to figure it out what could be wrong. You can see the short summary below

I have SQL Server 2008R2 SP2 with the DB2 drivers installed, v. (64 bits). I have two instance in the same box, both using the same ODBCs, one instance is fully functional but second instance gives me this result when I try to execute a command on a linked server:

The OLE DB provider IBMDADB2 for linked server reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider IBMDADB2 for linked server

I already verified allow in process is = 1, and also assign the users the “Create global objects” user right.

This affects only the windows account but it works fine with SQL Logins. After months of troubleshooting we found the error can be fixed by Disabling the UAC you can refer to

  1. Click Start, and then click Control Panel.
  2. In Control Panel, click User Accounts.
  3. In the User Accounts window, click User Accounts.
  4. In the User Accounts tasks window, click Turn User Account Control on or off.
  5. If UAC is currently configured in Admin Approval Mode, the User Account Control message appears. Click Continue.
  6. Clear the Use User Account Control (UAC) to help protect your computer check box, and then click OK.
  7. Click Restart Now to apply the change right away, or click Restart Later, and then close the User Accounts tasks window.

I sincerely hope this could save somebody months of workaround. If this is your case, leave a comment.

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: seconds. Working set, committed, memory utilization

Long story short: you need to add the SQL service account to Lock Pages in Memory and set a max SQL memory configuration.

The problem is when SQL Server stops working but don’t attempt to failover. There are a few scenarios where this error applies but most of the cases SQL doesn’t not accept new connections for short periods (5 seconds.)  The problem can be identified in SQL Error Log with the following entry:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 157348, committed (KB): 444456, memory utilization: 35%.


This error is more visible in virtual server and server with more than 128 GB in RAM. This affects so far SQL Server 2012 and 2014 Standard or Enterprise Editions.

 Root Cause

Problem happened because Windows paged out SQL server memory. KB 918483 discusses the issue and the resolution.


You will need to work with Enterprise Windows or Wintel team to resolve the paging out issue. You can refer “How to troubleshoot this problem” of the “More information” section of the following article: How to reduce paging of buffer pool memory in the 64-bit version of SQL Server


You can grant the SQL service account is ‘DOMAIN\SA_SQLAccount_P’ Lock pages in memory user right to avoid the issue. Before you grant the privilege, you need to make sure SQL maximum memory is set such that there is enough memory left for the OS to work smoothly. This needs to be done in each node of the cluster.

To enable the lock pages in memory option

1. On the Start menu, click Run. In the Open box, type gpedit.msc.

2. On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.

3. Expand Security Settings, and then expand Local Policies. –> Lock Pages in Memory

Lock Pages in Memory

In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.

Adding user to Lock pages in memory
Adding user to Lock pages in memory

In the Select Users, Service Accounts, or Groups dialog box, add an account with privileges to run sqlservr.exe.

Log out and then log back in for this change to take effect.

SQL Server has now lock page in memory and it is registered in the SQL Error Log:



SQL Server memory is tricky when running in a virtual machine. Anybody can get the Page Out Memory error but it will become more and more frequent. The problem is when it goes down, not even monitoring tools can catch it as recovery time is two to five seconds. However, important SSIS, jobs, or request can occur during those seconds and it will reject all connections. The problem goes very serious when the application is not able to recover itself and SQL did not register any attempt of failover.

Fixing the problem with this relatively easy solution will not only improve the performance but also maintain a consistent database health. Make sure to configure the SQL Server max memory 80/20 to avoid any issue.



Enable lock pages

How to reduce paging of buffer pool memory in the 64-bit version of SQL Server

SQL Server Set Up Error The RPC Server is Unavailable

This error occurs at installation time when SQL is unable to authenticate the service accounts to start the services. This occurs with A domain accounts, B domain accounts, and local system accounts. This error stops all the installation of SQL Server and it says “The RPC server is unavailable.” The screen below shows how this error occurs.

SQL Error

I troubleshoot this error by changing the accounts to start the services with no luck. Also attempted with a valid domain_A\sa_account, domain_B\valid_account, and NT AUTHORITY\Local Account but none of them worked.

Windows Server by default has some policies enabled. In order to fix this error HP configured the following
1. Below marked FW rules should be enabled from group policy.

2. Network binding should be below order. Yours may change but the production/primary must be listed first.

3. Make sure DNS suffix entry should be correct order.

4. Execute below command if any issue found on Time synchronization

w32tm.exe /register
net stop w32time
net start w32time

After these changed were made, DBA team was able to proceed with the installation and the error “The RPC server is unavailable” disappear.

Technical Specifications
OS Version: Microsoft Windows Server 2012 R2 Standard x64
SQL Version: SQL Server Standard Edition 2014 x64
Memory: 16Gb
Processor: 4 CPUs
Two nodes Active-Passive Cluster.

SSRS Subscription Failure sending mail: The user or group name ‘domain\user’ is not recognized.Mail will not be resent.

When you’re getting the error above is because the user is no longer valid for Reporting Services.
Quick Fix –> Re-create the subscription.
Best Fix –> Reassign the subscriptions to a Service Account.

However, if you have several subscriptions it is not feasible to recreate all of them. Here it is how can you reassign these subscription to someone else.

1. Create a Service Account to assign it as owner of these subscriptions. This is because the Active Directory account set before is no longer valid (the employee left the company.) If you just use another user account and that user left the company you’ll have to do it over and over again. Account created: SA_REPORTS_P

2. Grant DBO permissions on ReportServer database to the new Service Account (SA_REPORTS_P)

3. Grant ALL permissions to the Service Account on Report Server Home directory. Make sure all the folder inherit permissions from parent, if not, individually check the permissions in each folder.

SSRS Permissions grant
SSRS Permissions grant

4. Find the OWNERID of the subcriptions that have an invalid user or group name. Run the query below in your ReportServer database and find those with the error.

--Query to get the Last Status of each Subcription and its OwenerID.
SELECT s.[SubscriptionID] -- Subscription ID
,s.[OwnerID] -- Report Owner
--,s.[Report_OID] -- Report ID
, c.Path -- Report Path
--,rs.ScheduleID as SQLJobName -- Name of Job on SQL Server
,s.[LastStatus] -- Status of last subscription execution.
,s.[Description] -- Description of the report subscription
,s.[EventType] -- Subscription type
,s.[LastRunTime] -- Last time subscription executed
--,s.[Parameters] -- Parameters used for subscription
,s.[DeliveryExtension] -- How to deliver the subscription
FROM [ReportServer].[dbo].[Subscriptions] as s left join dbo.Catalog as c
on c.ItemID = s.Report_OID left join dbo.ReportSchedule as rs
on rs.ReportID = s.Report_OID
order by s.LastStatus


5. Identify OwnerID for the Service Account Created (or whatever account you’re going to use). Run the query below to list the users.

SELECT [UserID],[UserName]
FROM ReportServer.dbo.Users

6. Replace the invalid OwnerID with a valid OwnerID. I use the Service Account ID.

UPDATE [Subscriptions]
SET [OwnerID] = '96C17DCA-42D6-44AE-B036-9EA1F870AEFD' -- New VALID OwnerID
WHERE [OwnerID] = 'EFBCD353-3745-41E4-B2BF-8C23198014AD' -- Old and invalid OwnerID

7. Verify the subscriptions were updated. Run the query below to see how the new user name is not in place.

--Query to get the Report Name and its Subcription Owner.
C.path AS ReportPath, AS ReportName,
u.username AS SubscriptionOwner
FROM dbo.ReportSchedule RS JOIN msdb.dbo.sysjobs jobs
ON CONVERT(varchar(36), RS.ScheduleID) =
INNER JOIN dbo.Subscriptions S
ON RS.SubscriptionID = S.SubscriptionID
INNER JOIN dbo.Catalog C
ON s.report_oid = C.itemid
INNER JOIN dbo.users u
ON s.ownerid = u.userid
order by u.username


The problem should be fixed at this point. Nevertheless, if you want to check if it really works, you can wait for the next subscription to run or invoke it manually. So, here you have a bonus on how to manually call a subscription.

A. In ReportServer, execute the query below and it will give you the SQL Job Name. Two Jobs (subcriptions) that were failing due to an invalid OwnerId are 36F500A6-58DA-4ACB-87AF-9947FEBB5797 and

--Query to get the SQL Job Name of each subscription in the server
C.path AS ReportPath, AS ReportName,
u.username AS SubscriptionOwner,
FROM dbo.ReportSchedule RS JOIN msdb.dbo.sysjobs jobs
ON CONVERT(varchar(36), RS.ScheduleID) =
	INNER JOIN dbo.Subscriptions S
ON RS.SubscriptionID = S.SubscriptionID
	INNER JOIN dbo.Catalog C
ON s.report_oid = C.itemid
	INNER JOIN dbo.users u
ON s.ownerid = u.userid
order by s.LastStatus


B. Execute the job manually with the script below.

EXEC msdb.dbo.sp_start_job @job_name = '36F500A6-58DA-4ACB-87AF-9947FEBB5797'

C. Validate the new status by executing the script in step A.

Veeam Failed to truncate transaction logs for SQL instances: MICROSOFT##WID. Possible reasons: lack of permissions, or transaction log corruption

This error occurs for several reason, but it can be fixed by applying a hotfix that replaces some libraries. However, follow the steps outlined in this KB article as is relevant to this type of errors, if none of them work out, then you’re in the right place!

NOTE: The hotfix I’ve uploaded is in PDF format but it is a ZIP file, wordpress doesn’t allow to upload ZIP files.

Short solution
– Download this ZIP file and replace them on your affected VM C:\Program Files\Veeam\Backup and Replication\VSS\VeeamGuestHelpres\. (rename it from PDF to ZIP)

Detailed solution
In my architecture we are working with a VM with the following software:
– SQL Version: Microsoft SQL Server 2014 Standard Edition (64-bit)
– OS Version: Windows Server 2012 Standard
– Veeam Backup & Replication

The error or warning shows a full text like this:
Unable to truncate SQL server transaction logs. Details: Failed to process ‘TruncateSQLLog’ command.
Failed to truncate transaction logs for SQL instances: MICROSOFT##WID. Possible reasons: lack of permissions, or transaction log corruption.

In your Veeam console you may see something like this:

Console warning
Console warning

To fix this, follow these steps:
1. Download the new files here (I got them directly from Veeam Support – make sure to rename it form pdf to zip)
2. Copy the zip file or extracted files to you VM.
3. Connect to the affected VM and find the path C:\Program Files\Veeam\Backup and Replication\VSS\VeeamGuestHelpres\. Where you’re going to see some libraries like VeeamVSSSupport*
4. Take a backup of those files
5. Replace the files with the new files. The new files size should be similar to this:
6. Execute the backup job in your Veeam Console

The issue will be fixed after you replace the old files for the new files.