SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Aaron Nelson / Finding Free Space per Data File with PowerShell / Latest Posts

RE: Finding Free Space per Data File with PowerShell

Aaron Nelson - SQLvariant — Thu, 12 May 2011 16:20:58 GMT

[quote][b]Steven Jones-245254 (5/12/2011)[/b][hr]Hiwhere do i get the out and write-datatable functions from?i did find the code for out-datatable but when your script runs, the engine errors saying that the Process parameter is missing.thanks[/quote]Here's the link to the Write-DataTable function: http://gallery.technet.microsoft.com/ScriptCenter/en-us/2fdeaf8d-b164-411c-9483-99413d6053ae

RE: Finding Free Space per Data File with PowerShell

Steven Jones-245254 — Thu, 12 May 2011 03:05:08 GMT

Hiwhere do i get the out and write-datatable functions from?i did find the code for out-datatable but when your script runs, the engine errors saying that the Process parameter is missing.thanks

RE: Finding Free Space per Data File with PowerShell

Ofer Gal — Wed, 11 May 2011 14:12:48 GMT

Thanks! this script is great!

RE: Finding Free Space per Data File with PowerShell

SQLRNNR — Wed, 11 May 2011 13:25:38 GMT

Thanks for another great resource in the series.

RE: Finding Free Space per Data File with PowerShell

SQLHeap — Wed, 11 May 2011 09:38:11 GMT

