Use PowerShell to gather SQL Server database physical file sizes

By:   |   Comments (17)   |   Related: > Monitoring


One of my SQL servers has a database that grows very rapidly (1 GB per week). I needed an easy way to monitor the growth of this database. I also needed an easy way to monitor all my servers for growth trending.


I know there are tools that will monitor database file size, but I wanted something easy and something I could use to gather and retain historical data. It also had to be free. After a little research I decided to try using PowerShell. I did some searching on the internet and found some sample scripts for connecting to servers, getting data from those servers and then writing the data to an Excel spreadsheet.

Prepare the environment

Before we start let me say that I am not a PowerShell guru, so some of the terminology I use may not be exact. The first thing you will need to do is start PowerShell. In Windows 7 click the Start button and type PowerShell in the Search field. When I do this on my system I am presented with a couple choices. The first two items in the list will start PowerShell, Windows PowerShell ISE is a GUI interface and Windows PowerShell is the command line interface. I like to use Windows PowerShell ISE.

Program Selection

The next thing you will need to do to use PowerShell on your system is to change the execution policy. By default the execution policy is set to Restricted, this means that no scripts will run, not any at all. To check what the current execution policy is set to, open PowerShell and enter the command Get-ExecutionPolicy. I have set my execution policy to RemoteSigned, this allows scripts I have written to run and to run scripts from the internet only if those scripts have been signed by a trusted publisher. To set the execution policy enter the command Set-ExecutionPolicy RemoteSigned. Note: there are other options for execution policy, check the documentation for the appropriate policy. Now that I have the execution policy set I am ready to go.

What I wanted to accomplish was to look at all my SQL servers and get the actual data file and log file size in megabytes. The script shown below reads a .txt file that lists all my SQL servers and gets the file name, file path, file size, and used size for both data file (.mdf) and log file (.ldf) for each database on each server in the .txt file. The data is then written to a spreadsheet on my local machine.

#Create a new Excel object using COM 
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True 
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
$intRow = 1
 ForEach ($instance in Get-Content "C:\Users\dkelly\Documents\PowershellScripts\sqlservers.txt")
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "Server: $s"
     $Sheet.Cells.Item($intRow,1).Font.Size = 12
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2) = "Database"
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True
     $Sheet.Cells.Item($intRow,3) = "Data Name"
     $Sheet.Cells.Item($intRow,3).Font.Bold = $True
     $Sheet.Cells.Item($intRow,4) = "Data File"
     $Sheet.Cells.Item($intRow,4).Font.Bold = $True
     $sheet.Cells.Item($intRow,5) = "Data Size (MB)"
     $Sheet.Cells.Item($intRow,5).Font.Bold = $True
     $Sheet.Cells.Item($intRow,6) = "Data Used Space (MB)"
     $Sheet.Cells.Item($intRow,6).Font.Bold = $True
     $Sheet.Cells.Item($intRow,7) = "Log Name"
     $Sheet.Cells.Item($intRow,7).Font.Bold = $True
     $Sheet.Cells.Item($intRow,8) = "Log Size (MB)"
     $Sheet.Cells.Item($intRow,8).Font.Bold = $True
     $Sheet.Cells.Item($intRow,9) = "Log Used Space (MB)"
     $Sheet.Cells.Item($intRow,9).Font.Bold = $True
     $Sheet.Cells.Item($intRow,10) = "Log File"
     $Sheet.Cells.Item($intRow,10).Font.Bold = $True
    foreach ($db in $dbs) 
          $dbname = $db.Name
          $fileGroups = $db.FileGroups
          ForEach ($fg in $fileGroups)
       #   write-host $fg.files | select name
            If ($fg) 
                    $mdfInfo = $fg.Files | Select Name, FileName, size, UsedSpace
                    $Sheet.Cells.Item($intRow,2) = $dbname
                    $Sheet.Cells.Item($intRow,3) = $mdfInfo.Name
                    $Sheet.Cells.Item($intRow,4) = $mdfInfo.FileName
                    $Sheet.Cells.Item($intRow,5) = ($mdfInfo.size / 1000)
                    $Sheet.Cells.Item($intRow,6) = ($mdfInfo.UsedSpace / 1000)
                    $logInfo = $db.LogFiles | Select Name, FileName, Size, UsedSpace
                    $Sheet.Cells.Item($intRow,7) = $logInfo.Name
                    $Sheet.Cells.Item($intRow,8) = ($logInfo.Size / 1000)
                    $Sheet.Cells.Item($intRow,9) = ($logInfo.UsedSpace / 1000)
                    $Sheet.Cells.Item($intRow,10) = $logInfo.FileName

