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;
