1 of 1 people found this helpful
I think, you may need to start exploring it in reverse way from File Server side :
1. Go to the File Server's file system or directory
(If you don't know it already, you can find it from nsh -> blasadmin -a show file all)
2. You may find scripts, blpackages, installables, etc.. directories under the default File system path
3. Try finding out the most space used objects under each directory.
4. You may have most of the objects with high space usage under Installables, blpackages and files (if you take snapshots very frequently) folder. So, worth searching more there.
5. Once you identify it, you may try to find the objects in the console by running the below query (of course, you have to join and enhance the query).
select * from FILE_LOCATION where RELATIVE_PATH in ('installables/xxx_guid_xxx','installables/xxx_guid_xxx', installables/xxx_guid_xxx')
select * from FILE_LOCATION where RELATIVE_PATH like blpackages/xxx_guid_xxx
Thanks Santhosh for your reply. Approach you've suggested seems better, I'll try to use this one instead of mine.
However queries that you've suggested(and also the one I have used) will only give me name of the object and not the absolute path in BL Console. It would be very tedious for us to delete all objects if count is very high, and would be more difficult if we have two objects with same name.
I actually want to create a script out of all this information. So if I somehow get db key of the object or its absolute path in BL Console then I can probably delete the objects using BLCLI. (after fully testing it in staging environment ofcourse ).
So, is there a way I can get dbkey or path for such objects?
2 of 2 people found this helpful
it's not that complicated - just a couple joins. for sql server change the || to +
WITH temp1 (group_id, path) AS
select group_id, cast(name as varchar(1000))
from blgroup bg1
where parent_group_id = 0
cast(bg2.path || '/' || cast(bg1.name as varchar(100)) as varchar(1000))
from blgroup bg1
inner join temp1 bg2 on bg1.parent_group_id = bg2.group_id
select temp1.path,do.name,do.is_deleted,o.name,fs.host,fs.root_path,fl.relative_path from file_location fl
join depot_object_location dol on fl.file_location_id = dol.file_location_id
join depot_object do on dol.depot_object_id = do.depot_object_id and dol.depot_object_version_id = do.depot_object_version_id
join temp1 on do.group_id = temp1.group_id
join object_type o on do.object_type_id = o.object_type_id
join file_server fs on fl.file_server_id = fs.file_server_id;
I mean, a person like me, with less SQL Query knowledge, it is complicated.
Thanks Santhosh and Bill for your replies. I got the answer I needed.