Different ways to find Data, log and DB Size

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


Data and Log file

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL powershellsql@gmail.com The articles are published in: http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL, sysfiles and tagged , , . Bookmark the permalink.

4 Responses to Different ways to find Data, log and DB Size

  1. RK says:

    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

  2. RK says:

    How much free space left in Log file

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s