{"id":914,"date":"2009-03-23T10:33:00","date_gmt":"2009-03-23T10:33:00","guid":{"rendered":"\/blogs\/paul\/post\/Measuring-churn-in-a-SharePoint-content-database-using-SQL-Server.aspx"},"modified":"2017-04-13T09:53:47","modified_gmt":"2017-04-13T16:53:47","slug":"measuring-churn-in-a-sharepoint-content-database-using-sql-server","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/","title":{"rendered":"Measuring &#8216;churn&#8217; in a SharePoint content database using SQL Server"},"content":{"rendered":"<p>\n<font face=\"verdana,geneva\" size=\"2\">After teaching some of the <\/font><a href=\"https:\/\/www.microsoft.com\/learning\/en\/us\/mcm-certification.aspx\"><font face=\"verdana,geneva\" size=\"2\">MCM-SharePoint<\/font><\/a><font face=\"verdana,geneva\" size=\"2\"> class last week, one of the attendees pointed me at a <\/font><a href=\"http:\/\/www.toddklindt.com\/blog\/Lists\/Posts\/Post.aspx?ID=113\"><font face=\"verdana,geneva\" size=\"2\">blog post<\/font><\/a><font face=\"verdana,geneva\" size=\"2\"> about measuring churn in SharePoint databases. The poster gave code to measure how large full backups are, which really only measures how much data there is in the database, not whether existing data has changed. If two successive full database backups are the same size, there&#39;s no way to tell how much changed &#8211; and of course, you need to take a full backup to be able to tell whether the size changed.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">A while ago I wrote a script that would enable SQL database DBAs to tell how big the next differential backup will be. A differential backup contains everything that&#39;s changed since the last full database backup, so every new and everything changed. Although this still doesn&#39;t show whether a single piece of existing content changed <em>multiple times<\/em>, it can still show whether existing content changed at least once. Better still, you don&#39;t need to take any kind of backup to run this script.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">So, to get an idea of the churn rate of your content databases, check out this script &#8211; see <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/\"><font face=\"verdana,geneva\" size=\"2\">New script: How much of the database has changed since the last full backup?<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Enjoy!<\/font>\n<\/p>\n<p>\n<font size=\"2\">PS If you&#39;re using SharePoint and find this useful, please let me know and if there&#39;s any other scripts that would be useful &#8211; Kimberly and I are both starting to get more into SharePoint admin from a SQL perspective.<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>After teaching some of the MCM-SharePoint class last week, one of the attendees pointed me at a blog post about measuring churn in SharePoint databases. The poster gave code to measure how large full backups are, which really only measures how much data there is in the database, not whether existing data has changed. If [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,38,80],"tags":[],"class_list":["post-914","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-example-scripts","category-sharepoint"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Measuring &#039;churn&#039; in a SharePoint content database using SQL Server - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Measuring &#039;churn&#039; in a SharePoint content database using SQL Server - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"After teaching some of the MCM-SharePoint class last week, one of the attendees pointed me at a blog post about measuring churn in SharePoint databases. The poster gave code to measure how large full backups are, which really only measures how much data there is in the database, not whether existing data has changed. If [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-03-23T10:33:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:53:47+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/\",\"name\":\"Measuring 'churn' in a SharePoint content database using SQL Server - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-03-23T10:33:00+00:00\",\"dateModified\":\"2017-04-13T16:53:47+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Measuring &#8216;churn&#8217; in a SharePoint content database using SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Measuring 'churn' in a SharePoint content database using SQL Server - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Measuring 'churn' in a SharePoint content database using SQL Server - Paul S. Randal","og_description":"After teaching some of the MCM-SharePoint class last week, one of the attendees pointed me at a blog post about measuring churn in SharePoint databases. The poster gave code to measure how large full backups are, which really only measures how much data there is in the database, not whether existing data has changed. If [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/","og_site_name":"Paul S. Randal","article_published_time":"2009-03-23T10:33:00+00:00","article_modified_time":"2017-04-13T16:53:47+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/","name":"Measuring 'churn' in a SharePoint content database using SQL Server - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-03-23T10:33:00+00:00","dateModified":"2017-04-13T16:53:47+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/measuring-churn-in-a-sharepoint-content-database-using-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Measuring &#8216;churn&#8217; in a SharePoint content database using SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/914","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=914"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/914\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=914"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=914"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=914"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}