The sqlserver.txt file is just a list of server names, one name per line with no punctuation as shown here;


The first four lines in the script create the Excel spreadsheet. Then for each server in the sqlserver.txt file we use the Microsoft.SQLServer.Management.SMO.Server class to create a server object of SQL Server. From the server object we can get the databases, $dbs=$s.Databases, on each server. The next several lines in the script set the values in the columns of the header row in the spreadsheet. The section of the script starting with foreach ($db in $dbs) is the part of the script that actually gets the database file data and writes it to the spreadsheet.

Shown below is the resulting spreadsheet. I broke the spreadsheet image into 3 images for readability.

The first four lines in the script create the Excel spreadsheet

The next several lines in the script set the values in the columns of the header row in the spreadsheet

the resulting spreadsheet

A couple things to note about this process. Sometimes when I execute the script the Execl spreadsheet does not get created correctly and the script throws a series of errors. I just stop the script and start it again and it works. I do not know why it does this. The script can probably be modified to write the data directly to a SQL database, but I chose to write it to a spreadsheet for a variety of reasons. In my next tip I will describe the process for getting the data into a database using SSIS then reporting on that data. Please stayed tuned.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Dale Kelly Dale Kelly has been in the computer industry since the late 80s and is currently a Sr. DBA responsible for 26 SQL Servers.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, October 5, 2016 - 4:08:38 AM - Att Back To Top (43497)

Thanks for sharing, it would be awesome if Available free space column can be added after the DB used space column.  

Wednesday, November 18, 2015 - 6:29:39 PM - PW Back To Top (39100)



I hope this is still alive!! I am using this script and mostly it works great, but for some reason the script fails at the end, when it tries to connect to my local machine, I do not have my local machine in the .txt file, which is what is confusing. The issue seems to be centred around...

foreach ($db in $dbs)


...the actual error I am getting is...


The following exception occurred while trying to enumerate the collection: "Failed to connect to server .".




At line:39 char:21


+ foreach ($db in $dbs)


+ ~~~~


+ CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException


+ FullyQualifiedErrorId : ExceptionInGetEnumerator


The following exception occurred while trying to enumerate the collection: "Failed to connect to server .".


At line:39 char:21


+ foreach ($db in $dbs)


+ ~~~~


+ CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException


+ FullyQualifiedErrorId : ExceptionInGetEnumerator










Exception : System.Management.Automation.ExtendedTypeSystemException: The following exception occurred while trying to enumerate the collection: "Failed to connect to


server .". ---> Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server . ---> System.Data.SqlClient.SqlException: A


network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify


that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open


a connection to SQL Server) ---> System.ComponentModel.Win32Exception: The system cannot find the file specified


--- End of inner exception stack trace ---


at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)


at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)


at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire,


Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)


at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean


ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)


at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean


redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)


at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential,


String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)


at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential


credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString




at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo,


DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)


at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnectionOptions options, DbConnectionPoolKey poolKey,


DbConnectionOptions userOptions)


at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionOptions userOptions)


at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionOptions userOptions)


at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate,


Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)


at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions,


DbConnectionInternal& connection)


at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions


userOptions, DbConnectionInternal& connection)


at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory,


TaskCompletionSource`1 retry, DbConnectionOptions userOptions)


at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)


at System.Data.SqlClient.SqlConnection.Open()


at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)


at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()


--- End of inner exception stack trace ---


at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()


at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()


at Microsoft.SqlServer.Management.Common.ConnectionManager.get_ServerVersion()


at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetServerVersion()


at Microsoft.SqlServer.Management.Smo.SqlSmoObject.get_ServerVersion()


at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetDbComparer(Boolean inServer)


at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitializeStringComparer()


at Microsoft.SqlServer.Management.Smo.AbstractCollectionBase.get_StringComparer()


at Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.InitInnerCollection()


at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(Boolean refresh)


at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetEnumerator()


at System.Management.Automation.EnumerableOps.GetEnumerator(IEnumerable enumerable)


--- End of inner exception stack trace ---


at System.Management.Automation.EnumerableOps.GetEnumerator(IEnumerable enumerable)


at System.Management.Automation.Interpreter.DynamicInstruction`2.Run(InterpretedFrame frame)


