MS SQL – Backup Compression

September 13, 2015

Recently I was wondering how much effect the DB compression would have on saving disk space

Well if you use this script you can follow up and check it.

SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'msdb'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);

 

image

Result is much better

image

Advertisements

SharePoint – BLOB Statistics

January 19, 2015

Ever wanted to have a quick statistics on how many document types or size per document type you have in SharePoint.

Each Site collection storage has some default SQL Views and 1 of them is a view called Docs.

image

So we can use this to get insight into the BLOB storage statistics.

To get the Total Storage in a document library.

  
  select  Round(Sum(Cast(Size AS numeric )/(1024*1024)),2) as Size_GB
  
  from  [WSS_Content_Innovation].[dbo].[Docs]
  
  where ListId= '63B9C392-89E5-47CA-AEB7-1F0C4909A392' and IsCurrentVersion=1 and Type = 0

Or the number of extensions in a Site collection.

select ExtensionForFile as Extension,COUNT(*) as Count_Files
  
from  [WSS_Content_Innovation].[dbo].[Docs]
  
  where ListId= '63B9C392-89E5-47CA-AEB7-1F0C4909A392'
  and IsCurrentVersion=1 and TYPE = 0
  
  group by ExtensionForFile
  order by COUNT(*) desc

image

Or the total size per extension

select ExtensionForFile as Extension,COUNT(*) as Count_Files, CAST(Sum(Size)/1024 AS VARCHAR) + ' Kb' as Sum_FilesSize

from  [WSS_Content_Innovation].[dbo].[Docs]

  where ListId= '63B9C392-89E5-47CA-AEB7-1F0C4909A392'
  and IsCurrentVersion=1 and TYPE = 0

  group by ExtensionForFile
  order by  Sum(Size) desc

image