Submit a ticketCall us

whitepaperYour VM Perplexities Called, and They Need You to Read This.

Virtualization can give you enormous flexibility with future workloads and can be a key enabler for other areas, like cloud computing and disaster recovery. So, how can you get a handle on the performance challenges in your virtual environment and manage deployments without erasing the potential upside? Learn the four key areas you need to be focusing on to help deliver a healthy and well-performing data center.

Get your free white paper.

Home > Success Center > Access Rights Manager (ARM) formerly 8MAN > ARM - Knowledgebase Articles > Generate data volume detailed view for ARM SQL server

Generate data volume detailed view for ARM SQL server

Updated November 22, 2018

Overview

This article provides information on how to generate a detailed report, which tables in SQL Server use how much disk space.

For an overview use the server health check.

 

Environment

  • Access Rights Manager

Resolution

To generate a report of the hard disk consumption of the individual ARM areas on the SQL server, use the following SQL statement:

USE <8MAN DB NAME>;  
GO 
SELECT 
    s.Name AS SchemaName, 
    SUM (p.rows) AS RowCounts, 
    SUM (a.total_pages) * 8 AS TotalSpaceKB, 
    SUM (a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM (a.total_pages) - SUM (a .used_pages)) * 8 AS UnusedSpaceKB 
FROM 
    sys.tables t 
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id 
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id 
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id 
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id 
WHERE 
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0 
    AND i.OBJECT_ID> 255 
GROUP BY 
 s.name 
ORDER BY 
    s.name
  • Customize the statement so the line <8MAN DB NAME> matches your ARM SQL database server name.
  • The default SQL database name is "_8MANDB".

Space usage of the most important ARM modules in the database

Modules with the highest space consumption is identified here.

data_vol_overview_2.png

Entries that typically occupy the most disk space (non-hierarchical)

  • ad: storage consumption of Active Directory scans
  • dbo: storage consumption of the DataOwner configuration
  • ex: storage consumption of Exchange scans
  • fs: storage consumption of file server scans
  • gc: generic connector storage space of the scans of all connected resources (SAP, SharePoint, Office 365).
  • history: storage consumption of AD Logga
  • reports: storage consumption of stored reports
  • tracer: storage consumption of FS Logga

 

To reduce the size of the database and used disk space, use:

  • the settings under Server > Storage of scans (determine, how much scans/archives are stored)
  • maintain the database in the basic configuration (shrink db or log file)
  • change the recovery mode in the basic configuration

 

 
Last modified

Tags

Classifications

Internal Use Only