[quote][b]Ofer Gal (5/11/2011)[/b][hr]This gives me the log space used.I need the data space used or unused[/quote]Oops. Here is a query (sql2k compatible) that I use regularly for that.[code="sql"]SET NOCOUNT ON;Declare @TargetDatabase sysname, @Level varchar(10), @UpdateUsage bit, @Unit char(2)Select @TargetDatabase = NULL, -- NULL: all dbs @Level = 'File', -- or "Database" @UpdateUsage = 0, -- default no update @Unit = 'GB' -- Megabytes, Kilobytes or GigabytesCREATE TABLE #Tbl_CombinedInfo ( DatabaseName sysname NULL, [type] VARCHAR(10) NULL, FileGroup VARCHAR(50) NULL, LogicalName VARCHAR(150) NULL, T dec(10, 2) NULL, U dec(10, 2) NULL, [U(%)] dec(5, 2) NULL, F dec(10, 2) NULL, [F(%)] dec(5, 2) NULL, PhysicalName sysname NULL );CREATE TABLE #Tbl_DbFileStats ( Id int identity, DatabaseName sysname NULL, FileId int NULL, FileGroupID int NULL, TotalExtents bigint NULL, UsedExtents bigint NULL, Name sysname NULL, [FileName] varchar(255) NULL ); CREATE TABLE #Tbl_ValidDbs ( Id int identity, Dbname sysname NULL ); CREATE TABLE #Tbl_Logs ( DatabaseName sysname NULL, LogSize dec (10, 2) NULL, LogSpaceUsedPercent dec (5, 2) NULL, Status int NULL );DECLARE @Ver varchar(10), @DatabaseName sysname, @Ident_last int, @String varchar(2000), @BaseString varchar(2000); SELECT @DatabaseName = '', @Ident_last = 0, @String = '', @Ver = CASE WHEN @@VERSION LIKE '%9.0%' THEN 'SQL 2005' WHEN @@VERSION LIKE '%8.0%' THEN 'SQL 2000' WHEN @@VERSION LIKE '%10.%' THEN 'SQL 2008' END;SELECT @BaseString = ' SELECT DB_NAME(), ' + CASE WHEN @Ver = 'SQL 2000' THEN 'CASE WHEN a.status & 0x40 = 0x40 THEN ''Log'' ELSE ''Data'' END' ELSE 'CASE type WHEN 0 THEN ''Data'' WHEN 1 THEN ''Log'' WHEN 4 THEN ''Full-text'' ELSE ''reserved'' END' END + ', groupname, name, ' + CASE WHEN @Ver = 'SQL 2000' THEN 'filename' ELSE 'physical_name' END + ', size*8.0/1024.0 FROM ' + CASE WHEN @Ver = 'SQL 2000' THEN 'sysfiles a Left Join sysfilegroups b on a.groupid = b.groupid' ELSE 'sys.database_files a Left Join sysfilegroups b on a.data_space_id = b.groupid' END + ' WHERE ' + CASE WHEN @Ver = 'SQL 2000' THEN ' HAS_DBACCESS(DB_NAME()) = 1' ELSE 'state_desc = ''ONLINE''' END + '';SELECT @String = 'INSERT INTO #Tbl_ValidDbs SELECT name FROM ' + CASE WHEN @Ver = 'SQL 2000' THEN 'master.dbo.sysdatabases' WHEN @Ver IN ('SQL 2005', 'SQL 2008') THEN 'master.sys.databases' END + ' WHERE HAS_DBACCESS(name) = 1 ORDER BY name ASC';EXEC (@String);INSERT INTO #Tbl_Logs EXEC ('DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS');BEGIN WHILE 1 = 1 BEGIN SELECT TOP 1 @DatabaseName = Dbname FROM #Tbl_ValidDbs WHERE Dbname > @DatabaseName ORDER BY Dbname; IF @@ROWCOUNT = 0 BREAK; SELECT @Ident_last = ISNULL(MAX(Id), 0) FROM #Tbl_DbFileStats; SELECT @String = 'INSERT INTO #Tbl_CombinedInfo (DatabaseName, type, FileGroup, LogicalName, PhysicalName, T) ' + @BaseString; EXEC ('USE [' + @DatabaseName + '] ' + @String); INSERT INTO #Tbl_DbFileStats (FileId, FileGroupID, TotalExtents, UsedExtents, Name, FileName) EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS'); UPDATE #Tbl_DbFileStats SET DatabaseName = @DatabaseName WHERE Id BETWEEN @Ident_last + 1 AND @@IDENTITY; ENDENDUPDATE #Tbl_CombinedInfo SET U = s.UsedExtents*8*8/1024.0 FROM #Tbl_CombinedInfo t JOIN #Tbl_DbFileStats s ON t.LogicalName = s.Name AND s.DatabaseName = t.DatabaseName;UPDATE #Tbl_CombinedInfo SET [U(%)] = LogSpaceUsedPercent, U = T * LogSpaceUsedPercent/100.0 FROM #Tbl_CombinedInfo t JOIN #Tbl_Logs l ON l.DatabaseName = t.DatabaseName WHERE t.type = 'Log';UPDATE #Tbl_CombinedInfo SET F = T - U, [U(%)] = U*100.0/T;UPDATE #Tbl_CombinedInfo SET [F(%)] = F*100.0/T;IF UPPER(ISNULL(@Level, 'DATABASE')) = 'FILE'BEGIN IF @Unit = 'KB' UPDATE #Tbl_CombinedInfo SET T = T * 1024, U = U * 1024, F = F * 1024; IF @Unit = 'GB' UPDATE #Tbl_CombinedInfo SET T = T / 1024, U = U / 1024, F = F / 1024; SELECT Case When CAST(SERVERPROPERTY('InstanceName') as varchar(50)) is NULL Then CAST(SERVERPROPERTY('MachineName') as varchar(50)) Else CAST(SERVERPROPERTY('InstanceName') as varchar(50)) End as 'InstanceName', DatabaseName AS 'Database', type AS 'Type', FileGroup, LogicalName, T AS 'Total', U AS 'Used', [U(%)] AS 'Used (%)', F AS 'Free', [F(%)] AS 'Free (%)', PhysicalName FROM #Tbl_CombinedInfo WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%') ORDER BY DatabaseName, type;ENDDROP TABLE #Tbl_CombinedInfoDROP TABLE #Tbl_DbFileStatsDROP TABLE #Tbl_ValidDbsDROP TABLE #Tbl_Logs [/code]