at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)


at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)


TargetObject :


CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException


FullyQualifiedErrorId : ExceptionInGetEnumerator


ErrorDetails :


InvocationInfo : System.Management.Automation.InvocationInfo


ScriptStackTrace : at , : line 39


PipelineIterationInfo : {}


PSMessageDetails :


 Needless to say, I do not want it to look at my local machine at all, just the ones in the .txt file. I'm just starting me PS journey, so I do not know enough to alter the script. Any help would be greatly appreciated.

Thank you.

















Tuesday, August 12, 2014 - 1:23:51 AM - Ajitesh Malhotra Back To Top (34090)


Nice Job,


Can I put the data in Sql Server database table instead of excel in the same query.Please find the below query:

When I execute the query I have got error Method invocation failed because [System.Object[]] doesn't contain a method named.


Could you please help for the same.


ForEach ($instance in Get-Content "C:\temp\test\sqlserverlist.txt")
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$database = "database"
$DataName  = "DataName"
$DataFile =  "DataFile"
$DataSize= "DataSize(MB)"

    foreach ($db in $dbs)
          $dbname = $db.Name
          $fileGroups = $db.FileGroups
          ForEach ($fg in $fileGroups)

If ($fg)

$mdfInfo = $fg.Files | Select Name, FileName, size, UsedSpace,FileGroups,database,DataName,DataFile,DataUsedSpace,LogName,LogSize,LogUsedSpace,LogFile
$FileName = $mdfInfo.FileName
$Size = ($mdfInfo.size / 1000)
$UsedSpace = ($mdfInfo.UsedSpace / 1000)
$logInfo = $db.LogFiles | Select Name, FileName, Size, UsedSpace
$name = $logInfo.Name
$Size = ($logInfo.Size / 1000)
$UsedSpace = ($logInfo.UsedSpace / 1000)
$FileGroup = $logInfo.FileName

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=AIT; Initial Catalog=Workdb; Integrated Security=SSPI")


$cmd = $conn.CreateCommand()

$cmd.CommandText = $insert_stmt

$insert_stmt = "INSERT INTO temp_ajmalh3(Name, FileName, size, UsedSpace,FileGroups,database,DataName,DataFile,DataUsedSpace,LogName,LogSize,LogUsedSpace,LogFile)

VALUES('$Name', '$FileName', '$size', '$UsedSpace', '$FileGroups','$database','$DataName','$DataFile','$DataUsedSpace','$LogName','$LogSize','$LogUsedSpace','$LogFile')"




Thursday, February 20, 2014 - 2:25:29 PM - Dale Back To Top (29517)


This sometimes happens, I am not sure why. Just run it again and it should populate.


Thursday, February 20, 2014 - 12:21:14 PM - Sam Yoko Back To Top (29516)


The poershell script open up the excell spreadsheet, buts it is not populated with the reselts any help?, thanks.

Tuesday, September 10, 2013 - 11:04:00 AM - Ed - sqlscripter Back To Top (26705)

Also for debugging I found this back in um 2003\2004 time frame, I saved as line.vbs and all you have t do is drag and rop he file onto the vb script and it will open up and number each line for you. Very handy if you code in notepad as I have.

Dim Shell
Dim System
Set Shell = CreateObject("Wscript.Shell")
Set System = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1
Const ForWriting = 2
Call LineNumbers(GetUserInput)
Call Cleanup()

