SQL Server DBA Script Files:
Thursday 2 June 2022
Thursday 16 August 2018
MS SQL Server Database Dependence Tree using DMV sys.sql_expression_dependencies
-- By Steven Rao
-- On Aug 16, 2018
-- Given a single database object (such as stored procedure, function, view, table), list the dependance tree view of all depedent objects
-- Recusively looking inside DMV sys.sql_expression_dependencies
-- Based on
-- https://www.sqlservercentral.com/Forums/Topic1874321-3077-1.aspx
declare @schemaName varchar(128), @objectName varchar(128), @objectID int
select @schemaName='dbo', @objectName='sp_SRC_MAIN'
--select @schemaName='dbo', @objectName='mp_MPS53B3_AWD_MAIN'
select @objectID=OBJECT_ID(@objectName)
-- debug and verify ObjectID
--select @schemaName as [@schemaName], @objectName as [@objectName], @objectID as [@objectID]
if object_id('tempdb..#base_objects') is not null
drop table #base_objects
create table #base_objects
(schemaname varchar(128)
,objectname varchar(128)
,objectID int
)
insert into #base_objects
select @schemaName, @objectName, @objectID
;
--------------------------------------------------------------------------------------
-- extend the expression dependance with a more reliable referenced ID
-- (ignore cross database case)
--------------------------------------------------------------------------------------
with DP as
(
-- referencing_id: parent
-- referenced_id: child
-- need to rely on
-- rely on referencing_id and referenced_entity_name
--select * from sys.sql_expression_dependencies where referencing_id is null or referenced_entity_name is null
--select * from sys.sql_expression_dependencies where referenced_id is null
select
*
-- this could give us more depedence data becasue referenced_id could be null
,isnull(referenced_id, OBJECT_ID(referenced_entity_name)) as ReferencedID
from sys.sql_expression_dependencies
)
-- this is CTE to recursivly serach for object dependence
,
DepTree
(
top_level_name, referenced_id, referenced_schema, referenced_name, referencing_id, referencing_schema, referencing_name, NestLevel, callstack, typedesc
)
as
(
-- initialization of the depedence tree
select
schema_name(o.schema_id) + '.' + o.name as top_level_name
, o.object_id as referenced_id
, schema_name(o.schema_id) as referenced_schema
, o.name as referenced_name
, o.object_id as referencing_id
, schema_name(o.schema_id) as referencing_schema
, o.name as referencing_name
, 0 as NestLevel
, cast ('|' + schema_name(o.schema_id) + '.' + o.name + '|' as nvarchar(max)) as callstack
, o.type_desc as typedesc
from sys.objects o
inner join #base_objects ro
on o.object_id=ro.objectID
--ro.schemaname = schema_name(o.schema_id)
--and ro.objectname = o.name
-- using ID for better matching, this can fix issue with 'sp_SRC_MAIN'
--SCHEMA_ID(ro.schemaname)=o.schema_id
--and OBJECT_ID(ro.objectname) = o.object_id
-- recursive expansion of the depedence tree
union all
SELECT
r.top_level_name
, ref.referenced_id
, ref.referenced_schema
, ref.referenced_name
, ref.referencing_id
, ref.referencing_schema
-- Steven Rao
-- is there a way to show the intermediate parent instead of the top level ??
, ref.referencing_name
--, ref.objectname
, ref.NestLevel
, callstack + ref.objectname + '|' as callstack
, cast(ref.typedesc as nvarchar(60)) as typedesc
FROM DP d1
INNER JOIN DepTree r
ON d1.referencing_id = r.referenced_id
outer apply (
select
ob2.object_id as referenced_id
, schema_name(ob2.schema_id) as referenced_schema
, ob1.name as referenced_name
, schema_name(ob2.schema_id) + '.' + ob2.name as objectname
, ob1.object_id as referencing_id
, schema_name(ob1.schema_id) as referencing_schema
, ob1.name as referencing_name
, NestLevel + 2 as NestLevel
, cast(ob2.type_desc as nvarchar(60)) as typedesc
from sys.synonyms sy1
inner join sys.objects ob1
on ob1.object_id = sy1.object_id
inner join sys.objects ob2
on '[' + schema_name(ob2.schema_id) + '].[' + ob2.name + ']' = sy1.base_object_name
--OBJECT_ID(ob2.name)=OBJECT_ID(sy1.base_object_name)
where --sy1.object_id = d1.referenced_id
sy1.object_id = d1.ReferencedID
union all
select
--d1.referenced_id
d1.ReferencedID as referenced_id
, schema_name(ob1.schema_id) as referenced_schema
, ob1.name as referenced_name
, schema_name(ob1.schema_id) + '.' + ob1.name as objectname
, r.referencing_id
, r.referencing_schema
, r.referencing_name
, NestLevel + 1 as NestLevel
, cast(ob1.type_desc as nvarchar(60)) as typedesc
from sys.objects ob1
where ob1.object_id = d1.ReferencedID
union all
select
--d1.referenced_id
d1.ReferencedID as referenced_id
, schema_name(ty1.schema_id) as referenced_schema
, ty1.name as referenced_name
, schema_name(ty1.schema_id) + '.' + ty1.name as objectname
, r.referencing_id
, r.referencing_schema
, r.referencing_name
, NestLevel + 1 as NestLevel
, cast(d1.referenced_class_desc as nvarchar(60)) as typedesc
from sys.table_types ty1
where ty1.user_type_id = ReferencedID
) ref
where --------------------------------------------
-- need to aviod infinity loop!!
--------------------------------------------
callstack not like '%|' + ref.objectname + '|%'
-- can we use not in clasue here??
)
select
NestLevel
,referenced_schema as ReferencedSchema
,referenced_name as ReferencedObjectName
-- more user friendly display
-- remove first and last |, then replace | with "<="
,replace(left(right(callstack, len(callstack)-1),len(callstack)-2), '|', ' <= ') as ReferencingStack
,typedesc as ReferencingType
/*
-- already shows up in the referencing stack
--,top_level_name as RootObjectName
,referencing_schema
,referencing_name
,referencing_id
,referenced_id
*/
from DepTree dt
where 1=1
--and NestLevel=0
-- ignore top leve which contains exactly one row for self reference
and NestLevel > 0
-------------------------------------------------------------------------------
-- Case 1
-- Stored Procedure Dependence only
and typedesc in ('SQL_STORED_PROCEDURE')
-------------------------------------------------------------------------------
-- Case 2
-- stored procedure and table dependence
--and typedesc in ('SQL_STORED_PROCEDURE', 'USER_TABLE')
-------------------------------------------------------------------------------
-- Case 3
-- all dependence
--and typedesc like 'SQL%' or typedesc in ('USER_TABLE')
-------------------------------------------------------------------------------
order by 1,2,5,3,4
option (maxrecursion 5000);
-- On Aug 16, 2018
-- Given a single database object (such as stored procedure, function, view, table), list the dependance tree view of all depedent objects
-- Recusively looking inside DMV sys.sql_expression_dependencies
-- Based on
-- https://www.sqlservercentral.com/Forums/Topic1874321-3077-1.aspx
declare @schemaName varchar(128), @objectName varchar(128), @objectID int
select @schemaName='dbo', @objectName='sp_SRC_MAIN'
--select @schemaName='dbo', @objectName='mp_MPS53B3_AWD_MAIN'
select @objectID=OBJECT_ID(@objectName)
-- debug and verify ObjectID
--select @schemaName as [@schemaName], @objectName as [@objectName], @objectID as [@objectID]
if object_id('tempdb..#base_objects') is not null
drop table #base_objects
create table #base_objects
(schemaname varchar(128)
,objectname varchar(128)
,objectID int
)
insert into #base_objects
select @schemaName, @objectName, @objectID
;
--------------------------------------------------------------------------------------
-- extend the expression dependance with a more reliable referenced ID
-- (ignore cross database case)
--------------------------------------------------------------------------------------
with DP as
(
-- referencing_id: parent
-- referenced_id: child
-- need to rely on
-- rely on referencing_id and referenced_entity_name
--select * from sys.sql_expression_dependencies where referencing_id is null or referenced_entity_name is null
--select * from sys.sql_expression_dependencies where referenced_id is null
select
*
-- this could give us more depedence data becasue referenced_id could be null
,isnull(referenced_id, OBJECT_ID(referenced_entity_name)) as ReferencedID
from sys.sql_expression_dependencies
)
-- this is CTE to recursivly serach for object dependence
,
DepTree
(
top_level_name, referenced_id, referenced_schema, referenced_name, referencing_id, referencing_schema, referencing_name, NestLevel, callstack, typedesc
)
as
(
-- initialization of the depedence tree
select
schema_name(o.schema_id) + '.' + o.name as top_level_name
, o.object_id as referenced_id
, schema_name(o.schema_id) as referenced_schema
, o.name as referenced_name
, o.object_id as referencing_id
, schema_name(o.schema_id) as referencing_schema
, o.name as referencing_name
, 0 as NestLevel
, cast ('|' + schema_name(o.schema_id) + '.' + o.name + '|' as nvarchar(max)) as callstack
, o.type_desc as typedesc
from sys.objects o
inner join #base_objects ro
on o.object_id=ro.objectID
--ro.schemaname = schema_name(o.schema_id)
--and ro.objectname = o.name
-- using ID for better matching, this can fix issue with 'sp_SRC_MAIN'
--SCHEMA_ID(ro.schemaname)=o.schema_id
--and OBJECT_ID(ro.objectname) = o.object_id
-- recursive expansion of the depedence tree
union all
SELECT
r.top_level_name
, ref.referenced_id
, ref.referenced_schema
, ref.referenced_name
, ref.referencing_id
, ref.referencing_schema
-- Steven Rao
-- is there a way to show the intermediate parent instead of the top level ??
, ref.referencing_name
--, ref.objectname
, ref.NestLevel
, callstack + ref.objectname + '|' as callstack
, cast(ref.typedesc as nvarchar(60)) as typedesc
FROM DP d1
INNER JOIN DepTree r
ON d1.referencing_id = r.referenced_id
outer apply (
select
ob2.object_id as referenced_id
, schema_name(ob2.schema_id) as referenced_schema
, ob1.name as referenced_name
, schema_name(ob2.schema_id) + '.' + ob2.name as objectname
, ob1.object_id as referencing_id
, schema_name(ob1.schema_id) as referencing_schema
, ob1.name as referencing_name
, NestLevel + 2 as NestLevel
, cast(ob2.type_desc as nvarchar(60)) as typedesc
from sys.synonyms sy1
inner join sys.objects ob1
on ob1.object_id = sy1.object_id
inner join sys.objects ob2
on '[' + schema_name(ob2.schema_id) + '].[' + ob2.name + ']' = sy1.base_object_name
--OBJECT_ID(ob2.name)=OBJECT_ID(sy1.base_object_name)
where --sy1.object_id = d1.referenced_id
sy1.object_id = d1.ReferencedID
union all
select
--d1.referenced_id
d1.ReferencedID as referenced_id
, schema_name(ob1.schema_id) as referenced_schema
, ob1.name as referenced_name
, schema_name(ob1.schema_id) + '.' + ob1.name as objectname
, r.referencing_id
, r.referencing_schema
, r.referencing_name
, NestLevel + 1 as NestLevel
, cast(ob1.type_desc as nvarchar(60)) as typedesc
from sys.objects ob1
where ob1.object_id = d1.ReferencedID
union all
select
--d1.referenced_id
d1.ReferencedID as referenced_id
, schema_name(ty1.schema_id) as referenced_schema
, ty1.name as referenced_name
, schema_name(ty1.schema_id) + '.' + ty1.name as objectname
, r.referencing_id
, r.referencing_schema
, r.referencing_name
, NestLevel + 1 as NestLevel
, cast(d1.referenced_class_desc as nvarchar(60)) as typedesc
from sys.table_types ty1
where ty1.user_type_id = ReferencedID
) ref
where --------------------------------------------
-- need to aviod infinity loop!!
--------------------------------------------
callstack not like '%|' + ref.objectname + '|%'
-- can we use not in clasue here??
)
select
NestLevel
,referenced_schema as ReferencedSchema
,referenced_name as ReferencedObjectName
-- more user friendly display
-- remove first and last |, then replace | with "<="
,replace(left(right(callstack, len(callstack)-1),len(callstack)-2), '|', ' <= ') as ReferencingStack
,typedesc as ReferencingType
/*
-- already shows up in the referencing stack
--,top_level_name as RootObjectName
,referencing_schema
,referencing_name
,referencing_id
,referenced_id
*/
from DepTree dt
where 1=1
--and NestLevel=0
-- ignore top leve which contains exactly one row for self reference
and NestLevel > 0
-------------------------------------------------------------------------------
-- Case 1
-- Stored Procedure Dependence only
and typedesc in ('SQL_STORED_PROCEDURE')
-------------------------------------------------------------------------------
-- Case 2
-- stored procedure and table dependence
--and typedesc in ('SQL_STORED_PROCEDURE', 'USER_TABLE')
-------------------------------------------------------------------------------
-- Case 3
-- all dependence
--and typedesc like 'SQL%' or typedesc in ('USER_TABLE')
-------------------------------------------------------------------------------
order by 1,2,5,3,4
option (maxrecursion 5000);
Saturday 11 November 2017
my publication, certification
1) Badges - IBM Big Data University - Cognitive Class
Python for Data Science
IBM Blockchain Essentials
https://www.youracclaim.com/badges/d7cf2864-b2c4-4a24-964e-e076f4f61e01
Machine Learning with R - Level 1
Hadoop Data Access - Level 2
https://www.youracclaim.com/badges/043eea69-78c5-4f64-a272-30dd85f8aa47
Spark - Level 2
https://www.youracclaim.com/badges/8bf56a1d-e26e-4cd5-b86b-cf6c95edd711
Spark - Level 1
https://www.youracclaim.com/badges/3bb9bccc-abb1-4d27-9065-b92e90f3992c
Hadoop Foundations - Level 2
https://www.youracclaim.com/badges/26e8bcfb-2d4f-4032-b643-abdf9c3d2642
Hadoop Data Access - Level 1
https://www.youracclaim.com/badges/c958032b-f950-4a2d-808a-c64c4ea107a6
Hadoop Programming - Level 1
https://www.youracclaim.com/badges/2e7e1c3d-e41c-4200-928c-51f15d3a304a
Hadoop Administration - Level 1
https://www.youracclaim.com/badges/4d136b2a-2ef3-449f-96b2-e608f88b8506
Hadoop Foundations - Level 1
https://www.youracclaim.com/badges/30fbaa56-be31-425b-a48f-1c750dbba2dc
2) Pulications - Simple Talk Publishing -SQL Server Central
Enable/Disable a table to be read only on the fly
http://www.sqlservercentral.com/articles/read+only/100618/
Simple and efficient way to split string with separator
http://www.sqlservercentral.com/scripts/T-SQL/100014/
Dynamically Truncate a Partitioned Table
http://www.sqlservercentral.com/articles/partition+switch/100013/
Saturday 11 March 2017
MS Dynamics CRM Metadata query sample
The following is a handy query example to look for entity/attribute/option set lookup value:
select
e.Name as Entity
,os.IsGlobal
,a.Name as Attribute
,aop.DisplayOrder
,aop.Value as OptionValue
,ol.Label as OptionLabel
,osl.Label as OptionSetLabel
,al.Label as AttributeLabel
,el.Label as EntityLabel
,e.IsAudited as EntityIsAudited
,os.Name as OptionSet
,e.IsAuditEnabled as EntityIsAuditEnabled
,al.Label as AttributeLable
,a.IsAuditEnabled as AttributeIsAuditEnabled
,atp.Description as AttributType
--,a.*
--,ol.*
--,al.*
from MetadataSchema.Entity e
inner join MetadataSchema.LocalizedLabel el on el.ObjectId=e.EntityId and el.LanguageId=1033 and el.ObjectColumnName='LocalizedName' and year(el.OverwriteTime)=1900
inner join MetadataSchema.Attribute a on a.EntityId=e.EntityId and a.IsCustomField=1 and year(a.OverwriteTime)=1900
inner join MetadataSchema.LocalizedLabel al on al.ObjectId=a.AttributeId and al.LanguageId=1033 and al.ObjectColumnName='DisplayName' and year(al.OverwriteTime)=1900
inner join MetadataSchema.AttributeTypes atp on atp.AttributeTypeId=a.AttributeTypeId
inner join MetaDataSchema.OptionSet os on os.OptionSetId=a.OptionSetId and os.IsCustomOptionSet=1
inner join MetadataSchema.LocalizedLabel osl on osl.ObjectId=os.OptionSetId and osl.LanguageId=1033 and osl.ObjectColumnName='DisplayName' and year(osl.OverwriteTime)=1900
inner join MetadataSchema.AttributePicklistValue aop on aop.OptionSetId=os.OptionSetId and year(aop.OverwriteTime)=1900
inner join MetaDataSchema.LocalizedLabel ol on ol.ObjectId=aop.AttributePicklistValueId and ol.ObjectColumnName='DisplayName' and year(ol.OverwriteTime)=1900 and ol.LanguageId=1033
where year(e.OverwriteTime)=1900
--and e.Name='new_entity'
and e.Name='opportunity'
--and e.IsCustomEntity=1
order by 1,2 desc,3,4
year(...)=1900 can be replaced by =0
select
e.Name as Entity
,os.IsGlobal
,a.Name as Attribute
,aop.DisplayOrder
,aop.Value as OptionValue
,ol.Label as OptionLabel
,osl.Label as OptionSetLabel
,al.Label as AttributeLabel
,el.Label as EntityLabel
,e.IsAudited as EntityIsAudited
,os.Name as OptionSet
,e.IsAuditEnabled as EntityIsAuditEnabled
,al.Label as AttributeLable
,a.IsAuditEnabled as AttributeIsAuditEnabled
,atp.Description as AttributType
--,a.*
--,ol.*
--,al.*
from MetadataSchema.Entity e
inner join MetadataSchema.LocalizedLabel el on el.ObjectId=e.EntityId and el.LanguageId=1033 and el.ObjectColumnName='LocalizedName' and year(el.OverwriteTime)=1900
inner join MetadataSchema.Attribute a on a.EntityId=e.EntityId and a.IsCustomField=1 and year(a.OverwriteTime)=1900
inner join MetadataSchema.LocalizedLabel al on al.ObjectId=a.AttributeId and al.LanguageId=1033 and al.ObjectColumnName='DisplayName' and year(al.OverwriteTime)=1900
inner join MetadataSchema.AttributeTypes atp on atp.AttributeTypeId=a.AttributeTypeId
inner join MetaDataSchema.OptionSet os on os.OptionSetId=a.OptionSetId and os.IsCustomOptionSet=1
inner join MetadataSchema.LocalizedLabel osl on osl.ObjectId=os.OptionSetId and osl.LanguageId=1033 and osl.ObjectColumnName='DisplayName' and year(osl.OverwriteTime)=1900
inner join MetadataSchema.AttributePicklistValue aop on aop.OptionSetId=os.OptionSetId and year(aop.OverwriteTime)=1900
inner join MetaDataSchema.LocalizedLabel ol on ol.ObjectId=aop.AttributePicklistValueId and ol.ObjectColumnName='DisplayName' and year(ol.OverwriteTime)=1900 and ol.LanguageId=1033
where year(e.OverwriteTime)=1900
--and e.Name='new_entity'
and e.Name='opportunity'
--and e.IsCustomEntity=1
order by 1,2 desc,3,4
year(...)=1900 can be replaced by =0
Thursday 2 February 2017
T-SQL to clean up PrincipleObjectAccess table of Microsoft Dynamics CRM
-- https://support.microsoft.com/en-us/help/2664150/how-to-control-principalobjectaccess-table-growth-in-microsoft-dynamics-crm-2011
-- ??
--Delete from SubscriptionTrackingDeletedObject where TimeStamp <= convert(timestamp, 55527527);
BEGIN TRY
BEGIN TRAN t1
IF NOT EXISTS (SELECT * FROM sys.sysobjects
WHERE id = object_id(N'[dbo].[ToDeletePOAEntries]')
AND ObjectProperty(id, N'IsUserTable') = 1)
create table ToDeletePoaEntries
(
ObjectId uniqueidentifier,
Otc int
)
IF NOT EXISTS (SELECT * FROM sys.sysindexes si
INNER JOIN sys.sysobjects so ON si.id = so.id
WHERE so.id = OBJECT_ID(N'[dbo].[ToDeletePoaEntries]')
AND OBJECTPROPERTY(so.id, N'IsUserTable') = 1
AND si.name LIKE '%mainindex%')
CREATE UNIQUE NONCLUSTERED INDEX [mainindex] ON [dbo].[ToDeletePoaEntries]
(
[ObjectId] ASC,
[Otc] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
----- Insert records to be deleted in ToDeletePoaEntries
-- go through all user-owned entities which are not replicated and don't support duplicate detection
declare entity_cursor cursor local FORWARD_ONLY READ_ONLY
for select distinct e.ObjectTypeCode, e.BaseTableName, a.PhysicalName from EntityView e
inner join AttributeView a on e.EntityId = a.EntityId and a.IsPKAttribute = 1
where e.IsReplicated = 0 and e.IsDuplicateCheckSupported = 0 and e.OwnershipTypeMask & 1 = 1
open entity_cursor
declare @baseTableName sysname
declare @otc nvarchar(20)
declare @primaryKey sysname
declare @totalCollected int = 0
declare @currentCollected int
declare @tempRowCount int = 0
declare @collectstatement nvarchar(max)
fetch next from entity_cursor
into @otc, @baseTableName, @primaryKey
while @@FETCH_STATUS = 0
begin
print 'Cleaning up POA for ' + @baseTableName
set @currentCollected = 0
set @collectstatement = 'insert into ToDeletePoaEntries(ObjectId, Otc)
select distinct poa.ObjectId, poa.ObjectTypeCode
from PrincipalObjectAccess poa
left join ' + @baseTableName + ' e on poa.ObjectId = e.' + @primaryKey +
' where e.' + @primaryKey + ' is null and poa.ObjectTypeCode = ' + @otc;
print @collectstatement
exec(@collectstatement)
set @tempRowCount = @@ROWCOUNT
set @currentCollected = @currentCollected + @tempRowCount
print CAST(@currentCollected as nvarchar(20)) + ' records collected for ' + @baseTableName
set @totalCollected = @totalCollected + @currentCollected
fetch next from entity_cursor
into @otc, @baseTableName, @primaryKey
end
close entity_cursor
deallocate entity_cursor
print CAST(@totalCollected as nvarchar(20)) + ' total records collected'
-- Delete query
-- This scripts cleans up orphaned POA records for selected entities
declare @deleteBatchSize int = 50000
declare @deleteBatchSizeNVarChar nvarchar(10) = CAST(@deleteBatchSize as nvarchar(10))
declare @totalDeleted int = 0
declare @currentDeleted int
declare @deletestatement nvarchar(max)
set @currentDeleted = 0
set @tempRowCount = 0
-- delete all records of the current entity type which don't have corresponding object in the base table
set @deletestatement = 'delete top (' + @deleteBatchSizeNVarChar + ') from PrincipalObjectAccess
from PrincipalObjectAccess poa
join ToDeletePoaEntries e on poa.ObjectId = e.ObjectId and poa.ObjectTypeCode = e.Otc'
print @deletestatement
-- delete PrincipalObjectAccess records in batches
exec(@deletestatement)
set @tempRowCount = @@ROWCOUNT
set @currentDeleted = @currentDeleted + @tempRowCount
while @tempRowCount = @deleteBatchSize
begin
exec(@deletestatement)
set @tempRowCount = @@ROWCOUNT
set @currentDeleted = @currentDeleted + @tempRowCount
print CAST(@currentDeleted as nvarchar(20)) + ' records deleted ' + cast(getUtcdate() as nvarchar(50))
--cleanup
end
COMMIT TRAN t1
-- Cleanup
DROP Table [dbo].[ToDeletePoaEntries]
PRINT 'EXECUTION SUCCEED'
END TRY
BEGIN CATCH
ROLLBACK TRAN t1
-- Cleanup
DROP Table [dbo].[ToDeletePoaEntries]
PRINT 'EXECUTION FAILED :' + ERROR_MESSAGE()
END CATCH
-- ??
--Delete from SubscriptionTrackingDeletedObject where TimeStamp <= convert(timestamp, 55527527);
BEGIN TRY
BEGIN TRAN t1
IF NOT EXISTS (SELECT * FROM sys.sysobjects
WHERE id = object_id(N'[dbo].[ToDeletePOAEntries]')
AND ObjectProperty(id, N'IsUserTable') = 1)
create table ToDeletePoaEntries
(
ObjectId uniqueidentifier,
Otc int
)
IF NOT EXISTS (SELECT * FROM sys.sysindexes si
INNER JOIN sys.sysobjects so ON si.id = so.id
WHERE so.id = OBJECT_ID(N'[dbo].[ToDeletePoaEntries]')
AND OBJECTPROPERTY(so.id, N'IsUserTable') = 1
AND si.name LIKE '%mainindex%')
CREATE UNIQUE NONCLUSTERED INDEX [mainindex] ON [dbo].[ToDeletePoaEntries]
(
[ObjectId] ASC,
[Otc] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
----- Insert records to be deleted in ToDeletePoaEntries
-- go through all user-owned entities which are not replicated and don't support duplicate detection
declare entity_cursor cursor local FORWARD_ONLY READ_ONLY
for select distinct e.ObjectTypeCode, e.BaseTableName, a.PhysicalName from EntityView e
inner join AttributeView a on e.EntityId = a.EntityId and a.IsPKAttribute = 1
where e.IsReplicated = 0 and e.IsDuplicateCheckSupported = 0 and e.OwnershipTypeMask & 1 = 1
open entity_cursor
declare @baseTableName sysname
declare @otc nvarchar(20)
declare @primaryKey sysname
declare @totalCollected int = 0
declare @currentCollected int
declare @tempRowCount int = 0
declare @collectstatement nvarchar(max)
fetch next from entity_cursor
into @otc, @baseTableName, @primaryKey
while @@FETCH_STATUS = 0
begin
print 'Cleaning up POA for ' + @baseTableName
set @currentCollected = 0
set @collectstatement = 'insert into ToDeletePoaEntries(ObjectId, Otc)
select distinct poa.ObjectId, poa.ObjectTypeCode
from PrincipalObjectAccess poa
left join ' + @baseTableName + ' e on poa.ObjectId = e.' + @primaryKey +
' where e.' + @primaryKey + ' is null and poa.ObjectTypeCode = ' + @otc;
print @collectstatement
exec(@collectstatement)
set @tempRowCount = @@ROWCOUNT
set @currentCollected = @currentCollected + @tempRowCount
print CAST(@currentCollected as nvarchar(20)) + ' records collected for ' + @baseTableName
set @totalCollected = @totalCollected + @currentCollected
fetch next from entity_cursor
into @otc, @baseTableName, @primaryKey
end
close entity_cursor
deallocate entity_cursor
print CAST(@totalCollected as nvarchar(20)) + ' total records collected'
-- Delete query
-- This scripts cleans up orphaned POA records for selected entities
declare @deleteBatchSize int = 50000
declare @deleteBatchSizeNVarChar nvarchar(10) = CAST(@deleteBatchSize as nvarchar(10))
declare @totalDeleted int = 0
declare @currentDeleted int
declare @deletestatement nvarchar(max)
set @currentDeleted = 0
set @tempRowCount = 0
-- delete all records of the current entity type which don't have corresponding object in the base table
set @deletestatement = 'delete top (' + @deleteBatchSizeNVarChar + ') from PrincipalObjectAccess
from PrincipalObjectAccess poa
join ToDeletePoaEntries e on poa.ObjectId = e.ObjectId and poa.ObjectTypeCode = e.Otc'
print @deletestatement
-- delete PrincipalObjectAccess records in batches
exec(@deletestatement)
set @tempRowCount = @@ROWCOUNT
set @currentDeleted = @currentDeleted + @tempRowCount
while @tempRowCount = @deleteBatchSize
begin
exec(@deletestatement)
set @tempRowCount = @@ROWCOUNT
set @currentDeleted = @currentDeleted + @tempRowCount
print CAST(@currentDeleted as nvarchar(20)) + ' records deleted ' + cast(getUtcdate() as nvarchar(50))
--cleanup
end
COMMIT TRAN t1
-- Cleanup
DROP Table [dbo].[ToDeletePoaEntries]
PRINT 'EXECUTION SUCCEED'
END TRY
BEGIN CATCH
ROLLBACK TRAN t1
-- Cleanup
DROP Table [dbo].[ToDeletePoaEntries]
PRINT 'EXECUTION FAILED :' + ERROR_MESSAGE()
END CATCH
T-SQL script to clean up SyncOperationBase table of Microsoft Dynamics CRM
-- https://support.microsoft.com/en-us/help/968520/performance-is-slow-if-the-asyncoperationbase-table-becomes-too-large-in-microsoft-dynamics-crm
/*
IF EXISTS (SELECT name from sys.indexes
WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO
*/
while (1=1)
begin
declare @DeleteRowCount int = 10000
declare @rowsAffected int
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
insert into @DeletedAsyncRowsTable(AsyncOperationId)
Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
where
OperationType in (1, 9, 12, 25, 27, 10)
AND StateCode = 3
AND StatusCode in (30, 32)
select @rowsAffected = @@rowcount
delete poa from PrincipalObjectAccess poa
join WorkflowLogBase wlb on
poa.ObjectId = wlb.WorkflowLogId
join @DeletedAsyncRowsTable dart on
wlb.AsyncOperationId = dart.AsyncOperationId
delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
where
W.AsyncOperationId = d.AsyncOperationId
delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
where
B.AsyncOperationId = d.AsyncOperationId
delete BulkDeleteOperationBase From BulkDeleteOperationBase O, @DeletedAsyncRowsTable d
where
O.AsyncOperationId = d.AsyncOperationId
delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where
WS.AsyncOperationId = d.AsyncOperationID
delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
where
A.AsyncOperationId = d.AsyncOperationId
/*If not calling from a SQL job, use the WAITFOR DELAY*/
if(@DeleteRowCount > @rowsAffected)
return
else
WAITFOR DELAY '00:00:02.000'
end
;
/*
IF EXISTS (SELECT name from sys.indexes
WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO
*/
while (1=1)
begin
declare @DeleteRowCount int = 10000
declare @rowsAffected int
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
insert into @DeletedAsyncRowsTable(AsyncOperationId)
Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
where
OperationType in (1, 9, 12, 25, 27, 10)
AND StateCode = 3
AND StatusCode in (30, 32)
select @rowsAffected = @@rowcount
delete poa from PrincipalObjectAccess poa
join WorkflowLogBase wlb on
poa.ObjectId = wlb.WorkflowLogId
join @DeletedAsyncRowsTable dart on
wlb.AsyncOperationId = dart.AsyncOperationId
delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
where
W.AsyncOperationId = d.AsyncOperationId
delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
where
B.AsyncOperationId = d.AsyncOperationId
delete BulkDeleteOperationBase From BulkDeleteOperationBase O, @DeletedAsyncRowsTable d
where
O.AsyncOperationId = d.AsyncOperationId
delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where
WS.AsyncOperationId = d.AsyncOperationID
delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
where
A.AsyncOperationId = d.AsyncOperationId
/*If not calling from a SQL job, use the WAITFOR DELAY*/
if(@DeleteRowCount > @rowsAffected)
return
else
WAITFOR DELAY '00:00:02.000'
end
;
Saturday 23 January 2016
SQL Server 2008 discontinued feature
- backup database with
- replace dump with backup
- replace load with restore
- backup log with no_log | truncate only not supported, switch to simple recovery mode
Subscribe to:
Posts (Atom)