failed to retrieve data for this request .... errors in the metadata manager an error occurred when loading the admin database role from
Scenario
My most recent encounter with SSAS DB corruption surfaced slightly differently than normal. Thought I would share to benefit others who may have experienced or is experiencing the issue as I write.
We have a situation, where:
- SSAS Service, hosting numerous multidimensional databases, goes into an unresponsive state
- When we restart SSAS service and try to look into the properties of the instances, connecting to SSAS from SSMS, we get the following error:
Analysis Services issue; Getting an error “failed to retrieve data for this request. (Microsoft.SQLServer.Management.SDK.SFC) Additional information: errors in the metadata manager an error occurred when loading the admin database role from \\?\<data location>\XXXX.0.db\XXXX.0.role.xml (Microsoft.AnalysisServices.xmla)” when he logs in to the instance and trying to pull properties of this instance;
Troubleshooting
Based on the error message, I had the following theories:
- We have a corruption issue in the DB file, showing up on the error message.
- SSAS is in a state that I retained the information somewhere, and trying to read the information, upon start up.
- Master.vmp file is corrupted
Understanding Master.vmp file
Master.vmp file is located under the Data folder, we configure for our SSAS DB, typically located at C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data (for SSAS 2012), unless you have a custom configuration. This file is the master version map that contains the GUIDS for all of the objects and the version of each object that currently exists on the server.
When the server starts, it looks at the master.vmp file and attempts to find each of the objects referenced in the file. So we can have following possible corruption scenarios:
- If there are objects in the data directory that don't have a corresponding GUID in master.vmp, those objects are deleted.
- If the master.vmp doesn't exist when the server starts, the server creates a new master.vmp file and deletes all of the objects in the data directory.
- If you have deleted your DB but the master.vmp file keeps a record of the GUID for the DB, this can also result in an inconsistency.
Check your log files
Keeping in line with my theory, I first checked the event log.
The event viewer shows this message:
Errors in the metadata manager. An error occurred when loading the Package Detail cube, from the file, '\\?\<data location>\xxxx.0.db\xxxx.cub.xml'.
This message also pointed me to fact that we are likely having a corruption issue, either on DB or on Master.vmp file.
I haven't checked the msmdsrv.log file, however I am pretty sure I would see the same error. I admit, it's worth looking into this file for more information.
More clues
At this stage, we discussed out another clue. Our clue to this scenario was the key towards our troubleshooting path: the DB, in the error message, was deleted and is no longer needed.
Connecting the dots
At this stage, I just started hunting for the missing DB.
1. On the Data folder, I find the folder for the DB, which I know is not supposed to be there, because we have deleted it from the SSMS.
2. I also see the <dbname>.x.db.xml file, which is the metadata file for the missing SSAS DB
3. I then opened the <dbname>.x.db.xml file, in notepad, located the object GUID for the DB object, an entry from <dbname>.x.db.xml file. [i.e. Adventure Works SSAS DB file name is Adventure Works DW.1.db.xml]
It would look like this: <ObjectID>D9B263AD-2E34-4C90-BC95-384CD15A48BC</ObjectID> for Adventure Works SSAS DB, in my machine.
4. Then searched master.vmp file for the same GUID
5. Stopped SSAS, removed the entry for this GUID, from master.vmp since we know that the DB is deleted so this entry should not be there any more. [Please take a backup of this file to avoid un-intended circumstances]
The xml node for the entry (which we removed), would look like below:
<VersionMapEntry><ObjectId>D9B263AD-2E34-4C90-BC95-384CD15A48BC</ObjectId><Version>1</Version><ObjectLastUpdated>130509560009251999</ObjectLastUpdated></VersionMapEntry>
<ObjectId>D9B263AD-2E34-4C90-BC95-384CD15A48BC</ObjectId> ---> This refers to the DB object for example
6. Started SSAS DB
Now we are able to see the DBs again, when connected to SSAS from SSMS
Conclusion
Since SSAS works with the file system, if the files under Data folder, are in a locked state, for some reason, when we try to write on those file, we may have inconsistent situation, which may lead to DB corruptions. If you suspect any such situation, please check the master.vmp file or the xxxx.db.xml file for the DB (or object) in question for clues. Application event log or msmdsrv.log file also may give you more information about the error condition. Understand what role master.vmp plays will help avoid or resolve some of the tricky DB corruption issues.
References
Tampering master.vmp file may result in losing all Analysis Services Databases
How to restore SSAS Database, tabular mode or multidimensional mode.