Function GetUserInput
    If Wscript.Arguments.Count = 0 Then
       GetUserInput = Trim(InputBox("Enter the full path o" &_
               "f any ASCII text file to see a line-nu" &_
               "mbered copy of the file."))
       GetUserInput = Wscript.Arguments(0)
    End If
    If GetUserInput = "" Then Call Cleanup()
    If Not System.FileExists(GetUserinput) Then
       MsgBox GetUserInput & " is not an existing file on" &_
              " your system."
       Shell.Run "wscript """ & Wscript.ScriptFullName & """"
       Call Cleanup()
    End If
End Function

Sub LineNumbers(Input)
    Dim ThisFile
    Dim NewFile
    Set ThisFile = System.OpenTextFile(Input,ForReading)
    Set NewFile = System.OpenTextFile("c:\#temp#.txt",ForWriting,True)
    While Not ThisFile.AtEndOfStream
        NewFile.WriteLine ThisFile.Line & ": " & ThisFile.ReadLine
    Shell.Run "c:\#temp#.txt"
    Wscript.Sleep 5000
    System.DeleteFile "c:\#temp#.txt"
End Sub

Sub Cleanup
    Set Shell = Nothing
    Set System = Nothing
    Set NewFile = Nothing
    Set ThisFile = Nothing
End Sub

Tuesday, September 10, 2013 - 11:01:02 AM - Ed - sqlscripter Back To Top (26704)

Here is some code for saving, time stamps and password set on the xls file. I did this with VB Script long ago 2005 and record set objects.

' automatically set todays date to append filename
Dim strFilename
DIM CurrentYear
DIM CurrentMonth
Dim CurrentDay
DIM TimeStamp2
DIM DbName
Dim strScriptPath
CurrentYear = DatePart("yyyy", Date)
CurrentMonth = DatePart("M",Date)
Currentday = DatePart("D",Date)
IF CurrentMonth < 10 then
CurrentMonth = "0"&CurrentMonth
TimeStamp2 = CurrentMonth & CurrentDay & CurrentYear
TimeStamp2 = CurrentMonth & CurrentDay & CurrentYear

'Below is the SAve path to edit and if you wish to not password protect uncomment the line below
'and comment out the one below that
'Remove or add comment below to auto close excel or leave open

Tuesday, August 7, 2012 - 2:47:31 AM - sudeep Back To Top (18938)

Hi Kelly ,

Great script. I found out that, if there are more than 2 datafile (multiple ndf files) then Powershell is not inserting the records. For example, I have tempdb with 6 datafiles, but excel shows only 2 datafiles & path for tempdb. 



Monday, July 9, 2012 - 10:09:28 AM - Dale Back To Top (18402)


When the script is run it opens Excel and adds the data. The spreadsheet is not saved until you save it. I am not sure how you would send it via email, there would need to be modifications to the script to save it then create an email and attach it.

Tuesday, July 3, 2012 - 1:43:23 AM - Swamy Back To Top (18316)

Hello Kelly,


It is nice script, it will definately useful for analyze the space for databases. I have few questions.

Where exactly this file will be saved?

Is there any way to send this file directly to email.. Please help me this modifications as I am new to PS

Tuesday, June 5, 2012 - 1:18:04 PM - Ameena Back To Top (17807)



You can modify the file name by running the modify file command. You need to put database into single user mode. After modifying the file you can put it back into the multi user mode. Here is the script to do it.

Alter database MyDB

Set single_user with rollback immediate


Alter database MyDB

Modife file (Name = 'MyDB_data', filename 'D:\My_data.mdf')


Alter database MyDB

Set multi_user with rollback immediate

Tuesday, June 5, 2012 - 9:08:08 AM - Parashuram Back To Top (17799)


In my SQL Server database performance is slow.

by mistake while creating database, created as below.

both datafile and log files created with extention as  .LDF

is there any issues with performance? please advise.....

Friday, June 1, 2012 - 3:16:54 AM - rajshri shitole Back To Top (17769)

thanks for sql prompt 5 software. my sql is running very fast , directly i get coding syntax

Wednesday, May 30, 2012 - 2:48:06 PM - Dale Back To Top (17736)


Thank you for the comment. I just checked and a couple of my databases do have more than one datafile and the script returned data for both. In the spreadsheet there are two rows one for each datafile.


Wednesday, May 30, 2012 - 2:17:19 PM - George Tossa Back To Top (17734)


I also find your article very handy. It seems to me you wrote the script taking in consideration databases with only one datafile (mdf file); what if we dealing with databases with many datafiles (mdf, ndf...)?





Wednesday, May 23, 2012 - 3:55:26 PM - Ameena Back To Top (17617)


Thanks a lot for posting it. I find it very useful and will definitely put it in place and will create a report on top of it.


Wednesday, May 23, 2012 - 2:48:30 AM - Gopalakrishnan Arthanarisamy Back To Top (17601)

Hello Kelly,

Thanks for this wonderful script. I was looking into this kind of script for analyzing database growth trends and for some other an internal auditing purposes. Thanks a lot.


Gopalakrishnan Arthanarisamy,

Unisys, Bangalore.

get free sql tips
agree to terms