The SQL Server Analysis Service Started and Then Stopped

Owned by Knowledge Assistant
Last updated: Jul 27, 2023 by Moriah Yorkey
2 min read
Description of Issue

When attempting to start a SQL analysis service on a server the following error is thrown: The SQL Server Analysis Services (MSSQLSERVER) service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

Context

  • SQL

  • Cubes

  • SaaS

  • AWS

  • Datacenter

  • OnPrem

  • EERP

Cause

Two files generated by the SQL Analysis Service have become corrupted at some point and will need to be be regenerated.

Resolution

  1. Connect to the server which the SQL Analysis Service is installed

    1. In EERP AWS that would be the Web Servers in your assigned pod

    2. In the Yarmouth Datacenter that is the Cubes server

    3. In On Premise, this is typically the database server

  2. Browse to the Server that the SQL Analysis Service is installed in

    1. This can be found by opening Services → right click on the SQL Analysis Service in question → Properties → Path to Executable will show the location

  3. Once you have browsed to this location:

    1. Copy the msmdsrv.ini and msmdsrv.bak files to the server desktop

    2. Delete original the msmdsrv.ini and msmdsrv.bak files in the Config folder

  4. Go back to Services and restart the SQL Analysis Service and those two files should regenerate automatically and the Analysis Service should remain running.

If this SQL Analysis Service is Case-Sensitive more steps are required

  1. Open SSMS on a server that has connection access to the SQL Analysis Service that you have just fixed.

  2. Click Connect → Analysis Services... → Select/Enter your SQL Analysis Service's name → right click on the Analysis Service within SSMS and click Properties → Language/Collation → enable Case-Sensitive → Ok

  3. Right click on the Analysis Service within SSMS and click Properties → Security → add relevant/necessary security groups needs for the analysis service.

    1. If unsure of what groups to add please compare with another server that is currently in use.

Note: if you need additional assistance, contact TSM support for resolution

Additional Information

Â