Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Finding Free Space per Data File with PowerShell Expand / Collapse
Author
Message
Posted Wednesday, May 11, 2011 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 12, 2011 4:24 PM
Points: 6, Visits: 13
This gives me the log space used.
I need the data space used or unused
Post #1107079
Posted Wednesday, May 11, 2011 9:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, July 13, 2014 5:53 PM
Points: 94, Visits: 575
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


Cheers
http://twitter.com/widba
http://widba.blogspot.com/
Post #1107091
Posted Wednesday, May 11, 2011 9:36 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 5:57 PM
Points: 41, Visits: 122
WI-DBA (5/11/2011)
@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.


The PowerShell for just a single database would be:
Get-SqlDatabase "WIN7W510\R2" "AdventureWorks" | Get-SqlDataFile | Format-Table Server, dbname, FileGroup, FileName, FreeSpace, timestamp

Just make sure to run this first if you haven't already:
Import-Module SQLServer

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.


@SQLvariant
I have a PowerShell script for you.
Post #1107096
Posted Wednesday, May 11, 2011 9:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 383, Visits: 567
Ofer Gal (5/11/2011)
This gives me the log space used.
I need the data space used or unused


Oops. Here is a query (sql2k compatible) that I use regularly for that.

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 Gigabytes

CREATE 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;
END
END

UPDATE #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;

END

DROP TABLE #Tbl_CombinedInfo
DROP TABLE #Tbl_DbFileStats
DROP TABLE #Tbl_ValidDbs
DROP TABLE #Tbl_Logs



There is an exception to every rule, except this one...
Post #1107101
Posted Wednesday, May 11, 2011 1:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 21,187, Visits: 14,879
Thanks for another great resource in the series.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1107295
Posted Wednesday, May 11, 2011 2:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 12, 2011 4:24 PM
Points: 6, Visits: 13
Thanks! this script is great!
Post #1107324
Posted Thursday, May 12, 2011 3:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 11:15 AM
Points: 61, Visits: 241
Hi

where 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
Post #1107541
Posted Thursday, May 12, 2011 4:20 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 5:57 PM
Points: 41, Visits: 122
Steven Jones-245254 (5/12/2011)
Hi

where 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


Here's the link to the Write-DataTable function: http://gallery.technet.microsoft.com/ScriptCenter/en-us/2fdeaf8d-b164-411c-9483-99413d6053ae


@SQLvariant
I have a PowerShell script for you.
Post #1108106
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse