{"id":1944,"date":"2012-03-28T06:05:15","date_gmt":"2012-03-28T06:05:15","guid":{"rendered":"https:\/\/poiseddevelopers.com\/reality-tech\/?p=1944"},"modified":"2024-05-13T10:48:04","modified_gmt":"2024-05-13T10:48:04","slug":"sync-db-grows-indefinitely","status":"publish","type":"post","link":"https:\/\/poiseddevelopers.com\/reality-tech\/sync-db-grows-indefinitely\/","title":{"rendered":"Sync DB Grows Indefinitely"},"content":{"rendered":"<p>Sync DB is a database that is a part of the User Profile Service related to Active Directory synchronization.<\/p>\n<p>Unfortunately it tends to grow in an unconstrained fashion.\u00a0 SharePoint has no built-in cleanup mechanism.<\/p>\n<p>The table that grows without bounds is \u201cInstanceData.\u201d\u00a0 The followed Stored Procedure should be first run in waves, so as not to overload the transaction logs or tempDB.<\/p>\n<p>Running on at least a monthly basis is recommended.<\/p>\n<p>The following is a report that shows monthly growth in row count:<\/p>\n<p>SELECT COUNT ([lastUpdated]) as countOfInstancedata, month ([created]) as month, YEAR([created]) as year<br \/>\nFROM [Sync DB].[dbo].[InstanceData]<br \/>\ngroup by month ([created]), YEAR ([created]) order by YEAR ([created]), month ([created])<br \/>\nHere\u2019s the stored procedure:<\/p>\n<pre lang=\"php\">USE [Sync DB]\r\nGO\r\nCREATE SCHEMA FIM\r\nGO\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nCREATE PROCEDURE [fim].[TruncateInstanceData]\r\nAS\r\nBEGIN\r\nSET NOCOUNT ON;\r\nDECLARE @truncationTime datetime;\r\nSET @truncationTime = DATEADD(day, -1, GETUTCDATE());\r\nDELETE FROM [dbo].[InstanceData]\r\nWHERE ([created] &lt; @truncationTime)\r\nEND\r\n<\/pre>\n<p>Alternatively, I\u2019ve been running this loop to gradually delete rows. You can adjust the loop number, delete size (rowcount) and delay to taste:<\/p>\n<pre lang=\"php\"> EXEC sp_spaceused N'dbo.Instancedata';\r\n -- Declare local variables\r\nDECLARE @NumberOfLoops AS int;\r\nSET @NumberOfLoops = 500;\r\nDECLARE @CurrentLoop AS int;\r\nSET @CurrentLoop = 0\r\n\r\nWHILE @CurrentLoop &lt; @NumberOfLoops BEGIN\r\nset rowcount 10000\r\ndelete from [Sync DB].[dbo].[InstanceData] \r\nwhere [Sync DB].[dbo].[InstanceData].created &lt;CONVERT(DATETIME,'2012-02-01 00:00:00', 102)\r\n\r\nWAITFOR DELAY '00:00:01:00';\r\nSET @CurrentLoop = @CurrentLoop + 1;END\r\n-- Check space used by table after we are done\r\nEXEC sp_spaceused N'dbo.BigLoggingTable';<\/pre>\n<p>A more generic WHERE clause I\u2019ve successfully used is:<br role=\"presentation\" data-uw-rm-sr=\"\" \/>WHERE [Sync DB1].[dbo].[InstanceData].created &lt;= DATEADD(day, -60, GETUTCDATE())<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sync DB is a database that is a part of the User Profile Service related to Active Directory synchronization. Unfortunately it tends to grow in an unconstrained fashion.\u00a0 SharePoint has no built-in cleanup mechanism. The table that grows without bounds is \u201cInstanceData.\u201d\u00a0 The followed Stored Procedure should be first run in waves, so as not [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":1945,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[34],"tags":[],"class_list":["post-1944","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-performance"],"acf":[],"_links":{"self":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1944","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/comments?post=1944"}],"version-history":[{"count":5,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1944\/revisions"}],"predecessor-version":[{"id":3960,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1944\/revisions\/3960"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media\/1945"}],"wp:attachment":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media?parent=1944"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/categories?post=1944"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/tags?post=1944"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}