RE: Finding Free Space per Data File with PowerShell

Aaron Nelson - SQLvariant — Wed, 11 May 2011 09:36:32 GMT

[quote][b]WI-DBA (5/11/2011)[/b][hr]@Nadrek - Its a preference really, I don't like having lots of maintenance jobs on my servers, and when I come up with something new to check, the single deployment makes the whole process much quicker to deploy.[/quote]The PowerShell for just a single database would be:[code="other"]Get-SqlDatabase "WIN7W510\R2" "AdventureWorks" | Get-SqlDataFile | Format-Table Server, dbname, FileGroup, FileName, FreeSpace, timestamp[/code]Just make sure to run this first if you haven't already:[code="other"]Import-Module SQLServer[/code]and obviously change out AdventureWorks to be the name of your database that you want to look at and "WIN7W510\R2" to be the name of the server & instance that database is on.

RE: Finding Free Space per Data File with PowerShell

WI-DBA — Wed, 11 May 2011 09:34:12 GMT

This should do it - Set-ExecutionPolicy RemoteSigned[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")Function Get-DatabaseFilesBySpaceAvailable ([Microsoft.SqlServer.Management.Smo.Server] $SmoSqlServer , [decimal] $sizeThreshold=0.80){ $sqlServer.Databases | Where-Object{$_.Status -eq "Normal"} ` | Select-Object FileGroups -ExpandProperty FileGroups ` | Select-Object Files -ExpandProperty Files ` | Where-Object {$_.MaxSize -gt -1} ` | Where-Object {$_.UsedSpace -gt ($_.MaxSize * $sizeThreshold)} ` | Select UsedSpace,Size,MaxSize,FileName}$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") "Server\Instance"Get-DatabaseFilesBySpaceAvailable -SmoSqlServer $sqlServer| FT -autosize

RE: Finding Free Space per Data File with PowerShell

Ofer Gal — Wed, 11 May 2011 09:28:18 GMT

This gives me the log space used.I need the data space used or unused

RE: Finding Free Space per Data File with PowerShell

SQLHeap — Wed, 11 May 2011 09:08:33 GMT

[quote][b]Ofer Gal (5/11/2011)[/b][hr]On the SQL server I only have PS1and the hard way gives me error:Unexpected token 'in' in expression or statement.At D:\Temp\freeInDB.ps1:11 char:40+ $cmdStatement+=foreach ($DFileGroups in <<<< $db.FileGroups)I just need to know how much unused space (GB) I have in 309GB database.even a SQL script will do.[/quote]DBCC SQLPERF(logspace)

RE: Finding Free Space per Data File with PowerShell

Ofer Gal — Wed, 11 May 2011 09:06:51 GMT

On the SQL server I only have PS1and the hard way gives me error:Unexpected token 'in' in expression or statement.At D:\Temp\freeInDB.ps1:11 char:40+ $cmdStatement+=foreach ($DFileGroups in <<<< $db.FileGroups)I just need to know how much unused space (GB) I have in 309GB database.even a SQL script will do.

RE: Finding Free Space per Data File with PowerShell

WI-DBA — Wed, 11 May 2011 08:31:10 GMT

@Nadrek - Its a preference really, I don't like having lots of maintenance jobs on my servers, and when I come up with something new to check, the single deployment makes the whole process much quicker to deploy.

RE: Finding Free Space per Data File with PowerShell

Aaron Nelson - SQLvariant — Wed, 11 May 2011 07:38:25 GMT

