Summary
As part of the SQL Server 2012 SP2 and SQL Server 2014 SP1 release, the SQL Server Analysis Service Logging feature is further improved as indicated in the following table.
| SQL Server Analysis Service Log improvement type | SQL Server Analysis Service Log improvement information detail | Log File |
|---|---|---|
| Product Version | SQL Server Analysis Service Version information | The SQL Server Analysis Service Log, and the Windows Application log |
| Processor Information | The number of NUMA nodes available to the SQL Server Analysis Service | The SQL Server Analysis Service Log |
| Processor Information | The number of Affinity Groups available to the SQL Server Analysis Service | The SQL Server Analysis Service Log |
| Processor Information | The number of CPUs available to the SQL Server Analysis Service | The SQL Server Analysis Service Log |
| Processor Information | The number of physical Cores available to the SQL Server Analysis Service | The SQL Server Analysis Service Log |
| Processor Information | The number of logical Cores available to the SQL Server Analysis Service | The SQL Server Analysis Service Log |
| Memory Information | The physical memory available to the SQL Server Analysis Service | The SQL Server Analysis Service Log |
| Connectivity Information | TCP IP protocol which the SQL Server Analysis Service listens to | The SQL Server Analysis Service Log |
| Connectivity Information | The Machine Name or Netbios name which is associated with the SQL Server Analysis Service | The SQL Server Analysis Service Log |
| Connectivity Information | The TCP IP and port which the SQL Server Analysis Service listens on | The SQL Server Analysis Service Log |
| Connectivity Information | The port and binding status to which the SQL Server Analysis Services binds to | The SQL Server Analysis Service Log |
| Blocking Event Information | The blocking event information including lock timeout event and deadlock event. If a lock timeout occurs, it logs the database id, the lock object type, and the lock type into the file. If a deadlock occurs, it logs the involved database id, the victim lock transaction id and the SPID which is rolled back. |
The SQL Server Analysis Service Log |
Resolution
After you apply this fix, the three phrases about deadlock information are changed from "database," "transaction ID," and "session ID" to "Database ID," "Session ID," and "SPID" in Msmdsrv.log. This change is made to maintain consistency with the words in Profiler.
Service pack information
To resolve this issue, obtain Service Pack for SQL Server.
- How to obtain the latest service pack for SQL Server 2014
- How to obtain the latest service pack for SQL Server 2012
For more information about SQL Server 2014 Service Pack 1 (SP1), see bugs that are fixed in SQL Server 2014 Service Pack 1 .
Cumulative update information
This issue was first fixed in the following cumulative update of SQL Server.
Cumulative Update 1 for SQL Server 2012 SP2 /en-us/help/2976982
About cumulative updates for SQL Server
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
More Information
The sample logging for SQL Server Analysis Services log file after you apply SSAS 2012 SP2 and Cumulative Update 1 is as follows:........................
........................
(<DateTime>) Message: Started listening on TCPIP: Status = ListenToPortSuccess, ComputerName = TestMachineName, PhysicalNetBIOSName = TestMachineName, ListenOnIP = [::]:2383, Port = 2383, ProtocolType = IP6 (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210040)
(<DateTime>) Message: Started listening on TCPIP: Status = ListenToPortSuccess, ComputerName = TestMachineName, PhysicalNetBIOSName = TestMachineName, ListenOnIP = 0.0.0.0:2383, Port = 2383, ProtocolType = IP4 (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210040)
(<DateTime>) Message: Processor Information: NumberOfNumaNodes = 2, NumberOfAffinityGroups = 1, NumberOfCPUs = 2, NumberOfPhysicalCores = 12, NumberOfLogicalCores = 12, PhysicalMemoryAvailable = 15 GB (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210041)
(<DateTime>) Message: Service started. Microsoft SQL Server Analysis Services 64 Bit Enterprise (x64) SP2 11.0.5049.0. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)
(<DateTime>) Message: Lock manager detected a deadlock in Database ID 'DataBaseID'. Chosen victim lock transaction ID is AEE4922D-0753-4A74-89A8-B776A08B2F2B and SPID is XXXXX. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x4121003E)
(<DateTime>) Message: Lock manager detected a lock timeout in database 'DataBaseID' for object of type 100002 and lock type Commit Write. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x4121003D) Note The SQL Server Analysis Services log is a text file that is typically named Msmdsrv.log. The default location is the program files folder for the instance of Analysis Services, such as the following:
C:\Program Files\Microsoft SQL Server\MSAS11.<InstanceName>\OLAP\Log
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.