Skip to main content

System tables for Policy based Management



 System tables for Policy based Management

Metadata: 



Database: msdb

Db objects:

     msdb .dbo.syspolicy_conditions


syspolicy_conditions_internal

syspolicy_execution_internal

syspolicy_facet_events

syspolicy_management_facets

syspolicy_policies

syspolicy_policies_internal

syspolicy_policy_categories

syspolicy_policy_execution_history

syspolicy_policy_execution_history_details

syspolicy_policy_execution_history_details_internal

syspolicy_policy_execution_history_internal

syspolicy_target_sets

syspolicy_target_sets_internal








Querying meta data:



USE MSDB

GO



--/* What policies exist on the server? */

SELECT p.name AS 'policy_name',

       p.is_enabled,

       p.execution_mode,

       c.name AS 'condition_name',

       c.facet,

       p.date_created

FROM syspolicy_policies p

     INNER JOIN syspolicy_conditions c

     ON p.condition_id = c.condition_id

ORDER  BY p.name





--/* Execution history of policy violations */

SELECT

            h.history_id,

            pp.name  'policy name',

            pp.execution_mode,

            h.result,

            h.start_date,

            h.end_date,

            d.target_query_expression,

            CAST(d.result_detail AS XML) 'result'

FROM syspolicy_policies AS pp

INNER JOIN syspolicy_policy_execution_history  h

ON h.policy_id = pp.policy_id

INNER JOIN syspolicy_policy_execution_history_details  d

ON d.history_id = h.history_id

ORDER  BY h.history_id

Comments

Popular posts from this blog

  Microsoft Power BI- Solve Problem  "Map and Filled Map Visuals aren't Enabled"   When you try to use Map or Filled Map from Visuals for the 1st time , you may get the following warning : "Map and Filled Map Visuals are disabled". This means you need to go to Options and have to enable these Visuals. Go to File ->Options and Settings-> Options -> Security-> Enable Map and Filled Map Visuals. (After this, in some cases you may need to close and reopen Power BI desktop) Now Go to Add Visual Then double click on Map or Filled Map. After that, you will be able to add Location, Legends etc. without any issues.

Difference between Clustered Index Scan and Index Scan in SQL Server

1.Clustered Index Scan ✅ What it is: A Clustered Index Scan means SQL Server is scanning every row of the entire table,  essentially performing a "table scan" because the clustered index is the table itself.  📌 When it happens: No WHERE clause or very broad filter (low selectivity). Join/filter columns aren’t indexed selectively. Query needs most or all columns (covering queries). 🧠 Performance Insight: Can be expensive for large tables. Usually appears when SQL Server can’t seek and needs to read all data. Example:    Table: CREATE TABLE dbo.Sales (     SaleID INT NOT NULL IDENTITY,     SaleDate DATE NOT NULL,     Quantity INT NOT NULL, PRIMARY KEY (SaleID) ) Query: SELECT * FROM dbo.Sales WHERE  SaleDate  BETWEEN '2025-02-04' AND '2025-02-10' 2. Index Scan (Non-Clustered) ✅ What it is: A Non-Clustered Index Scan scans all rows in a specific non-clustered index( not the full table) 📌 When it happens: The query filters on a ...

SQL Server script Error: Insufficient result space to convert uniqueidentifier value to char.

Error:    G etting below error while trying to copy data from one table to another: Msg 8170, Level 16, State 2, Line 1004  Insufficient result space to convert uniqueidentifier value to char.   Fix: UserID column which would be having Unique identifier data require more space (36 character). Size is specified as 15 here. To fix it just specify 36 as varchar length or specify Uniqueidentifier as data type for UserID  column.