size – Run SQL Server BLOG

20th September 2021

Get the subfolder sizes in a folder with PowerShell

List the subfolder sizes in a folder

Get-ChildItem  -force '\\servername\sql_backup\BACKUP_OLA' -ErrorAction SilentlyContinue | Where-Object { $_ -is [io.directoryinfo] } | % {
    $len = 0
    Get-ChildItem -recurse -force $_.fullname -ErrorAction SilentlyContinue | % { $len += $_.length }
    $_.fullname, '{0:N2} GB' -f ($len / 1Gb)
    }

Get-ChildItem -force '\\servername\sql_backup\BACKUP_OLA' -ErrorAction SilentlyContinue | Where-Object { $_ -is [io.directoryinfo] } | % {

$len = 0

Get-ChildItem -recurse -force $_.fullname -ErrorAction SilentlyContinue | % { $len += $_.length }

$_.fullname, '{0:N2} GB' -f ($len / 1Gb)

}

Result sample:

    /#  result

    \\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AI 15.61 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102318e002b 718.37 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BI 22.81 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102318e001b 94.21 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102184_STSS50E 97.33 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AP 17.52 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BP 36.99 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102318e003c 355.39 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102318e004b 3312.04 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102249_STS05AP 1.85 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102250_STS05BP 2.39 GB
    \\servername\sql_backup\BACKUP_OLA\AGSQL102318E005 0.90 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102185_STSS50P 0.07 GB


    #/

/# result

\\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AI 15.61 GB

\\servername\sql_backup\BACKUP_OLA\SQL102318e002b 718.37 GB

\\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BI 22.81 GB

\\servername\sql_backup\BACKUP_OLA\SQL102318e001b 94.21 GB

\\servername\sql_backup\BACKUP_OLA\SQL102184_STSS50E 97.33 GB

\\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AP 17.52 GB

\\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BP 36.99 GB

\\servername\sql_backup\BACKUP_OLA\SQL102318e003c 355.39 GB

\\servername\sql_backup\BACKUP_OLA\SQL102318e004b 3312.04 GB

\\servername\sql_backup\BACKUP_OLA\SQL102249_STS05AP 1.85 GB

\\servername\sql_backup\BACKUP_OLA\SQL102250_STS05BP 2.39 GB

\\servername\sql_backup\BACKUP_OLA\AGSQL102318E005 0.90 GB

\\servername\sql_backup\BACKUP_OLA\SQL102185_STSS50P 0.07 GB

2nd July 2021

Find limited SQL database files and log files

How to list limited database file size in a SQL server?

SELECT DB_NAME(database_id) AS DatabaseName,
[Name] AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB, (size*8) as SizeKB 
--, SUM(size*8) OVER (partition by database_id) as [Total Size in KB]
,* 
FROM sys.master_files
where  not (max_size = -1 or   max_size = 2684354561 )   -- (268435456 * 8000 = 2147483648000 = 2TB)
--  and DB_NAME(database_id) not in ('master','tempdb','model')
order by DatabaseName

SELECT DB_NAME(database_id) AS DatabaseName,

[Name] AS Logical_Name,

Physical_Name, (size*8)/1024 SizeMB, (size*8) as SizeKB

--, SUM(size*8) OVER (partition by database_id) as [Total Size in KB]

FROM sys.master_files

where not (max_size = -1 or max_size = 2684354561 ) -- (268435456 * 8000 = 2147483648000 = 2TB)

-- and DB_NAME(database_id) not in ('master','tempdb','model')

order by DatabaseName

25th May 2018

Index méretek

Index size

--  USE <databasename>
SELECT i.[name] AS IndexName
                ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
                AND s.[index_id] = i.[index_id]
-- where i.[name] like 'mix%'
GROUP BY i.[name]
ORDER BY IndexSizeKB Desc, i.[name]
GO

-- USE <databasename>

SELECT i.[name] AS IndexName

,SUM(s.[used_page_count]) * 8 AS IndexSizeKB

FROM sys.dm_db_partition_stats AS s

INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]

AND s.[index_id] = i.[index_id]

-- where i.[name] like 'mix%'

GROUP BY i.[name]

ORDER BY IndexSizeKB Desc, i.[name]

Az egyes indexek méretei. Az adott adatbázisra kell használni, szükség esetén szűrni.