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>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: