Space related commands

Database Files
This script runs against the database you are connected to. This will show the total size of a database file along with the available space left in the file. This is for all the files that make up the database including the log file.

SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'
FROM sys.database_files;

Log Files
This runs against every log file in the database giving the database name, Log Size in MB, Log Space Used (%), and Status.

DBCC SQLPERF (LOGSPACE);

DBCC SQLPERF: http://msdn.microsoft.com/en-us/library/ms189768.aspx

Free Drive Space
Shows the name of the drives and how many MB of free space it has. It does not show how large the drive is – or if there is any compression on it etc. Good to use if you a quick check to see if there is space to run a backup up.

EXEC master..xp_fixeddrives

Database Space Used
Replace tempDB with the name of the database you want to querey. Shows the database name (although you know that already), the size of the database in MB, percentage of unallocated space. It also show: total amount of space allocated by objects in the database, total amount of space used by data, total amount of space used by indexes, Total amount of space reserved for objects in the database, but not yet used.

EXEC tempDB.dbo.sp_spaceused;

You can also use this to find the size of an object:
EXEC sp_spaceused N'NameofObject';

sp_spaceUsed: http://msdn.microsoft.com/en-us/library/ms188776.aspx

SSIS Script to show disk data on all servers populated from a table list of servers:
I have a management server with a list of servers – for this I use columns isAccessible and isSSPI. I do the isSSPI bit because this normally means I can run the WMI query due to the way the servers have been set up.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Collections
Imports System.Management
Imports System.IO

Public Class ScriptMain
    Dim sConn As String = "server=.;database=ServerAudit;Integrated Security=SSPI;max pool size=100;min pool size=1;"
    Dim serverNameString As String = String.Empty
    Dim serverIPString As String = String.Empty
    Public Sub Main()
        Call getServerRecordSet()
        Dim serverNameArray As String() = Split(serverNameString, ",")
        Dim serverIPArray As String() = Split(serverIPString, ",")
        Dim serverHDD As String = String.Empty
        Dim x As Integer = 0
        For Each Server As String In serverNameArray
            serverHDD = getDataDiskDrives(Server, serverIPArray(x))
            x += 1
            Dim aResultSet As String() = Split(serverHDD, "/*/")
            For Each sDisk As String In aResultSet
                Try
                    If Len(sDisk) > 1 Then
                       ' MsgBox("sending to db: " & sDisk)
                        sendDatatoDB(sDisk)
                    End If
                Catch ex As Exception
                    ' MsgBox("DB Failed: " & ex.Message)
                End Try
            Next
        Next
        Dts.TaskResult = Dts.Results.Success
    End Sub

    Public Function getDataDiskDrives(ByVal server As String, ByVal ipAddress As String) As String
        Dim query1 As ManagementObjectSearcher
        Dim queryCollection1 As ManagementObjectCollection
        Dim HDDSize As String = String.Empty
        Dim thisDrive As String = String.Empty
        Try
            query1 = New ManagementObjectSearcher("\\" + ipAddress + "\root\cimv2", "SELECT * FROM Win32_DiskDrive")
            queryCollection1 = query1.Get()
            For Each mo As ManagementObject In queryCollection1
                Dim HddKB As Long = 0
                Dim Hddletter As String = String.Empty
                HddKB = Convert.ToInt64(mo("size"))
                For Each b As ManagementObject In mo.GetRelated("Win32_DiskPartition")
                    For Each c As ManagementBaseObject In b.GetRelated("Win32_LogicalDisk")
                        Hddletter = Left(Convert.ToString(c("Name")), 1) & "," & (Convert.ToInt64(c("Size")) / 1073741824).ToString & "," & (Convert.ToInt64(c("FreeSpace")) / 1073741824).ToString
                    Next
                Next
                If Len(Hddletter) > 1 Then
                    thisDrive = server & "," & Hddletter & "/*/"
                    HDDSize += thisDrive
                End If
            Next
        Catch ex As Exception
            'MsgBox(ex.Message)
            HDDSize = String.Empty
        End Try
        getDataDiskDrives = HDDSize
    End Function

    Sub sendDatatoDB(ByVal dataArray As String)
        Dim SqlCommand As SqlCommand = New SqlCommand
        SqlCommand.CommandText = "[uspAddHDDItem]"
        SqlCommand.CommandType = CommandType.StoredProcedure
        SqlCommand.Connection = New SqlConnection(sConn)
        Dim DriveLetter As String
        Dim DriveSize As String
        Dim DriveSpace As String
        Dim serverName As String
        Dim resultSet As String() = Split(dataArray, ",")
        serverName = Trim(resultSet(0))
        DriveLetter = Trim(resultSet(1))
        DriveSize = Left(Trim(resultSet(2)), 7)
        DriveSpace = Left(Trim(resultSet(3)), 7)
        If InStr(DriveSize, ".") > 1 Then
            DriveSize = Left(DriveSize, InStr(DriveSize, ".") + 2)
        End If
        If InStr(DriveSpace, ".") > 1 Then
            DriveSpace = Left(DriveSpace, InStr(DriveSpace, ".") + 2)
        End If
        Dim server As SqlParameter = New SqlParameter("@server ", SqlDbType.VarChar, 25)
        Dim letter As SqlParameter = New SqlParameter("@letter", SqlDbType.Char, 1)
        Dim size As SqlParameter = New SqlParameter("@size", SqlDbType.VarChar, 7)
        Dim space As SqlParameter = New SqlParameter("@free", SqlDbType.VarChar, 7)
        server.Value = serverName
        letter.Value = DriveLetter
        size.Value = DriveSize
        space.Value = DriveSpace
        server.Direction = ParameterDirection.Input
        letter.Direction = ParameterDirection.Input
        size.Direction = ParameterDirection.Input
        space.Direction = ParameterDirection.Input
        SqlCommand.Parameters.Add(server)
        SqlCommand.Parameters.Add(letter)
        SqlCommand.Parameters.Add(size)
        SqlCommand.Parameters.Add(space)
        SqlCommand.Connection.Open()
        Try
            SqlCommand.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("DriveSpace: " & DriveSpace & " DriveSize " & DriveSize)
        End Try
        SqlCommand.Connection.Close()
        SqlCommand.Connection.Dispose()
    End Sub

    Private Sub getServerRecordSet()
        Dim sql As String = "SELECT DISTINCT ipAddress, serverName FROM servers WHERE (isAccessible = 1) AND ipAddress is not null and domainID = 1"
        Dim conn As SqlConnection = New SqlConnection(sConn)
        Dim cmd As SqlCommand = New SqlCommand(sql, conn)
        cmd.Connection.Open()
        Dim r As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        While r.Read()
            serverNameString += r("serverName").ToString() & ","
            serverIPString += r("ipAddress").ToString() & ","
        End While
        r.Close()
        conn.Close()
        conn.Dispose()
        serverNameString = Left(serverNameString, Len(serverNameString) - 1)
        serverIPString = Left(serverIPString, Len(serverIPString) - 1)
    End Sub
End Class

This is the Stored Proc for Importing the data. From this you can work out the table schema – I delete data in the table first and use server as a primary key. You could keep it and add an update data column – and change the Primary Key.
CREATE PROCEDURE [dbo].[uspAddHDDItem]
    @server as Varchar(25),
    @letter as char(1),
    @size as Varchar(7),
    @free as Varchar(7)
AS
BEGIN
SET NOCOUNT ON;
    INSERT INTO HDDAudit(diskLetter,DiskSize,FreeSpace,ServerName)
    VALUES(@letter,Cast(@size as Decimal(7,2)),Cast(@free as Decimal(7,2)),@server);
END;