Database Space Usage

[Former Member]

Hi

Is there a query/sp that can check each database on server and report by total space usage by segment. The databases have user defined segments.

Thanks

[Former Member]

Yes Jay, here is mine. If you use it, please feel free to modify, but keep the comment and author.

Regards,

Jean-Pierre

P.S. If I ever meet you, I will take a couple of beers as payment! LOL.

-- Author       : Jean-Pierre Dareys Robertson

-- Date         : 080415

--

-- Notes        : This query will report free space left on all

--                devices in a 15.x server regardless of configured

--                page size.

--

-- Updates      : 130207

--                converted into stored procedure. 

USE sybsystemprocs

GO

CREATE PROCEDURE sp_generic_device_free_space

AS        

SELECT

  device_name           = d.name

, size_in_MB            = (high - low + 1) / (power(1024,2) / @@pagesize)

, free_space_in_MB      =

  isnull(

  ( (high - low + 1) / (power(1024,2) / @@pagesize) ) -

   (SELECT sum(u.size) / (power(1024,2) / @@maxpagesize) FROM master..sysusages u

    WHERE  u.vstart between d.low and d.high

    AND    u.vdevno = d.vdevno

    GROUP BY d.vdevno)

, ( (high - low + 1) / (power(1024,2) / @@pagesize) )

  )

FROM

   master..sysdevices d

WHERE

   d.cntrltype = 0

GO

[Former Member]

Jay,

You can alter my procedure to report segment information. I would provide the solution, but it might require some testing and I don´t have an ASE server installed at the moment.

Actually, I am looking to build one on the AMAZON Cloud, for easy and ubicuitous access, but I am bogged down right now.

I commented on Juerguen´s code, posted earlier, and I believe you could incorportare some of that into the second FROM statement in my query above.

However, It would be missing at least one join, e.g. sysusages.segmap =syssegments.segment (N:N), but I suspect other conditions as well.

If you come up with it prior to me posting it. Let us know. I will look into it, paper and pencil, while I travel on business next week.

Regards,

Jean-Pierre

[Former Member]

All,

I personally garantee the code above to report accurate results.

I tested it on ASE 12.5, ASE 15.0.3 and most recently on a migration to ASE 15.7.

I might have tested it on ASE 16.0 but I do not recall. Anyone confirm it still works on that release?

Thank you.

Jean-Pierre

[Former Member]

Hi Jean-Pierre & Juergen

Thank you. Much appreciated.

[Former Member]

Jay,

You are welcome.

Like I said, I garantee my code in all versions of ASE up to 15.7, and I am pretty sure it will work on ASE 16.0 .

I believe Juerguen´s code is missing at least one join condition. If incorporate his query into mine, add the missing join and test I will revert.

Without a server to test on, a more difficult proposition. I will attempt the paper and pencil exercize, and let you know.

You will have to test and let us know if it works though!

LOL.

Jean-Pierre

[Former Member]

Just to add, 2k page size server. Trying to reverse engineer the database to be able to create on a new set of devices which are differently sized to source server.

Output format similar to below:

dbid/name, segment name, total allocated size

Should include any space allocated to default segments that are not allocated also to user defined segments.

Thanks

[hans-juergen_schwindke]

Hi,

here is a sample script. As segments belong to databases, you have to be in the database in question:

select distinct "databasename" = db_name(uu.dbid), "devicename" = d.name, "segmentname" = seg.name,

"size [MB]" =  (d.high - d.low+1) /512

from master..sysdevices d, master..sysusages uu, syssegments seg

where d.status & 16 = 0

and uu.vdevno = d.vdevno

and db_name(uu.dbid) = db_name()

and power(2, segment) & uu.segmap > 0

order by uu.dbid, d.name

The output will look like this:

databasename  devicename       segmentname   size [MB]

------------- ---------------- ------------- -----------

tools         tools_data_001   default              2048

tools         tools_data_001   myseg                2048

tools         tools_data_001   system               2048

tools         tools_log_001    logsegment           1024

Please note, that a device appears once for each segment located on it. So in the example the tools_data_001 device is listed three times as three segments (default, myseg, and system) are placed on the device. The log device contains one segment only and therefore it appears only once.

Best regards,

Juergen

[Former Member]

Juergen,

Looks nice but I believe you are missing joins... E.g. sysusages.segmap =syssegments.segment (N:N)?

Jean-Pierre

[hans-juergen_schwindke]

Hi,

the join between sysusages and syssegments is done in this clause:

and power(2, segment) & uu.segmap > 0

We have to retrieve all segments being encoded in sysusages.segmap.

Best regards,

Juergen

[Former Member]

Juergen,

This might be a matter of SQL semantics. I would have thought that based on how your query is written, the join you indicate should have looked like this:

and power(2, seg.segment) & uu.segmap > 0

not

and power(2, segment) & uu.segmap > 0

Did you try it both ways. Does it give the same results?

Personally, I always qualify column names, to avoid problems.

Regards,

Jean-Pierre

P.S. Sorry, but like I said, I cannot test right now.

[hans-juergen_schwindke]

Hi Jean-Pierre,

as the column "segment" appears in the table syssegments the prefix can be omitted. For ASE it is clear that the column belongs to that table.

You're right it is better to have prefixes for all columns in order to avoid problems and it is also a better coding style.

I didn't try both ways but I'm pretty sure that the results will be the same regardless the prefix is used or not.

Best regards,

Juergen

[Former Member]

Juergen,

Thank you for the note.

You are right. When the refferenced co.lumn exists only in one of the tables involved in the query, there is no need to qualify it with the table name or alias. However, as you indicate, it is better coding style and enhances readability and maintainability. It is obvious In you the code you posted...

However, I am thinking of merging your code into the procedure I posted and have to review how many times and where the "segment" column will be refferenced. If only in the outer query, then it is a moot point. If in the inner query, then both would have to be qualified.

Forgive me, but I am in a rush, and have not reviewed this carefully and I don't want to post something incorrect. Remember that a device has a finite size (theoretically since they can now be resized) but segments don't. They have always been expandable.

I will revisit this later. Thank you for your input.

Regards,

Jean-Pierre