Skip Ribbon Commands
Skip to main content

Quick Launch

Todd Klindt's home page > Todd Klindt's SharePoint Admin Blog > Posts > Using PowerShell to determine SharePoint database size
April 12
Using PowerShell to determine SharePoint database size

Recently on the MSDN SharePoint forums someone was asking about backup size, and how much space a farm backup would take. This got my wheels turning; there must be a way to do this with Windows PowerShell. To the SharePoint Management Console I went, with purpose in my heart.

I've used Get-SPContentDatabase a few times in the past so that's where I started. I piped it through Get-Member and found a disksizerequired property. The lightbulb when off. That property tells us how much space a database is actually using, not the size of the MDF file itself. This is exactly what I needed. Then I realized not only was there a Get-SPContentDatabase there was also a Get-SPDatabase (no content) that covers all the databases, including the big one, the logging database. After some noodling around I came up with this little gem:

Get-SPDatabase | Sort-Object disksizerequired -desc | Format-Table Name, @{Label ="Size in MB"; Expression = {$_.disksizerequired/1024/1024}}

This lists all of your databases, from largest to smallest and their size in MBs. Its output looks like this:

Name Size in MB

---- ----------

WSS_Logging 1657

SharePoint_Config 505

Search_Service_Application_PropertyS... 158

SharePoint_AdminContent_9cdc3220-ff2... 99

Search_Service_Application_DB_79408a... 99

WSS_Content 74

WSS_Content_OOTB_upgrade 46

WSS_Content_ac5e33ae4e354dd493b6da17... 26

wss_content_upgrade 26

WSS_Content_portal 24

Search_Service_Application_CrawlStor... 17

User Profile Service Application_Pro... 7

Bdc_Service_DB_a95c44b879ef48f8a5307... 6

WebAnalyticsServiceApplication_Repor... 6

WebAnalyticsServiceApplication_Stagi... 5

Application_Registry_Service_DB_2f70... 4

Managed Metadata Service_c23b61fed61... 3

managed_metadata_the_truth 3

User Profile Service Application_Soc... 3

User Profile Service Application_Syn... 2

StateService_783e168d26fd498b9be3061... 2

Secure_Store_Service_DB_89dc97ed2262... 2

PerformancePoint Service Application... 2

WordAutomationServices_00a4e73d0d734... 2

That's pretty helpful, but it doesn't answer that burning question, how much space is needed. I came up with this to answer that question:

Get-SPDatabase | ForEach-Object {$db=0} {$db +=$_.disksizerequired; $_.name + " - " + $_.disksizerequired/1024/1024} {Write-Host "`nTotal Storage (in MB) =" ("{0:n0}" -f ($db/1024/1024))}

Its output looked this this:

StateService_783e168d26fd498b9be3061299862269 - 2

Secure_Store_Service_DB_89dc97ed22624025ae6e9a69f2684978 - 2

WordAutomationServices_00a4e73d0d7341cdac915e8247da5211 - 2

User Profile Service Application_SocialDB_f2c646049bfd4456b612e454ac1a73fd - 3

Bdc_Service_DB_a95c44b879ef48f8a53070abd98a9d03 - 6

WebAnalyticsServiceApplication_StagingDB_51bf4216-80fa-45d5-b580-b5d419c8e269 - 5

SharePoint_Config - 505

PerformancePoint Service Application_c575f52198844d46a6c2d29c69a6594a - 2

Search_Service_Application_CrawlStoreDB_6c608154693c412bb9fc1e35235e502f - 17

Search_Service_Application_PropertyStoreDB_d7726abcc15f425eb39428f3d6d983d4 - 158

Application_Registry_Service_DB_2f70cb1bdc274522ac5ca345df9de86e - 4

SharePoint_AdminContent_9cdc3220-ff2c-4b52-abcf-ad9ce1ba463d - 99

WSS_Content - 74

WSS_Content_ac5e33ae4e354dd493b6da176e9e6c84 - 26

WSS_Content_OOTB_upgrade - 46

WSS_Content_portal - 24

wss_content_upgrade - 26

Search_Service_Application_DB_79408a739be74c18ac0b44630382b13c - 99

Managed Metadata Service_c23b61fed6114e88af70931a2add3c36 - 3

managed_metadata_the_truth - 3

User Profile Service Application_ProfileDB_15a709d2085741fb9ed182d6e77e2a4f - 7

