{"id":461,"date":"2012-12-05T09:44:00","date_gmt":"2012-12-05T09:44:00","guid":{"rendered":"\/blogs\/erin\/post\/Trending-Database-Growth-From-Backups.aspx"},"modified":"2013-05-07T10:22:34","modified_gmt":"2013-05-07T17:22:34","slug":"trending-database-growth-from-backups","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/trending-database-growth-from-backups\/","title":{"rendered":"Trending Database Growth From Backups"},"content":{"rendered":"<p>When I start working with a client, one question I always ask is whether they are collecting baselines of their SQL Server environment (a shocker, I know). If they are not, I explain why it\u2019s a good idea to start capturing them. And even though I think it\u2019s an easy argument to make, I find I make a better case when I have data to back it up. But how do you make the argument for baseline data, when you don\u2019t have any real data to show?<\/p>\n<p>There is data in SQL Server that you can mine; you just have to know where to find it. If I look at a client system and notice that maintenance tasks keep taking longer and longer, then I might assume it\u2019s due to database growth. Now, if it\u2019s just database integrity checks that are taking longer and longer, that might be a sign that something is wrong. However, that\u2019s out of scope for this post, so let\u2019s stick with the assumption that the database is growing larger over time because data is rarely deleted, only added. Depending on the client\u2019s current storage and the duration of the tasks, I may have some concerns about how much disk space they\u2019re going to need down the road. I really want to trend database growth, among other things, over time. And one way I can approximate growth is by using information from full backups.<\/p>\n<p>When you backup a database, every page that is allocated in the database is copied to the backup. This means you could have a 100GB database with a backup of only 50GB, because only 50GB\u2019s worth of pages are allocated. If my database files are pre-sized, as they hopefully are, then looking at backup size will not tell me anything about the current size of the database. However, it will tell me about the growth of it \u2013 which is really what I\u2019m after.<\/p>\n<p>Backup information is stored in msdb, and while it should be removed on a regular basis via a scheduled maintenance task, it is not unusual for at least three to six months of data to exist, if not more. Everything I need for this example I can capture from dbo.backupset, which has one row for every successful backup operation. Here\u2019s my query*:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT\r\n&#x5B;database_name] AS &quot;Database&quot;,\r\nDATEPART(month,&#x5B;backup_start_date]) AS &quot;Month&quot;,\r\nAVG(&#x5B;backup_size]\/1024\/1024) AS &quot;Backup Size MB&quot;,\r\nAVG(&#x5B;compressed_backup_size]\/1024\/1024) AS &quot;Compressed Backup Size MB&quot;,\r\nAVG(&#x5B;backup_size]\/&#x5B;compressed_backup_size]) AS &quot;Compression Ratio&quot;\r\nFROM msdb.dbo.backupset\r\nWHERE &#x5B;database_name] = N'AdventureWorks'\r\nAND &#x5B;type] = 'D'\r\nGROUP BY &#x5B;database_name],DATEPART(mm,&#x5B;backup_start_date]);\r\n<\/pre>\n<p>In this query I\u2019m filtering on a specific database, and I\u2019m only looking at full backups (type = \u2018D\u2019). Log backups would be interesting to examine as well, but that\u2019s for another post. I\u2019m also aggregating all the full backups for one month. Whether you\u2019re running full backups daily or weekly, I would recommend aggregating the data by month. Trying to look at the changes day-by-day or even week-by-week is too detailed. We want to look at the big picture, and a monthly summary gives us that. Here\u2019s the output for my AdventureWorks database:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/windowslivewriter\/trendingdatabasegrowthfrombackups\/064bb95a\/output.jpg\"><img fetchpriority=\"high\" decoding=\"async\" style=\"border-width: 0px; border-style: none; border-color: -moz-use-text-color; display: inline;\" title=\"output\" alt=\"output\" src=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/windowslivewriter\/trendingdatabasegrowthfrombackups\/109cddba\/output_thumb.jpg\" width=\"499\" height=\"211\" border=\"0\" \/><\/a><\/p>\n<p>Notice that the backup size increases over time, but it\u2019s not linear. If I graph it in Excel, I can really see the trend:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/windowslivewriter\/trendingdatabasegrowthfrombackups\/4ffa914a\/image.png\"><img decoding=\"async\" class=\"alignnone\" style=\"display: inline; border: 0px none;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/windowslivewriter\/trendingdatabasegrowthfrombackups\/4464d40b\/image_thumb.png\" width=\"498\" height=\"277\" border=\"0\" \/><\/a><\/p>\n<p>Further analysis is natural from this point on \u2013 what\u2019s the percent increase each month? Each quarter? Which month had the largest increase? When is the database going to fill up the storage we have allocated currently? In my case, I just want to be able to show that we can get this kind of information, plus a lot more, from SQL Server if we just capture it. And this data supports my point very well. If you want to dig deeper into database growth analysis, I say run with it. J<\/p>\n<p>Hopefully you now see how easy it to use data from SQL Server to make your life easier: the information the above query provides can help you understand database growth and start basic capacity planning. I also hope this information helps to convince you (or your manager) that collecting baseline data can be extremely beneficial, and now\u2019s the time to start. If you need more background, or some queries to get you started, please check out my <a href=\"http:\/\/www.sqlservercentral.com\/articles\/baselines\">Baselines series<\/a> on SQL Server Central. Good luck!<\/p>\n<p><em>EDIT: *For those of you running SQL Server 2005 and below, you will need to exclude compression information:<\/em><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT\r\n&#x5B;database_name] AS &quot;Database&quot;,\r\nDATEPART(month,&#x5B;backup_start_date]) AS &quot;Month&quot;,\r\nAVG(&#x5B;backup_size]\/1024\/1024) AS &quot;Backup Size MB&quot;\r\nFROM msdb.dbo.backupset\r\nWHERE &#x5B;database_name] = N'AdventureWorks'\r\nAND &#x5B;type] = 'D'\r\nGROUP BY &#x5B;database_name],DATEPART(mm,&#x5B;backup_start_date]);\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>When I start working with a client, one question I always ask is whether they are collecting baselines of their SQL Server environment (a shocker, I know). If they are not, I explain why it\u2019s a good idea to start capturing them. And even though I think it\u2019s an easy argument to make, I find [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Trending SQL Server database growth using database backups<\/title>\n<meta name=\"description\" content=\"Every page that is allocated in a SQL Server database is copied to the backup, and backup size can be used trend database growth over time.\" \/>\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\/erin\/trending-database-growth-from-backups\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Trending SQL Server database growth using database backups\" \/>\n<meta property=\"og:description\" content=\"Every page that is allocated in a SQL Server database is copied to the backup, and backup size can be used trend database growth over time.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/trending-database-growth-from-backups\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2012-12-05T09:44:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-05-07T17:22:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/windowslivewriter\/trendingdatabasegrowthfrombackups\/109cddba\/output_thumb.jpg\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/trending-database-growth-from-backups\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/trending-database-growth-from-backups\/\",\"name\":\"Trending SQL Server database growth using database backups\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2012-12-05T09:44:00+00:00\",\"dateModified\":\"2013-05-07T17:22:34+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"Every page that is allocated in a SQL Server database is copied to the backup, and backup size can be used trend database growth over time.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/trending-database-growth-from-backups\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/trending-database-growth-from-backups\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/trending-database-growth-from-backups\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Trending Database Growth From Backups\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Trending SQL Server database growth using database backups","description":"Every page that is allocated in a SQL Server database is copied to the backup, and backup size can be used trend database growth over time.","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\/erin\/trending-database-growth-from-backups\/","og_locale":"en_US","og_type":"article","og_title":"Trending SQL Server database growth using database backups","og_description":"Every page that is allocated in a SQL Server database is copied to the backup, and backup size can be used trend database growth over time.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/trending-database-growth-from-backups\/","og_site_name":"Erin Stellato","article_published_time":"2012-12-05T09:44:00+00:00","article_modified_time":"2013-05-07T17:22:34+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/windowslivewriter\/trendingdatabasegrowthfrombackups\/109cddba\/output_thumb.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/trending-database-growth-from-backups\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/trending-database-growth-from-backups\/","name":"Trending SQL Server database growth using database backups","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2012-12-05T09:44:00+00:00","dateModified":"2013-05-07T17:22:34+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"Every page that is allocated in a SQL Server database is copied to the backup, and backup size can be used trend database growth over time.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/trending-database-growth-from-backups\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/trending-database-growth-from-backups\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/trending-database-growth-from-backups\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Trending Database Growth From Backups"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/461"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=461"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/461\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=461"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=461"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=461"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}