SSAS 2012 logging feature improvement in SQL Server 2012 Service Pack 2 and SQL Server 2014 Service Pack 1

Applies to: SQL Server 2012 Analysis ServicesSQL Server 2014 Business IntelligenceSQL Server 2014 Business Intelligence

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 typeSQL Server Analysis Service Log improvement information detailLog File
Product VersionSQL Server Analysis Service Version informationThe SQL Server Analysis Service Log, and the Windows Application log
Processor InformationThe number of NUMA nodes available to the SQL Server Analysis ServiceThe SQL Server Analysis Service Log
Processor InformationThe number of Affinity Groups available to the SQL Server Analysis ServiceThe SQL Server Analysis Service Log
Processor InformationThe number of CPUs available to the SQL Server Analysis ServiceThe SQL Server Analysis Service Log
Processor InformationThe number of physical Cores available to the SQL Server Analysis ServiceThe SQL Server Analysis Service Log
Processor InformationThe number of logical Cores available to the SQL Server Analysis ServiceThe SQL Server Analysis Service Log
Memory InformationThe physical memory available to the SQL Server Analysis ServiceThe SQL Server Analysis Service Log
Connectivity InformationTCP IP protocol which the SQL Server Analysis Service listens to The SQL Server Analysis Service Log
Connectivity InformationThe Machine Name or Netbios name which is associated with the SQL Server Analysis ServiceThe SQL Server Analysis Service Log
Connectivity InformationThe TCP IP and port which the SQL Server Analysis Service listens onThe SQL Server Analysis Service Log
Connectivity InformationThe port and binding status to which the SQL Server Analysis Services binds toThe SQL Server Analysis Service Log
Blocking Event InformationThe 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.
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

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.