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, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s