Different ways to get the size of the data , log files and Databases
- Using Counters
- Querying sysaltfiles & sys.databases
- Querying sys.master_files
SELECT instance_name AS DatabaseName,
[Data File(s) Size (KB)]/1024.00 [DataInMB],
[LOG File(s) Size (KB)]/1024.00 [LogInMB],
[Data File(s) Size (KB)]/1024.00 + [LOG File(s) Size (KB)]/1024.00 [TotalSize]
FROM
(
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Data File(s) Size (KB)', 'Log File(s) Size (KB)' )
AND instance_name != '_Total'
) AS Src
PIVOT
(
MAX(cntr_value)
FOR counter_name IN
( [Data File(s) Size (KB)], [LOG File(s) Size (KB)] )
)AS pvt
USE MASTER Go SELECT CONVERT(VARCHAR(25), DB.name) AS DatabaseName, (SELECT SUM((size*8)/1024.00) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB], (SELECT SUM((size*8)/1024.00) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB], (SELECT SUM((size*8)/1024.00) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024.00) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB FROM sys.databases DB ORDER BY DatabaseName
SELECT DatabaseName = DB_NAME(database_id) ,[Data MB] = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) ,[Log MB] = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , [TotalSizeMB] = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) FROM sys.master_files GROUP BY database_id ORDER BY DatabaseName
Advertisements
Hi Prashanth,
we have power shell script for data base size, Data file free space available,log size and log file free space.
I need ur help for getting result for Log file free space.
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
function Remove-ComObject {
# Requires -Version 2.0
[CmdletBinding()]
param()
end {
Start-Sleep -Milliseconds 500
[Management.Automation.ScopedItemOptions]$scopedOpt = ‘ReadOnly, Constant’
Get-Variable -Scope 1 | Where-Object {
$_.Value.pstypenames -contains ‘System.__ComObject’ -and -not ($scopedOpt -band $_.Options)
} | Remove-Variable -Scope 1 -Verbose:([Bool]$PSBoundParameters[‘Verbose’].IsPresent)
[gc]::Collect()
}
}
Function Using-Culture (
[System.Globalization.CultureInfo]$culture,
[ScriptBlock]$script)
{
$OldCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
trap
{
[System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
}
[System.Threading.Thread]::CurrentThread.CurrentCulture = $culture
$script:executionContext.InvokeCommand.InvokeScript($script)
[System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
} # End Function
trap {
write-Warning (‘An Error occurred “{0}” in “{1}”‘ -f $_.Exception.Message, $_.InvocationInfo.ScriptName) ;
continue
}
#Change directory and filename here
$DirectoryToSaveTo=’d:\System-Applications\ps_scripts\tmp\’
$Filename=’DatabaseConfigurationPerServer’
$serverlist= ‘d:\System-Applications\ps_scripts\conf\servers.txt’
#Create a new Excel object using COM
#force user locale to en-US
#http://stackoverflow.com/questions/687891/exception-automating-excel-2007-with-powershell-when-calling-workbooks-add
#$ci = new-object system.globalization.cultureinfo “en-US”
Using-Culture en-us{
$script:e = New-Object -COM “Excel.Application”
}
$script:e.Visible = $true
$script:e.DisplayAlerts=$false
#script:e.UserControl= $True
$books = $script:e.Workbooks
$wb=$books.Add()
$sheet=$wb.WorkSheets.item(1)
#counter for worksheet
$currentWorksheet = 1
#Counter variable for rows
$intRow = 1
foreach ($instance in get-content $serverlist)
{
#create the worksheet only if necessary
if ($currentWorksheet-lt 4) {
$Sheet = $wb.Worksheets.Item($currentWorksheet)
}
else {
$Sheet = $wb.Worksheets.Add()
} #add if it doesn’t exist
if($instance){
$currentName=$instance -replace ‘[\\\/\:\.]’,” # and name the worksheet
$Sheet.Name = $currentName }
# Create an SMO connection to the instance
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’)$instance
$dbs = $s.Databases
#Create column headers
$Sheet.Cells.Item($intRow,1) = “INSTANCE NAME:”
$Sheet.Cells.Item($intRow,2) = $instance
$sheet.cells.item($intRow,3) = “VERSION”
$Sheet.Cells.Item($intRow,4) = $s.versionString
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$intRow++
$Sheet.Cells.Item($intRow,1) = “DATABASE NAME”
$Sheet.Cells.Item($intRow,2) = “COLLATION”
$Sheet.Cells.Item($intRow,3) = “COMPATIBILITY LEVEL”
$Sheet.Cells.Item($intRow,4) = “AUTOSHRINK”
$Sheet.Cells.Item($intRow,5) = “RECOVERY MODEL”
$Sheet.Cells.Item($intRow,6) = “SIZE (MB)”
$Sheet.Cells.Item($intRow,7) = “SPACE AVAILABLE (MB)”
$sheet.Cells.Item($intRow,8) = “Log Size(MB)”
#Format the column headers
for ($col = 1; $col –le 9; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}
$intRow++
#######################################################
#This script gets SQL Server database information using PowerShell
#Formatting using Excel
ForEach ($db in $dbs)
{
#Divide the value of SpaceAvailable by 1KB
$dbSpaceAvailable = $db.SpaceAvailable/1KB
#Format the results to a number with three decimal places
$dbSpaceAvailable = “{0:N3}” -f $dbSpaceAvailable
$Sheet.Cells.Item($intRow, 1) = $db.Name
$Sheet.Cells.Item($intRow, 2) = $db.Collation
$Sheet.Cells.Item($intRow, 3) = $db.CompatibilityLevel
#Change the background color of the Cell depending on the AutoShrink property value
if ($db.AutoShrink -eq “True”)
{
$fgColor = 3
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow, 4) = $db.AutoShrink
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor
$Sheet.Cells.Item($intRow, 5) = $db.RecoveryModel
if($db.RecoveryModel -eq 1)
{
$Sheet.Cells.Item($intRow, 5)=”Full”
}
elseif($db.RecoveryModel -eq 3)
{
$Sheet.Cells.Item($intRow, 5)=”Simple”
}
elseif($db.RecoveryModel -eq 2 )
{
$Sheet.Cells.Item($intRow, 5)=”Bulk_Logged”
}
$Sheet.Cells.Item($intRow, 6) = “{0:N3}” -f $db.Size
#Change the background color of the Cell depending on the SpaceAvailable property value
if ($dbSpaceAvailable -lt 1.00)
{
$fgColor = 3
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow, 7) = $dbSpaceAvailable
$Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor
#Log File information
$logsize= $db.LogFiles[0].Size/1KB
$logsize = “{0:N3}” -f $logsize
$Sheet.Cells.Item($intRow, 8) = $logsize
$intRow ++
}
$intRow =1
$Sheet.UsedRange.EntireColumn.AutoFit()
$currentWorksheet += 1 #keep a tally
}
start-sleep 3
$filename=$filename -replace ‘[\\\/\:\.]’,’ ‘ #remove characters that can cause problems
$filename = “$DirectoryToSaveTo$filename.xls” #save it according to its title
if (test-path $filename ) { rm $filename } #delete the file if it already exists
#$wb.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.SaveAs($filename) #save as an XML Workbook (xslx)
#Todo : Check order to kill excel components
$wb.Saved = $True #flag it as being saved
$wb.Close() #close the document
$books=$null
$script:e.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$Sheet = $Null #makes sure Excel deflates
$script:e=$Null #let the air out
#kill excel.exe
Remove-ComObject
Send-MailMessage -From “email.com” -To “email.com” -SmtpServer “smptmailid” `
-Subject “Databases Configuration & space used” -Attachments $Filename
Hi RK,
What do you mean by Log file free space?
–Prashanth
How much free space left in Log file
Hi RK,
Check this post
https://sqlpowershell.wordpress.com/2016/04/11/sql-db-details-using-over-multiple-servers-using-powershell/
–Prashanth