ConfigMgr Error – Error 2168851714 – SQL command failed
This afternoon I encountered an interesting issue with ConfigMgr that I have not seen before, ErrorCode = 2168851714 SQL command failed. This happened when trying to add to or change the content of the Configuration Manager database through the console window.
I got this error after every attempt
The full error:
ConfigMgr Error Object:
instance of SMS_ExtendedStatus
{
CauseInfo = “”;
Description = “SQL command failed: “;
ErrorCode = 2168851714;
File = “X:\bt\1216594\repo\src\SiteServer\SDK_Provider\SMSProv\sspfolder.cpp”;
Line = 766;
ObjectInfo = “Please check SMS Provider log file for details of the SQL errors”;
Operation = “PutInstance”;
ParameterInfo = “”;
ProviderName = “ExtnProv”;
SQLMessage = “[42000][9002][Microsoft][SQL Server Native Client 11.0][SQL Server]The transaction log for database ‘CM_XXX’ is full due to ‘LOG_BACKUP‘. : vSMS_Folder_insupddel”;
SQLSeverity = 17;
SQLStatus = 9002;
StatusCode = 2147749889;
};
Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlQueryException
The SMS Provider reported an error.
Stack Trace:
at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlResultObject.Put(ReportProgress progressReport)
at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlResultObject.Put()
at Microsoft.ConfigurationManagement.AdminConsole.PersonalFolders.NewFolderDialog.buttonOK_Click(Object sender, EventArgs e)
System.Management.ManagementException
Generic failure
Stack Trace:
at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlResultObject.Put(ReportProgress progressReport)
at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlResultObject.Put()
at Microsoft.ConfigurationManagement.AdminConsole.PersonalFolders.NewFolderDialog.buttonOK_Click(Object sender, EventArgs e)
The cause…
The key part of this error is highlighted above, its LOG_BACKUP. This is clearly a SQL database issue.
After many leads (that went no where) I have established the problem is that the transaction log is full on the SQL database.
Note: contrary popular belief, you cannot always assume that a transaction log is not full, just because the disk the logs are stored on, is not full. In the SQL database, its possible to set a ‘hard limit’ on the Transaction log size.
In my case the cause turned out to be the backup solution in place. When backing up the database, it was not successfully truncating the logs. The backups have been taking care of this for years but just recently, something happened and the result was this error.
The fix…
We have two fixes, the first is to get ConfigMgr back up and running.
To do this…
- To do this, logon to your SQL server and open SSMS
- Right click on your database, usually its called CM_[SITECODE]
- Click Properties
- Choose Options
- Change the Recovery Mode from Full to Simple
- Click OK
- Right click on your database, usually its called CM_[SITECODE]
- Click Tasks
- Click Shrink
- Click Files
- Choose File type as Log
- Under Shrink action choose Release unused space
- Click OK
- Right click on your database, usually its called CM_[SITECODE]
- Click Properties
- Choose Options
- Change the Recovery Mode from Simple to Full
At this point, if you return to the Configuration Manager console and retry the action you were attempting before, you should now be able to complete the action.
At this point, this is not a permanent fix. You need to get to the root cause of the problem.
This is where the second fix comes in. In my case, it was the backup job settings. Now that its correctly truncating the logs, this problem should be fully resolved.
Further SQL Consideration…
You may want to further consider the log file settings in SQL.
Right click on the ConfigMgr database, click Properties | Options | Files
In our example here we can see that the log file is Limited, to change the limit or make it unlimited, click the ellipses next to it.
From here you can then either choose the limit for you log file size, in MB. Or you can choose unlimited.
Click OK to all open windows.
I hope this post helps someone with this error, it took me a bit of time to work through all the possible causes so hopefully, you will find this and speed up your fix time!