{"id":17592,"date":"2022-06-28T13:36:00","date_gmt":"2022-06-28T11:36:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=17592"},"modified":"2022-06-28T13:35:26","modified_gmt":"2022-06-28T11:35:26","slug":"get-sql-server-database-size-with-powershell","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/","title":{"rendered":"Get SQL Server database size with PowerShell"},"content":{"rendered":"\n<h4 class=\"wp-block-heading\">Introduction<\/h4>\n\n\n\n<p>For various reasons, following the evolution of the data size of databases is interesting for a dba or for the application team. Most of the time, this is something built-in for monitoring tools &#8211; convenient to follow the evolution over the time.<br>But in certain circumstances, you must turn to another alternative &#8211; no access to the monitoring tool, no monitoring tool&#8230;<\/p>\n\n\n\n<p>Automation is nowadays a central topic, and the interest of PowerShell is no longer to be demonstrated in Windows environments. That is why, in this blog, I propose you to achieve our goal with this language.<br>It offers several main advantages, such as:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Saving time<\/li><li>Reducing human errors<\/li><li>Standardizing actions<\/li><li>Abstracting the complexity<\/li><\/ul>\n\n\n\n<p>To simplify this automation, I will rely on the PowerShell module <a href=\"https:\/\/dbatools.io\/\">dbatools<\/a>. Very helpful, it includes many commands to automate dba&#8217;s daily tasks such as administration, migration, maintenance&#8230;<\/p>\n\n\n<h4>Size per database<\/h4>\n<p><!-- \/wp:post-content --><\/p>\n<p>First, you need to configure the variable <em>$MyInstance<\/em> to target the instance which is hosting the databases. The command <a href=\"https:\/\/docs.dbatools.io\/Get-DbaDatabase\"><em>Get-DbaDatabase<\/em><\/a> will retrieve all the databases available on the instance &#8211; it includes many information, but we will only focus on those we are interesting on.<br><!-- wp:paragraph -->As reminder, you can use <a href=\"https:\/\/docs.microsoft.com\/en-us\/powershell\/module\/microsoft.powershell.utility\/get-member?view=powershell-7.2\"><em>Get-Member<\/em><\/a> command to list properties and methods available to in the object.<br><!-- wp:code --><\/p>\n<pre class=\"wp-block-code\"><code>$MyInstance = '&lt;ServerName&gt;';\nGet-DbaDatabase -SqlInstance $MyInstance | Format-Table SqlInstance, \n\tName, `\n\t@{Label=\"DataSpaceUsage_MB\"; Expression={$_.DataSpaceUsage\/128}}, `\n\t@{Label=\"SpaceAvailable_MB\"; Expression={$_.SpaceAvailable\/128}}, `\n\t@{Label=\"TotalSize_MB\"; Expression={$_.Size}} -AutoSize;\n<\/code><\/pre>\n<p><!-- \/wp:code --><\/p>\n<p><\/p>\n<h4>Size per file<\/h4>\n<p><!-- wp:paragraph --><\/p>\n<p>The command <a href=\"https:\/\/docs.dbatools.io\/Get-DbaDbFile\"><em>Get-DbaDbFile<\/em><\/a> will retrieve all the database files available for all your instances, including main data files \/ next data files and also log files &#8211; it also includes many information, such as autogrowth configuration.<br><!-- wp:code --><\/p>\n<pre class=\"wp-block-code\"><code>$MyInstance = '&lt;ServerName&gt;';\nGet-DbaDbFile -SqlInstance $MyInstance | Format-Table SqlInstance, \n\tDatabase, `\n\tLogicalName, `\n\tTypeDescription, `\n\t@{Label=\"UsedSpace_MB\"; Expression={$_.UsedSpace}}, `\n\t@{Label=\"AvailableSpace_MB\"; Expression={$_.AvailableSpace}}, `\n\t@{Label=\"TotalSize_MB\"; Expression={$_.Size}} -AutoSize;\n<\/code><\/pre>\n<p><!-- \/wp:code --><\/p>\n<p><\/p>\n<p><!-- wp:heading {\"level\":4} --><\/p>\n<h4>Conclusion<\/h4>\n<p><!-- \/wp:heading --><\/p>\n<p><!-- wp:paragraph --><\/p>\n<p>Through this blog, we worked on retrieving database sizes in an automated manner with PowerShell.<br>As a next step, it is possible to iterate the result on multiple instances loaded from a Central Management Server (CMS) with the command <a href=\"https:\/\/docs.dbatools.io\/Get-DbaRegServer\">Get-DbaRegServer<\/a>. It is also possible to persist the results in a table with the command <a href=\"https:\/\/docs.dbatools.io\/Write-DbaDbTableData\">Write-DbaDbTableData<\/a>.<\/p>\n<p><!-- \/wp:paragraph --><\/p><!-- \/wp:paragraph -->","protected":false},"excerpt":{"rendered":"<p>Introduction For various reasons, following the evolution of the data size of databases is interesting for a dba or for the application team. Most of the time, this is something built-in for monitoring tools &#8211; convenient to follow the evolution over the time.But in certain circumstances, you must turn to another alternative &#8211; no access [&hellip;]<\/p>\n","protected":false},"author":14,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[2595,2550],"type_dbi":[],"class_list":["post-17592","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-powershell-2","tag-sql-server-2"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Get SQL Server database size with PowerShell - dbi Blog<\/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.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Get SQL Server database size with PowerShell\" \/>\n<meta property=\"og:description\" content=\"Introduction For various reasons, following the evolution of the data size of databases is interesting for a dba or for the application team. Most of the time, this is something built-in for monitoring tools &#8211; convenient to follow the evolution over the time.But in certain circumstances, you must turn to another alternative &#8211; no access [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-06-28T11:36:00+00:00\" \/>\n<meta name=\"author\" content=\"Nathan Courtine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nathan Courtine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/\"},\"author\":{\"name\":\"Nathan Courtine\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"headline\":\"Get SQL Server database size with PowerShell\",\"datePublished\":\"2022-06-28T11:36:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/\"},\"wordCount\":311,\"commentCount\":0,\"keywords\":[\"PowerShell\",\"SQL Server\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/\",\"name\":\"Get SQL Server database size with PowerShell - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2022-06-28T11:36:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Get SQL Server database size with PowerShell\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\",\"name\":\"Nathan Courtine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"caption\":\"Nathan Courtine\"},\"description\":\"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Get SQL Server database size with PowerShell - dbi Blog","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.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/","og_locale":"en_US","og_type":"article","og_title":"Get SQL Server database size with PowerShell","og_description":"Introduction For various reasons, following the evolution of the data size of databases is interesting for a dba or for the application team. Most of the time, this is something built-in for monitoring tools &#8211; convenient to follow the evolution over the time.But in certain circumstances, you must turn to another alternative &#8211; no access [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/","og_site_name":"dbi Blog","article_published_time":"2022-06-28T11:36:00+00:00","author":"Nathan Courtine","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Nathan Courtine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/"},"author":{"name":"Nathan Courtine","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"headline":"Get SQL Server database size with PowerShell","datePublished":"2022-06-28T11:36:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/"},"wordCount":311,"commentCount":0,"keywords":["PowerShell","SQL Server"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/","url":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/","name":"Get SQL Server database size with PowerShell - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-06-28T11:36:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/get-sql-server-database-size-with-powershell\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Get SQL Server database size with PowerShell"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1","name":"Nathan Courtine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","caption":"Nathan Courtine"},"description":"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17592","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=17592"}],"version-history":[{"count":34,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17592\/revisions"}],"predecessor-version":[{"id":23123,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17592\/revisions\/23123"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17592"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17592"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17592"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=17592"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}