Useful Scripts

SQL Server:

• Change job schedule and ownership

USE [msdb]

EXECUTE [dbo].[usp_change_job_owner] ‘(jobname)’, ‘(userid)’

USE [msdb]

EXECUTE dbo.usp_change_job_schedule_owner

‘(job name)’,'(schedulename)’,'(userid)’

Analysis Server

Model Data Dictionary

• Model All fields

SELECT

[CATALOG_NAME] as [DATABASE]

, [CUBE_NAME] AS [CUBE]

,[DIMENSION_UNIQUE_NAME] AS [DIMENSION]

, HIERARCHY_DISPLAY_FOLDER AS [FOLDER]

,HIERARCHY_CAPTION AS [DIMENSION ATTRIBUTE]

, HIERARCHY_IS_VISIBLE AS [VISIBLE]

FROM $system.MDSchema_hierarchies

WHERE [CUBE_NAME] =’Model’

• model measures

select

[catalog_NAME]

,[MEASUREGROUP_NAME]

,[measure_name]

,[expression]

,[measure_is_visible]

from $System.MDSCHEMA_MEASURES

where cube_name = ‘Model’

• Model Hierarchies

SELECT

*

FROM $system.MDSchema_hierarchies

WHERE CUBE_NAME =’Model’

• Model Relationships

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

WHERE OBJECT_TYPE = ‘RELATIONSHIP’

Monitoring AS server user activities

Select * from $System.discover_sessions

order by session_used_memory desc

Select * from $System.discover_locks

order by lock_object_id

select * from $System.discover_object_memory_usage

–order by object_Parent_path

order by object_memory_nonshrinkable desc

select * from $System.discover_memoryusage

SELECT * FROM $SYSTEM.DISCOVER_COMMANDS

SELECT * FROM $SYSTEM.DISCOVER_SESSIONS

order by session_spid

GO

SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS

GO

<Cancel xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;

<SPID>123456</SPID>

<CancelAssociated>1</CancelAssociated>

</Cancel>