topic Re: How to Get a Empty Volume List via SQL Query or PowerShell in ONTAP Discussions

How to Get a Empty Volume List via SQL Query or PowerShell

raj_shrivastava11 — Wed, 04 Jun 2025 22:02:08 GMT

Hi Team,

Need help in Getting a Empty Volume List either via MySQL Query or PowerShell.

I have checked the MySQL database side but does not get any option to get this.

Also tried via PowerShell, by getting "Used %" but that is not correct info.

Thanks and Regars

Raj

Re: How to Get a Empty Volume List via SQL Query or PowerShell

JGPSHNTAP — Wed, 17 Feb 2016 21:00:32 GMT

Anything can be done in powershell.

The issue is that there is truly no ZERO volume b/c when you create a volume there is a small amount of data on the volume.

You can query for all volumes with size less than x and then go from there.

Re: How to Get a Empty Volume List via SQL Query or PowerShell

asulliva — Thu, 18 Feb 2016 01:55:40 GMT

What was incorrect about used %? Aside from it's not very accurate (1% of 1GB = 10MB, 1% of 1TB = 10GB...that's a big difference).

As @JGPSHNTAP mentioned, it's difficult to find a truly empty volume because the system has a few KB for metadata, etc. I think the closest thing would be to look for a volume with used space of less than 512KB:

Get-NcVol -Query @{VolumeSpaceAttributes=@{SizeUsed='<512KB'};VolumeStateAttributes=@{IsNodeRoot=$false;IsVserverRoot=$false}}

That should narrow the returned volumes back significantly. Theoretically it might still be possible to have a volume with something in it, for example and thin provisioned LUN which hasn't had any data written to it, or a share/export which has empty files/folders. If you want to be 100% safe, you'll want to mount the volume (NFS/CIFS) and check the contents.

Hope that helps.

Andrew

Re: How to Get a Empty Volume List via SQL Query or PowerShell

JGPSHNTAP — Thu, 18 Feb 2016 01:58:11 GMT

I assume from his tag, this is for 7-mode so it's easier..

get-navol | ? {$_.sizeused < xxxx}

Re: How to Get a Empty Volume List via SQL Query or PowerShell

mbeattie — Thu, 18 Feb 2016 03:29:29 GMT

Hi Raj,

What are you ultimately trying to achieve or automate once you have this information?

As you've mentioned MySQL as an option are you using WFA as the source for your query?

If so you could use something like:

For 7-Mode:

#'---------------------------------------
#'Select the empty data volumes.
#'---------------------------------------
SELECT
   volume.name AS 'volume_name',
   aggregate.name AS 'aggregate_name',
   vfiler.name AS 'vfiler_name',
   array.name AS 'controller_name',
   array.ip AS 'controller_address'
FROM
   storage.volume,
   storage.aggregate,
   storage.vfiler,
   storage.array
WHERE
   volume.vfiler_id = vfiler.id
AND
   vfiler.array_id = array.id
AND
   volume.aggregate_id = aggregate.id
AND
   volume.used_size_mb = 0;
#'---------------------------------------

For cDOT

#'---------------------------------------
#'Select the empty data volumes.
#'---------------------------------------
SELECT
   volume.name AS 'volume_name',
   aggregate.name AS 'aggregate_name',
   vserver.name AS 'vserver_name',
   node.name AS 'node_name',
   cluster.name AS 'cluster_name',
   cluster.primary_address
FROM
   cm_storage.volume,
   cm_storage.aggregate,
   cm_storage.vserver,
   cm_storage.node,
   cm_storage.cluster
WHERE
   volume.vserver_id = vserver.id
AND
   vserver.cluster_id = cluster.id
AND
   volume.aggregate_id = aggregate.id
AND
   aggregate.node_id = node.id
AND
   volume.junction_path <> '/'
AND
   volume.used_size_mb = 0;
#'---------------------------------------

Note: or change 'volume.used_size_mb <= {$VolumeSizeMB}' assuming you want to enumerate volumes of a small (but not totally empty) size.

/matt

Re: How to Get a Empty Volume List via SQL Query or PowerShell

raj_shrivastava11 — Thu, 18 Feb 2016 14:03:09 GMT

Thanks Guys.

Actually i m working on a workflow which can give me a list of completely empty volumes.

Used % will not give me the exact list.

I am also trying to look into Read-NaDirectory where i can do LS on each volume and see if there is any content and return one which does not have any content.

Raj