Unable to stop a job running "sql server analysis services command"

• patamons

  Old Hand

  Points: 341

  More actions

  September 26, 2012 at 2:38 am

  #267243

  Hello,

  From time to time, a SQL Server job with a step type "sql server analysis services command" is running for ever.

  If I try to stop it, SQL Server returns "job stopped successfully" but the job is still shown as running and cannot be restarted (job already running). The only way to really stop it is to restart the SQL Server agent. Is there a way to really stop this Sql server analysis command ?

• patamons

  Old Hand

  Points: 341

  More actions

  May 30, 2013 at 2:50 am

  #1619591

  The solution is here : http://sqlmag.com/reporting/managing-analysis-services-activity-14-feb-2010

  To show running queries, execute this MDX query :

  SELECT * FROM $SYSTEM.DISCOVER_COMMANDS

  order by command_start_time

  GO

  SELECT * FROM $SYSTEM.DISCOVER_SESSIONS

  GO

  SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS

  GO

  Find the forever running query using command_start_time and COMMAND_TEXT.

  And then kill the query started by the SQL job using this XMLA query :

  <Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <ConnectionID>xxxx</ConnectionID>

  </Cancel>

  Thanks a lot Derek !