[quote][b]Nadrek (5/11/2011)[/b][hr][quote][b]WI-DBA (5/11/2011)[/b][hr]@Nadrek - using SQL is the normal way for most of us.At some point, you are going to have too many servers to manage doing on a server by server basis.Utilizing a script that reads your central inventory server or simply a text file, you can check over every database file on every server in your enterprise - no multiple deployments or huge server groups to manage in SSMS. (I run a whole battery of checks nightly on every server, as I find something else, i just add it to my monitoring scripts in one location, the next day I have new info)To me, that is the benefit.[/quote]That's why the script INSERTS INTO a central monitoring server from each server I manage, based on SQL Server Agent Jobs; I just read from the central monitoring server.The SQL Server Agent Jobs and SP's are distributed using SSMS's Multiple Instance capability, based on Registered Server groups.[/quote]Looks like you read my mind on the next article in the series :-)

RE: Finding Free Space per Data File with PowerShell

Nadrek — Wed, 11 May 2011 07:36:34 GMT

[quote][b]WI-DBA (5/11/2011)[/b][hr]@Nadrek - using SQL is the normal way for most of us.At some point, you are going to have too many servers to manage doing on a server by server basis.Utilizing a script that reads your central inventory server or simply a text file, you can check over every database file on every server in your enterprise - no multiple deployments or huge server groups to manage in SSMS. (I run a whole battery of checks nightly on every server, as I find something else, i just add it to my monitoring scripts in one location, the next day I have new info)To me, that is the benefit.[/quote]That's why the script INSERTS INTO a central monitoring server from each server I manage, based on SQL Server Agent Jobs; I just read from the central monitoring server.The SQL Server Agent Jobs and SP's are distributed using SSMS's Multiple Instance capability, based on Registered Server groups.

RE: Finding Free Space per Data File with PowerShell

WI-DBA — Wed, 11 May 2011 07:33:25 GMT

@Nadrek - using SQL is the normal way for most of us.At some point, you are going to have too many servers to manage doing on a server by server basis.Utilizing a script that reads your central inventory server or simply a text file, you can check over every database file on every server in your enterprise - no multiple deployments or huge server groups to manage in SSMS. (I run a whole battery of checks nightly on every server, as I find something else, i just add it to my monitoring scripts in one location, the next day I have new info)To me, that is the benefit.

RE: Finding Free Space per Data File with PowerShell

Nadrek — Wed, 11 May 2011 07:23:36 GMT

Hmmm... I usually just use SQL to find SQL data.EXEC ('DBCC SQLPERF(logspace)'); - handles overall log information per database... but unless you have multiple Tlog files because you have critical drive space issues or for some other reason, that's not an issue. Runs on all DBs.DBCC LOGINFO - shows VLFs in the Tlog (I particularly like watching Total Size/VLF; for very large Tlogs, this is more important). Runs on the current DB (so do dynamic SQL with a USE dbname; first).DBCC SHOWFILESTATS - shows total and used extents for each data file. Runs on the current DB.

RE: Finding Free Space per Data File with PowerShell

WI-DBA — Wed, 11 May 2011 07:11:46 GMT

The hard way.... sheesh :)Pass in an SMO sql object and here you go! (doing the log files is that easy as well)Function Get-DatabaseFilesBySpaceAvailable ([Microsoft.SqlServer.Management.Smo.Server] $SmoSqlServer , [decimal] $sizeThreshold=0.85){ $sqlServer.Databases | Where-Object{$_.Status -eq "Normal"} ` | Select-Object FileGroups -ExpandProperty FileGroups ` | Select-Object Files -ExpandProperty Files ` | Where-Object {$_.MaxSize -gt -1} ` | Where-Object {$_.Size -gt ($_.MaxSize * $sizeThreshold)} ` | Select Name,UsedSpace,Size,MaxSize}

RE: Finding Free Space per Data File with PowerShell

SQLHeap — Wed, 11 May 2011 06:35:11 GMT

Where can I get this cmdlet Get-SqlDatabase? Am using PSv2.0, SQL08, WinXP."The term 'Get-SqlDatabase' is not recognized as the name of a cmdlet,"

Finding Free Space per Data File with PowerShell

Aaron Nelson - SQLvariant — Tue, 10 May 2011 21:52:07 GMT

Comments posted to this topic are about the item [B]Finding Free Space per Data File with PowerShell[/B]