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

--/* 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

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.

TRUNCATE Vs DELETE Q1. What are the difference between TRUNCATE command and DELETE command? Q2. Can TRUNCATE be rolled back like the DELETE command? Q3. What are the advantages of TRUNCATE command compare to DELETE? Q1.What are the difference between TRUNCATE command and DELETE command ? TRUNCATE : 1.Remove all pages from the table. You can’t use WHERE clause with TRUNCATE . 2.Deallocate the data pages in the table. 3.Faster than DELETE. 4.Reset the identity column. TRUNCATE is a DDL statement. So you need ALTER permissions for it. 5.Doesn’t fire trigger. 6.Acquire only page or table lock.. 7. TRUNCATE cannot be used with indexed views. 8. Drop all object’s statistics and leave the table empty. Zero pages are left in the table. DELETE: 1.Remove all pages from file if used without a WHERE clause. You can use WHERE clause with DELETE. ...

Top 10 essential SQL skills for data analysts, data engineers, and data scientists Photo by Carlos Muza on Unsplash It doesn’t matter how good you are at creating visualizations, extracting insights, or building complicated models; you need data in order to do those things. SQL enters the picture when it comes to extracting data from the database in order to work with it. Mastering the commonly used SQL concepts is essential to work effectively as a data analyst, data engineer, and data scientist in today's world. The following are the top 10 skills for mastering SQL: 1. Knowing SQL Fundamentals: Understanding the following keywords is essential to write common DML queries: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, WINDOW\RANK FUNCTIONS. 2. Understanding different real-world scenarios: Analyzing and understanding multiple domains and how to use SQL, like writing complex queries, manipulating data, pulling reports, and identifying patterns and t...

TSQL Masters

Search This Blog