WSS_Logging - 1654

User Profile Service Application_SyncDB_9ff01258380945d99a5c9e8e110b6835 - 2

WebAnalyticsServiceApplication_ReportingDB_c7b9b6b2-f3d7-40c4-b72f-70a47e78deec – 6

 

Total Storage (in MB) = 2,775

They both introduce some new PowerShell learnin' for me. In the first example the new thing was the Label and Expression part at the end. This lets you put something more complicated than just a property in Format-Table. Also lets you put a fancy label on the column. I like it.

The second command took a lot of work. I had to learn a lot of things. I had to learn how to keep a tally as I loop through items. I used the $db variable for that and had to use curly braces to get everything in the right place. I also had to figure out how to write a blank line to separate the Total column. `n did that for me. Finally I wanted to format the output. Disksizerequired is in bytes, which is a gigantic and useless number for us. In both examples I divided it by 1024 twice to get it to megabytes. That wasn't enough. In a large enough farm that value is going to be gigabytes, so I wanted to format it so it could be readable. ("{0:n0}" –f ($db/1024/1024)) made that happen. I did a lot of searching on "Powershell format numbers" to get that all worked out. TechNet had the solution for me. The second script doesn't give as nice of output since we're not using Format-Table anymore. Maybe I'll add that in a later tip.

tk

Comments

nice

nice post, very helpful examples...  fyi, got here via link in RT by @Sharepoint911
 on 4/12/2010 2:27 PM

May I use this in a presentation?

Good Morning, Todd -

Excellent post! I am a member of the Richmond SharePoint Users Group and I'm doing a topic on Basics of PowerShell next month. May I please use your solution above as an example to illustrate the usefulness of PowerShell with SP2010? I will of course note you and your blog site as the source.

Thanks!
Adam Preston
 on 4/15/2010 10:41 AM

May I use this in a presentation?

Probably would have helped if you knew how to contact me to respond to...is it Friday yet? :)

apreston@spammerssmelllikefeet.com

Thanks!
Adam
 on 4/15/2010 10:44 AM

Re: May I use this in a presentation?

Adam,
As long as you give me credit, and tell your UG how smart and handsome I am you have my blessing.

tk
Todd O. KlindtNo presence information on 4/15/2010 11:21 AM

Need Help for SharePoint 2010 Installation

Hi All,

I want to install SharePoint 2010 on Windows 7 locally.

Pls Help me and suggest the link or guideline. Can we install SharePoint 2010 locally on Windows 7

Thanks in advance
 on 4/16/2010 11:51 AM

Converting to MB (or other byte sizes)

Todd,

   One little suggestion.  When you want to convert bytes to another scale (KB, MB, GB,...) it's possible to divide your value by the scale itself and PowerShell understands natively.

$myValInBytes/1MB = <value in MBs>
or
$myValInBytes/1GB = <value in GBs>

   With PowerShell v1 I believe it was possible to go up to GBs, but with v2 PowerShell now understands TB (terabyte) and even PB (petabyte).  Nice little way to clean up scripts with less need to remember how many times to multiply by 1024 :)


-Brian T. Jackett
 on 4/30/2010 8:27 AM

Publish DB size in SP list

Is there an easy way to publish this to a SharePoint list?
 on 8/31/2010 8:05 AM

DB Size separately

With Get-SPDatabase we get the *space a database is actually using*, however SQL stores the information separately in ldf and mdf file.
Is there a way to get these information separately, like how much space used by ldf and mdf respectively.

~Neeraj
 on 10/6/2010 6:14 AM

very nice

thanks for your effort
 on 1/3/2013 7:57 AM

Re: Using PowerShell to determine SharePoint database size

We have created nice script to extract this report for multiple farms, it also reports as orange when DB size is 80 GB, red in case database size is over 100 GB. It also report as orange if number site collection is on warning level.

http://msexchange.me/2014/08/10/sharepoint-content-database-size-report/

Regards
Sukhija Vikas
http://msexchange.me
 on 8/10/2014 6:22 PM
1 - 10Next

Add Comment

Items on this list require content approval. Your submission will not appear in public views until approved by someone with proper rights. More information on content approval.

Title


Body *


Today's date *

Select a date from the calendar.
Please enter today's date so I know you are a real person

Twitter


Want a message when I reply to your comment? Put your Twitter handle here.

Attachments

 

 Please Support my Sponsors